By gbn


2009-09-27 14:52:29 8 Comments

Inspired by this question where there are differing views on SET NOCOUNT...

Should we use SET NOCOUNT ON for SQL Server? If not, why not?

What it does Edit 6, on 22 Jul 2011

It suppresses the "xx rows affected" message after any DML. This is a resultset and when sent, the client must process it. It's tiny, but measurable (see answers below)

For triggers etc, the client will receive multiple "xx rows affected" and this causes all manner of errors for some ORMs, MS Access, JPA etc (see edits below)

Background:

General accepted best practice (I thought until this question) is to use SET NOCOUNT ON in triggers and stored procedures in SQL Server. We use it everywhere and a quick google shows plenty of SQL Server MVPs agreeing too.

MSDN says this can break a .net SQLDataAdapter.

Now, this means to me that the SQLDataAdapter is limited to utterly simply CRUD processing because it expects the "n rows affected" message to match. So, I can't use:

  • IF EXISTS to avoid duplicates (no rows affected message) Note: use with caution
  • WHERE NOT EXISTS (less rows then expected
  • Filter out trivial updates (eg no data actually changes)
  • Do any table access before (such as logging)
  • Hide complexity or denormlisation
  • etc

In the question marc_s (who knows his SQL stuff) says do not use it. This differs to what I think (and I regard myself as somewhat competent at SQL too).

It's possible I'm missing something (feel free to point out the obvious), but what do you folks out there think?

Note: it's been years since I saw this error because I don't use SQLDataAdapter nowadays.

Edits after comments and questions:

Edit: More thoughts...

We have multiple clients: one may use a C# SQLDataAdaptor, another may use nHibernate from Java. These can be affected in different ways with SET NOCOUNT ON.

If you regard stored procs as methods, then it's bad form (anti-pattern) to assume some internal processing works a certain way for your own purposes.

Edit 2: a trigger breaking nHibernate question, where SET NOCOUNT ON can not be set

(and no, it's not a duplicate of this)

Edit 3: Yet more info, thanks to my MVP colleague

Edit 4: 13 May 2011

Breaks Linq 2 SQL too when not specified?

Edit 5: 14 Jun 2011

Breaks JPA, stored proc with table variables: Does JPA 2.0 support SQL Server table variables?

Edit 6: 15 Aug 2011

The SSMS "Edit rows" data grid requires SET NOCOUNT ON: Update trigger with GROUP BY

Edit 7: 07 Mar 2013

More in depth details from @RemusRusanu:
Does SET NOCOUNT ON really make that much of a performance difference

15 comments

@StriplingWarrior 2011-03-29 21:13:52

It took me a lot of digging to find real benchmark figures around NOCOUNT, so I figured I'd share a quick summary.

  • If your stored procedure uses a cursor to perform a lot of very quick operations with no returned results, having NOCOUNT OFF can take roughly 10 times as long as having it ON. 1 This is the worst-case scenario.
  • If your stored procedure only performs a single quick operation with no returned results, setting NOCOUNT ON might yield around a 3% performance boost. 2 This would be consistent with a typical insert or update procedure. (See the comments on this answer for some discussion about why this may not always be faster.)
  • If your stored procedure returns results (i.e. you SELECT something), the performance difference will diminish proportionately with the size of the result set.

@zvolkov 2011-05-02 15:52:23

+1 for the impact on the cursor, this is consistent with my observations

@Milan Jaric 2019-08-01 10:02:44

Point 2 is not accurate! I mean the blog it is referring to. Never was! DONE and DONEPROC and DONEINPROC are sent with same size regardless if NO_COUNT is set to ON or OFF. RowCount is still there as ULONGLONG (64 bytes) and flag DONE_COUNT is still there but bit value is 0. SQL server will count number of rows anyways, even tho you are not interested to read value from DONE token. If you read @@ROWCOUNT then you added more bytes to token stream either in form of returnvalue token or as another colmetadata + row tokens!

@StriplingWarrior 2019-08-01 17:09:17

@MilanJaric: Thanks for calling that out. You helped me realize that I'd linked the wrong article. The link is updated now, and the article makes a compelling argument to show that there can be a slight performance improvement with SET NOCOUNT ON. Do you think there are problems with the benchmark methods used?

@Milan Jaric 2019-08-02 16:18:05

:) still inaccurate about SET NOCOUNT OFF/ON, Error is that second SP do not have SET NOCOUNT OFF; and that is why they think they are not getting extra bytes in response. Accurate benchmark would be to use SET NOCOUNT ON in left and SET NOCOUNT OFF in right stored procedure. This way you will get TDS package with DONEINPROC (SET NOCOUNT ...), ten again DONEINPROC (INSERT statement), and then RETURNVALUE(@@ROWCOUNT), then RETURNSTATUS 0 for sp and finaly DONPROC. Error is there because second sp do not have SET NOCOUNT OFF in body!

@Milan Jaric 2019-08-02 16:22:24

To rephrase what they found but they didn't realised is that if you have 1K fetch cursor requestst, first make one request to set NOCOUNT to ON or OFF for connection and then use same connection to call cursor fetch 1K times to save some bandwidth. Actual connection state for NOCOUNT ON or OFF will not affect bandwidth, it may just confuse client lib e.g. ADO.net or ODBC. So "don't use SET NOCOUNT <WHATEVER> if you care about bandwidth" :)

@Milan Jaric 2019-08-02 16:27:34

One more thing, cursors are read using same connection, so no need to put SET NOCOUNT <WATHEVER> for each FETCH NEXT request execution

@StriplingWarrior 2019-08-02 17:07:16

@MilanJaric: I don't fully understand everything you're saying about the TDS package, but I think you're saying that the benchmark would have been more accurate if they had added SET NOCOUNT OFF to the second stored procedure. But since the second procedure was already slower in the benchmarks, it seems like that only would have added more bytes to the stream in that case, right? So perhaps, even though there is overhead in the returned value, setting NOCOUNT ON actually does make the execution of the insert itself slightly faster?

@StriplingWarrior 2019-08-02 17:11:21

I could see it being the case, depending on the speed of your connection, that the extra call to SET NOCOUNT ON (and whatever data gets sent back across the wire as a result) might outweigh the performance gains you get from a speedier insert, though. And, regardless, a 3% difference is probably not a sufficient benefit to merit the added complexity. I've updated my post to use softer language about what you might expect in that second bullet point.

@bcoughlan 2019-04-10 14:30:56

One place that SET NOCOUNT ON can really help is where you are doing queries in a loop or a cursor. This can add up to a lot of network traffic.

CREATE PROCEDURE NoCountOn
AS
set nocount on
    DECLARE @num INT = 10000
    while @num > 0
    begin
       update MyTable SET SomeColumn=SomeColumn
       set @num = @num - 1
    end
GO


CREATE PROCEDURE NoCountOff
AS
set nocount off
    DECLARE @num INT = 10000
    while @num > 0
    begin
       update MyTable SET SomeColumn=SomeColumn
       set @num = @num - 1
    end
GO

Turning on client statistics in SSMS, a run of EXEC NoCountOn and EXEC NoCountOff shows that there was an extra 390KB traffic on the NoCountOff one:

client statistics

Probably not ideal to be doing queries in a loop or cursor, but we don't live in in ideal world either :)

@phil_w 2019-01-22 16:20:19

I wanted to verify myself that 'SET NOCOUNT ON' does not save a network packet nor a roundtrip

I used a test SQLServer 2017 on another host (I used a VM) create table ttable1 (n int); insert into ttable1 values (1),(2),(3),(4),(5),(6),(7) go create procedure procNoCount as begin set nocount on update ttable1 set n=10-n end create procedure procNormal as begin update ttable1 set n=10-n end Then I traced packets on port 1433 with the tool 'Wireshark': 'capture filter' button -> 'port 1433'

exec procNoCount

this is the response packet: 0000 00 50 56 c0 00 08 00 0c 29 31 3f 75 08 00 45 00 0010 00 42 d0 ce 40 00 40 06 84 0d c0 a8 32 88 c0 a8 0020 32 01 05 99 fe a5 91 49 e5 9c be fb 85 01 50 18 0030 02 b4 e6 0e 00 00 04 01 00 1a 00 35 01 00 79 00 0040 00 00 00 fe 00 00 e0 00 00 00 00 00 00 00 00 00

exec procNormal

this is the response packet: 0000 00 50 56 c0 00 08 00 0c 29 31 3f 75 08 00 45 00 0010 00 4f d0 ea 40 00 40 06 83 e4 c0 a8 32 88 c0 a8 0020 32 01 05 99 fe a5 91 49 e8 b1 be fb 8a 35 50 18 0030 03 02 e6 1b 00 00 04 01 00 27 00 35 01 00 ff 11 0040 00 c5 00 07 00 00 00 00 00 00 00 79 00 00 00 00 0050 fe 00 00 e0 00 00 00 00 00 00 00 00 00

On line 40 I can see '07' which is the number of 'row(s) affected'. It is included in the response packet. No extra packet.

It has however 13 extra bytes which could be saved, but probably not more worth it than reducing column names (e.g. 'ManagingDepartment' to 'MD')

So I see no reason to use it for performance

BUT As others mentioned it can break ADO.NET and I also stumbled on an issue using python: MSSQL2008 - Pyodbc - Previous SQL was not a query

So probably a good habit still...

@Vinayak Savale 2018-12-27 04:55:18

Sometimes even the simplest things can make a difference. One of these simple items that should be part of every stored procedure is SET NOCOUNT ON. This one line of code, put at the top of a stored procedure turns off the messages that SQL Server sends back to the client after each T-SQL statement is executed. This is performed for all SELECT, INSERT, UPDATE, and DELETE statements. Having this information is handy when you run a T-SQL statement in a query window, but when stored procedures are run there is no need for this information to be passed back to the client.

By removing this extra overhead from the network it can greatly improve overall performance for your database and application.

If you still need to get the number of rows affected by the T-SQL statement that is executing you can still use the @@ROWCOUNT option. By issuing a SET NOCOUNT ON this function (@@ROWCOUNT) still works and can still be used in your stored procedures to identify how many rows were affected by the statement.

@Sedat Kapanoglu 2009-10-10 10:00:39

Ok now I've done my research, here is the deal:

In TDS protocol, SET NOCOUNT ON only saves 9-bytes per query while the text "SET NOCOUNT ON" itself is a whopping 14 bytes. I used to think that 123 row(s) affected was returned from server in plain text in a separate network packet but that's not the case. It's in fact a small structure called DONE_IN_PROC embedded in the response. It's not a separate network packet so no roundtrips are wasted.

I think you can stick to default counting behavior almost always without worrying about the performance. There are some cases though, where calculating the number of rows beforehand would impact the performance, such as a forward-only cursor. In that case NOCOUNT might be a necessity. Other than that, there is absolutely no need to follow "use NOCOUNT wherever possible" motto.

Here is a very detailed analysis about insignificance of SET NOCOUNT setting: http://daleburnett.com/2014/01/everything-ever-wanted-know-set-nocount/

@gbn 2009-10-10 12:26:34

Indeed. I've been using SET NOCOUNT ON forever, but marc_s pointed out the limitation of SQLDataAdapter in the other question.

@gbn 2009-10-11 06:38:06

Thanks. The bytes or size is not the issue to me, but the client has to process it. It's the SQLDataAdapter dependency that still astounds me though...

@Sedat Kapanoglu 2009-10-11 13:26:00

I don't have any benchmarks on client processing overhead, but I guess that processing 9 bytes wouldn't be that complicated or cumbersome.

@Sedat Kapanoglu 2009-10-11 13:26:48

(9 bytes per result set, that is)

@gbn 2009-10-11 17:19:33

The 9 bytes is treated as 2nd result set, which still has some overhead. Anyway, thanks for the answer.

@gbn 2009-10-16 15:43:45

Thanks for your answer. I'll accept this because of your investigations, which triggered more info and work from me. I disagree on the overhead though: it can matter as other answers show. Cheers, gbn

@racingsnail 2013-07-09 20:20:45

Its not the number of bytes its round trip delay over wire which is the performance killer

@Sedat Kapanoglu 2013-07-10 12:53:36

@racingsnail good point. +1. I added it to my answer too.

@Milan Jaric 2019-07-31 10:34:22

In TDS message stream, and example is when one is inserting only values into table. DONINPROC (RPC) or DONE (BATCH) message is streamed with rowcount set to affected rows, while done_count flag is true, regardless if NO_COUNT is ON. Depends on client lib implementation in cases when query holds SELECT statements or RPC calls that do select, it may require to disable counting... WHEN disabled, rows are still counted for select statement, but flag DONE_COUNT is set to false. Always read what your client lib suggests since it will interpret token (message) stream instead of you

@Sedat Kapanoglu 2019-07-31 20:26:37

@MilanJaric thanks milan, good info. i think if the behavior is supposed to be the same among different protocol versions, then the client shouldn't make a difference, should it?

@Milan Jaric 2019-08-01 09:45:21

depends what client expects. If client driver documentation explicitly states that it needs counting turned off, then You need to respect that. And reason is simple, for select and output statement it will wait for COLMETADATA and ROWs, and it will count rows until next COLMETADATA when it start over with new result. Any DONE token with count=1 indicates that there was insert/update/delete and how many rows are affected. There is one draw back with SET NO_COUNT ON set in connection, if stored procedure do not have SET NOCOUNT ON, DONEINPROC will have flag count=1.

@Milan Jaric 2019-08-01 09:46:45

btw, YES, this is same behavior in all TDS 7.X versions.

@Subhransu Panda 2017-04-18 17:34:56

SET NOCOUNT ON; Above code will stop the message generated by sql server engine to fronted result window after the DML/DDL command execution.

Why we do it? As SQL server engine takes some resource to get the status and generate the message, it is considered as overload to the Sql server engine.So we set the noncount message on.

@KRules 2016-08-05 11:30:24

I know it's pretty old question. but just for update.

Best way to use "SET NOCOUNT ON" is to put it up as a first statement in your SP and setting it OFF again just before the last SELECT statement.

@Shubham Sharma 2015-12-08 11:26:01

if (set no count== off)

{ then it will keep data of how many records affected so reduce performance } else { it will not track the record of changes hence improve perfomace } }

