SET NOCOUNT has to be one of the most used T-SQL statements. I can’t think of a single production script or procedure in which I didn’t use this SET statement. And I have to stretch to come up with a case when I would specifically not use it. The only time I don’t use NOCOUNT is when writing off-the-cuff test scripts. I decided to dig into it a bit and see what it really does behind the scenes.
“Stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set.
SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.”
I learned that the first statement isn’t as clear as it could be. I think it is technically true but easy to misunderstand. It appears to say that it stops the count from being returned in a result set. This is not true. The result still returns the count for statements that return data to the client. However, this count is marked as not to be used.
The second account is a bit misleading. It does stop the DONE_IN_PROC messages (tokens, actually, which I will explain later) from being sent. The reduction in network traffic is very case specific. Now it is always better to have less network traffic than more but I can think of only a few cases where a significant difference would be noticed.
Examining this requires a bit of understanding of Tabular Data Streams (TDS). I’m not interested in writing a series of columns about TDS at the moment so I’m going to keep this intro as short as possible. One of the great things about TDS is that we don’t have to know a lot about it ’cause it just works.
Quoting Microsoft on TDS.
“The Tabular Data Stream (TDS) Protocol is an application-level protocol used for the transfer of requests and responses between clients and database server systems. In such systems, the client will typically establish a long-lived connection with the server. Once the connection is established using a transport-level protocol, TDS messages are used to communicate between the client and the server. A database server can also act as the client if needed, in which case a separate TDS connection must be established. Note that the TDS session is directly tied to the transport-level session, meaning that a TDS session is established when the transport-level connection is established and the server receives a request to establish a TDS connection. It persists until the transport-level connection is terminated (for example, when a TCP socket is closed). In addition, TDS does not make any assumption about the transport protocol used, but it does assume the transport protocol supports reliable, in-order delivery of the data.”
In short, when you get data back from SQL Server it is in the format defined by the TDS protocol. If we understand a little bit about TDS we can capture the packets on the wire and examine them. For me seeing examples was the best way to understand what is going on and I demonstrate some of these.
A few TDS definitions first
These definitions will make much more sense when I apply it to an example.
Response: “A TDS message sent by a server to a client related to a previously issued request.”
Result Set: “A set of data streams representing the result of a query. A result set starts with a COLMETADATA token and ends with a DONE, DONEPROC, or DONEINPROC token.”
From looking at the traces I learned that a single response will contain multiple result sets. This surprised me. I expected each result to be encapsulated with its own protocol header. The entire TDS packet had a header but each result set did not. Multiple result sets (not MARS) returned from the execution of a single batch were simply stacked on after another sequentially in the response. A COLMETADATA token in the result stream indicated the beginning of another result set.
Responses come in two forms: tokenless and token streams. I’m not going to talk about tokenless streams because they aren’t applicable for this scenario and I didn’t take the time to learn about them. A token is “a single byte identifier used to describe the data that follows it…” A token stream uses tokens to delineate different parts of the TDS packet. The effect is a token with some data following that is applicable to that token. Then another token with data following it that is applicable to that specific token, etc. This will be easy to see in the example.
My NOCOUNT Test Case
To get started I captured very simple packets but to get a better understanding of what is happening I decided to execute a more complex hierarch of statements. In my test case I created a batch with many dependent child procedures so that the thread of execution would have to go many levels deep into the stored procedure and statement execution. I wanted to see how the complexity of the batch affected the results returned.
IF EXISTS ( SELECT NULL FROM sys.objects WHERE object_id = object_id('Child1','P') ) DROP PROCEDURE Child1 GO CREATE PROCEDURE Child1 AS --SET NOCOUNT ON SELECT CAST('Miami' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'Miami' EXEC Child2 SELECT CAST('Salem' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'Salem' RETURN 765432 GO IF EXISTS ( SELECT NULL FROM sys.objects WHERE object_id = object_id('Child2','P') ) DROP PROCEDURE Child2 GO CREATE PROCEDURE Child2 AS EXEC Child3A SELECT CAST('Houston' as varchar(10)) [City], AddressLine1 FROM Person.Address WHERE City = 'Houston' EXEC Child3B GO IF EXISTS ( SELECT NULL FROM sys.objects WHERE object_id = object_id('Child3A','P') ) DROP PROCEDURE Child3A GO CREATE PROCEDURE Child3A AS EXEC Child4 SELECT CAST('Dallas' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'Dallas' GO IF EXISTS ( SELECT NULL FROM sys.objects WHERE object_id = object_id('Child3B','P') ) DROP PROCEDURE Child3B GO CREATE PROCEDURE Child3B AS SELECT CAST('New York' as varchar(10)) [City],AddressLine1 FROM Person.Address FROM City = 'New York' GO IF EXISTS ( SELECT NULL FROM sys.objects WHERE object_id = object_id('Child4','P') ) DROP PROCEDURE Child4 GO CREATE PROCEDURE Child4 AS SELECT CAST('Chicago' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'Chicago' CREATE TABLE #Test (AddressLine1 nvarchar(60)) INSERT INTO #Test (AddressLine1) SELECT AddressLine1 FROM Person.Address WHERE City = 'Seattle' GO
This is fairly complicated for a blog example so I wrote out in plain English the order of execution. As you can see in each SELECT statement in each procedure I am returning a virtual column that contains the city I’m constraining the search by. This makes it much easier to see the result set in a data capture. By looking at the batch and the stored procedures being called we can imagine the thread of execution flowing through it. We should see this order of execution.
Select Tampa in batch
Exec Procedure Child1
Select Miami in Child 1
Exec Procedure Child 2
Exec Procedure Child 3A
Exec Procedure Child 4
Select Chicago in Child 4
Create Table in Child 4
Insert into Table in Child4
Return to Child 3a
Select Dallas in 3a
Return to Child2
Select Houston Child2
Exec Procedure Child 3B
Select New York in Child 3b
Return to Child2
Return to Child1
Select Salem in Child1
Return to batch
The pictures I show are from the Packet Bytes Pane of a Wireshark packet capture. Each picture has three components. On the left is the offset number of the bytes in that row. Line 10 is actually 0x10 which converts to decimal 16. There are 16 bytes represented on each row. The middle section contains the actual bytes in the packet. The right section contains the ASCII representation of the bytes if applicable. It isn’t a very robust translation but it is good enough for us to easily see where we are in the packet and is the reason I included the city name in my results. My sample selections include the city name so that we can use the right section to help orient ourselves in the packet.
The SET NOCOUNT Results
Using the right section of the wireshark capture and the knowledge that each statement completion has a DONE-type token we can pick our way through the TDS packet. I’ve highlighted each type of token so it is easier to pick out the stream.
DONE, DONEINPROC and DONEPROC tokens are in yellow. These have the hex values of 0xfd, 0xfe and 0xff respectively and are typically 13 bytes long.
COLMETADATA tokens are in green
ROW tokens are in light red.
Within the DONE-type tokens are fields of information.
Status – pink (2 bytes)
CurCmd – blue (2 bytes but I won’t be talking about this at all)
DoneRowCount – Orange (4 or 8 bytes depending on the version of TDS. In our example it is 8.)
At offset 0000 we see the very first DONE token. This is from the SET NOCOUNT statement. The follow 12 bytes are not of interest right now. However, in the same row we see a COLMETADATA token. This indicates the beginning of a result set. In the offset row 0040 we see a ROW token (d1 highlighted in red). By looking at the right pane we see that this is a row from the Tampa SELECT statement in the beginning of the batch. And finally in offset row 0070 there is a DONE token indicating the end of the Tampa select statement results.
The content of this done token data stream are interesting and directly applicable to the NOCOUNT topic. The status field highlighted in pink gives us more information about the result set. It uses a bitwise OR to combine various hex values into these two bytes. Two of the possible statuses are:
0x1: DONE_MORE – This means that this is not the final DONE in the response and that more data streams will follow
0x10: DONE_COUNT – This means that the count given to us in the DoneRowCount field is valid. The DoneRowCount stream is in orange. Looking at the DoneRowCount field we see that the value is 0x01. This converts to 1 in decimal. This is the number of rows affected/returned by the Tampa SELECT.
The short explanation is that 0x11 or 0x10 in the pink status indicates that we can take the 0x1 count in the orange section as value. This changes when SET NOCOUNT is on as I will show later in a data capture.
After the end of the DoneRowCount section of the DONE token we see another COLMETADATA token. This indicates the beginning of another result set. By looking at the right pane in the data capture we can see that it is from the Miami SELECT. As an interesting aside there is nothing in the response that indicates that the Miami SELECT is in a different procedure.
By looking at the right hand pane in the capture we can see that the DONEINPROC token (0xff highlighted in yellow) on offset line 0510 is for the Chicago SELECT. In our previous example we saw a DONE token (0xfd). On this occasion the statement completed is in a procedure and not the root of the batch so the DONE token is of a different flavor. Once again the pink section shows the status of 0x11 which is a bitwise OR of 0x1 and 0x10. Recall that these mean that there are more results to follow this one and that the row count in the orange section (0xb) is valid. 0xb converts to 11 decimal, which is an accurate count of the number of rows returned.
There is a second 0xff token on this offset line. This is for the creation of a temp table in procedure Child4. The 0xff token in offset line 0520 is the insert of records into this temp table. The status of this token 0x10 indicates that the count is valid and the count is 0x8d. 0x8d is 141 and this is the number of rows inserted into the table. There is one more 0xff token before the next COLMETADATA token on offset line 0540. This is the completion of the EXEC statements in Child3A that calls procedure Child4.
Now the capture of the end of the response:
The 0xff token on offset 1a70 is for the Salem SELECT. The status of 0x11 again indicates the count of 0x65 (101 decimal) is valid and that more DONE tokens are expected. Highlighted in green on this capture is the token 0x79. This is a RETURNSTATUS token. It has a token stream (brown) indicating what the return value is. Notice that in my last procedure, Child1, I have a RETURN statement with the value of 765432. If we translate the hex of 0xf8, 0xad, 0x0b it converts to 765432. The bytes are stored in BIG ENDIAN order so we’d type 0xbadf8 into our conversion calculation.
Now, here is the final done token, 0xfe, DONEPROC. This is the final result set in the response. We know this because the status is value 0x0 instead of 0x1.
Before continuing I think it is important to note some interesting insights from this packet. The first statement in the batch is SET NOCOUNT OFF. The second statement in the batch is a query for addresses in Tampa. The third query in the batch is actually inside of a stored procedure called from the batch. What is interesting to note is that there is no separation between the results packets delineating where in the batch the specific clause is executed. In other words we cannot tell from the response which procedure in our batch produce which results. We see that the results from each statement are simply stacked one after the other. This also means that our app can’t tell either. The implementation is invisible to the client.
Comparing this to a batch where NOCOUNT is ON
This is very similar to the capture with NOCOUNT OFF. You will notice though for the DONE token in offset 0070 (0xfd in yellow) the status (in pink) is 0x01 instead of 0x11. Recall that 0x1 means that there are more result sets to follow and that 0x10 means that we can trust the count returned by the DONE data stream. 0x01 instructs the client to NOT trust the count returned in the DoneRowCount field. The count (in orange) is actually returned with the result set and is the same as when NO COUNT was OFF. In this case setting NOCOUNT ON doesn’t actually save any network traffic since the count is included in the result set. It is a meager bit in the status field that tells us if we can use the count or not.
Compared to the previous capture we can see that several DONEINPROC tokens are missing. This works in accordance with the documentation in that “SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure.” The statements that return actual data still get a DONEINPROC token otherwise the tokens are eliminated from the response. Note also that the status (pink) of the DONEINPROC token at offset 0510 is 0x01 indicated there are more result sets to come and that we can’t trust the count returned in the DoneRowCount stream (orange) even though the DoneRowCount does contain an accurate count of the rows returned.
And the capture at the end of the response
No significant difference from the initial capture. The status of the DONEINPROC token that starts on line offset 1a30 is 0x01 which is to be expected when NOCOUNT is disabled.
I created this chart to help compare the two responses including the DONE tokens with the Status and DoneRowCount fields.
|Statement||Token (with nocount off)||Token (with nocount on)|
|Set NoCount||fd 01 00 ba 00 00||fd 01 00 b9 00 00|
|Select Tampa in batch||fd 11 00 c1 00 01||fd 01 00 c1 00 01|
|Enter Procedure Child1||No token||No token|
|Select Miami in Child 1||ff 11 00 c1 00 0a||ff 01 00 c1 00 0a|
|Enter Procedure Child 2||No token||No token|
|Enter Procedure Child 3A||No token||No token|
|Enter Procedure Child 4||No token||No token|
|Select Chicago in Child 4||ff 11 00 c1 00 0b||ff 01 00 c1 00 0b|
|Create Table in Child 4||ff 01 00 c6 00 00||None|
|Insert into Table in Child4||ff 11 00 c3 00 8d||None|
|Back to Child 3a||ff 01 00 e0 00 00||None|
|Select Dallas in 3a||ff 11 00 c1 00 07||ff 01 00 c1 00 07|
|Back to Child2||ff 01 00 e0 00 00||None|
|Select Houston Child2||ff 11 00 c1 00 05||ff 01 00 c1 00 05|
|Enter Procedure Child 3B||No token||None|
|Select New York in Child 3b||ff 11 00 c1 00 02||ff 01 00 c1 00 02|
|Back to Child2||ff 01 00 e0 00 00||None|
|Back to Child1||ff 01 00 e0 00 00||None|
|Select Salem in Child1||ff 11 00 c1 00 65||ff 01 00 c1 00 65|
|Back to batch||79 e6 1d 00 00||79 f8 ad 0b|
|End batch||fe 00 00 e0 00 00||fe 00 00 e0 00 00|
The documentation claims that setting NOCOUNT on can have significant network bandwidth savings. As we can see this is on a case-by-case basis determined by the number of non-data returning statements being executed relative to the amount of results returned. A WHILE loop that executes 10,000 times in a procedure that only returns one row will see a great reduction in traffic relative to the size of the entire result set. But a few non-data returning statements in a batch that returns thousands and thousands of rows will see practically zero percentage change in bandwidth.
How SET NOCOUNT Interacts With Your Client
When developing a client the count in the DoneRowCount is what is given as the number of rows affected/returned. If there are multiple statements returning multiple results and multiple DoneRowCount fields these numbers are totaled up as a single value by the client. You can affect this count returned by changing NOCOUNT in the middle of a procedure. I created this procedure to test this.
CREATE PROCEDURE dbo.CountTest AS SET NOCOUNT ON SELECT CAST('Miami' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'Miami' SET NOCOUNT OFF SELECT CAST('Dallas' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'Dallas' SET NOCOUNT ON SELECT CAST('Houston' as varchar(10)) [City], AddressLine1 FROM Person.Address WHERE City = 'Houston' SET NOCOUNT OFF SELECT CAST('New York' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'New York' SET NOCOUNT ON SELECT CAST('Salem' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'Salem' SET NOCOUNT OFF SELECT CAST('Chicago' as varchar(10)) [City],AddressLine1 FROM Person.Address WHERE City = 'Chicago' GO
Calling this procedure from a client should result in the rows selected count to be the sum of the rows from the Dallas, New York, and Chicago queries (the count is 20). I wrote a little .Net app to show this.
SqlCommand command = new SqlCommand(); command.Connection = connection; command.Connection.StatisticsEnabled = true; command.CommandText = "CountTest"; command.CommandType = System.Data.CommandType.StoredProcedure; int affected; Int64 rows; connection.Open(); affected = command.ExecuteNonQuery(); System.Collections.IDictionary stats = command.Connection.RetrieveStatistics(); rows = (Int64)stats["SelectRows"];
Most of the time, in a multistatement procedure, the number of rows affected is not useful for any business purposes. However if the rows affected by some combination of statements does have some business use we can use NOCOUNT selectively to influence the value returned to the client.