@Bhaumik Patel 2012-10-14 16:20:26

  • When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. It is used with any SELECT, INSERT, UPDATE, DELETE statement.

  • The setting of SET NOCOUNT is set at execute or run time and not at parse time.

  • SET NOCOUNT ON improves stored procedure (SP) performance.

  • Syntax: SET NOCOUNT { ON | OFF }

Example of SET NOCOUNT ON:

enter image description here

Example of SET NOCOUNT OFF:

enter image description here

@stom 2018-08-15 14:07:54

Easy and Quick to understand with screenshots. Great Job. :)

@user1509 2012-05-17 06:24:19

SET NOCOUNT ON;

This line of code is used in SQL for not returning the number rows affected in the execution of the query. If we don't require the number of rows affected, we can use this as this would help in saving memory usage and increase the speeed of execution of the query.

@gbn 2012-05-17 06:51:18

Note that @@ROWCOUNT is still set. SET NOCOUNT ON suppresses any extra response that SQL Server sends to the client. See the accepted answer above please

@Rabia Mansour 2011-07-26 11:33:36

I don't know how to test SET NOCOUNT ON between client and SQL, so I tested a similar behavior for other SET command "SET TRANSACTION ISOLATION LEVEL READ UNCIMMITTED"

I sent a command from my connection changing the default behavior of SQL (READ COMMITTED), and it was changed for the next commands. When I changed the ISOLATION level inside a stored procedure, it didn't change the connection behavior for the next command.

Current conclusion,

  1. Changing settings inside stored procedure doesn't change the connection default settings.
  2. Changing setting by sending commands using the ADOCOnnection changes the default behavior.

I think this is relevant to other SET command such like "SET NOCOUNT ON"

@funkymushroom 2014-03-21 15:25:47

Does your point 1 above imply that you don't really need to SET NOCOUNT OFF at the end because it doesn't affect the global environment?

@Doug 2016-01-28 19:35:25

I'm not sure if that's what he meant with point 1, but in my tests yes, apparently the global environment is not affected by SET NOCOUNT ON inside a stored procedure.

@Tao 2011-07-22 11:03:56

At the risk of making things more complicated, I encourage a slightly different rule to all those I see above:

  • Always set NOCOUNT ON at the top of a proc, before you do any work in the proc, but also always SET NOCOUNT OFF again, before returning any recordsets from the stored proc.

So "generally keep nocount on, except when you are actually returning a resultset". I don't know any ways that this can break any client code, it means client code never needs to know anything about the proc internals, and it isn't particularly onerous.

@gbn 2011-07-22 11:07:04

Thanks. You can get rowcount from the DataSet or consuming container of course, but could be useful. We can't have triggers on SELECTs so this would be safe: most client errors are caused by spurious messages on data changes.

@Tao 2011-07-22 11:20:18

The problem with this rule is just that it's harder to test for than "Is SET NOCOUNT ON at the top of the proc?"; I wonder whether SQL Analysis tools like Sql Enlight can test for that sort of thing... Adding it to my long-term todo list for my SQL formatter project :)

@Chris J 2009-10-13 15:52:00

If you're saying you might have different clients as well, there are problems with classic ADO if SET NOCOUNT is not set ON.

One I experience regularly: if a stored procedure executes a number of statements (and thus a number of "xxx rows affected" messages are returned), ADO seems not to handle this and throws the error "Cannot change the ActiveConnection property of a Recordset object which has a Command object as its source."

So I generally advocate setting it ON unless there's a really really good reason not to. you may have found the really really good reason which I need to go and read into more.

@zvolkov 2009-10-10 12:50:06

Regarding the triggers breaking NHibernate, I had that experience first-hand. Basically, when NH does an UPDATE it expects certain number of rows affected. By adding SET NOCOUNT ON to the triggers you get the number of rows back to what NH expected thereby fixing the issue. So yeah, I would definitely recommend turning it off for triggers if you use NH.

Regarding the usage in SPs, it's a matter of personal preference. I had always turned the row count off, but then again, there are no real strong arguments either way.

On a different note, you should really consider moving away from SP-based architecture, then you won't even have this question.

@gbn 2009-10-10 12:57:50

I disagree with moving away from stored procs. This would mean we have to have the same SQL in 2 different client code bases and trust our client coders. We're developer DBAs. And don't you mean "SET NOCOUNT ON"?

@gbn 2009-10-10 13:00:46

About SP vs no-SP, it's been done to death already... ;-)

@Coops 2013-01-07 14:16:07

@gbn Can you name some good articles on SP vs no-SP please

@gbn 2013-01-07 14:27:18

@CodeBlend: just Google it for more than you ever need. However... stackoverflow.com/a/4040466/27535

@marc_s 2009-09-27 14:56:24

I guess to some degree it's a DBA vs. developer issue.

As a dev mostly, I'd say don't use it unless you absolutely positively have to - because using it can break your ADO.NET code (as documented by Microsoft).

And I guess as a DBA, you'd be more on the other side - use it whenever possible unless you really must prevent it's usage.

Also, if your devs ever use the "RecordsAffected" being returned by ADO.NET's ExecuteNonQuery method call, you're in trouble if everyone uses SET NOCOUNT ON since in this case, ExecuteNonQuery will always return 0.

Also see Peter Bromberg's blog post and check out his position.

So it really boils down to who gets to set the standards :-)

Marc

@gbn 2009-09-27 15:00:57

He's on about simple CRUD though: the data grid he mentions could use xml to send multiple rows to avoid round trips etc

@marc_s 2009-09-27 15:09:20

I guess if you never use SqlDataAdapters, and you never check for and rely on the "records affected" number returned by ExecuteNonQuery (e.g. if you use something like Linq-to-SQL or NHibernate), then you probably don't have any problems using SET NOCOUNT ON in all stored procs.

Related Questions

Sponsored Content

12 Answered Questions

[SOLVED] SQL Server: Query fast, but slow from procedure

5 Answered Questions

[SOLVED] SET NOCOUNT OFF or RETURN @@ROWCOUNT?

1 Answered Questions

[SOLVED] ExecuteNonQuery() and SET NOCOUNT ON

  • 2015-04-16 15:46:26
  • Victor Sotnikov
  • 2153 View
  • 2 Score
  • 1 Answer
  • Tags:   c# sql-server ado.net

1 Answered Questions

[SOLVED] What is the DB2 equivalent of SQL Server's SET NOCOUNT ON?

  • 2013-07-24 08:56:14
  • slung
  • 1731 View
  • 1 Score
  • 1 Answer
  • Tags:   sql sql-server db2

1 Answered Questions

Oracle trigger instead of and rowcount

2 Answered Questions

[SOLVED] What is the Oracle equivalent of SQL Server's SET NOCOUNT ON?

1 Answered Questions

[SOLVED] System Triggers for Replication and the NOCOUNT option

4 Answered Questions

[SOLVED] SET NOCOUNT ON and reading messages using C# and ADO.NET

3 Answered Questions

[SOLVED] SET NOCOUNT ON and Cursors

Sponsored Content