Most seasoned SQL Server administrators and developers will tell you to include SET NOCOUNT ON in all your stored procedure code. The reason generally given is to turn off the unnecessary “n rows affected” message that appears so often in the Management Studio Message pane. However, the “rows affected” message is only the visible tip of a much larger iceberg. SQL Server is a regular chatterbox sending way too much useless information to the client. SET NOCOUNT ON is far more important than most people realize.
Let’s take a look at the network traffic generated by a simple stored procedure. To do this, we’ll use the Client Statistics feature of the Management Studio.
Of course, we’ll need a test store procedure; let’s create one that returns one single row. We’ll include a loop that can iterate a varying number of times as determined by an input parameter.
IF EXISTS (SELECT * FROM sys.sysobjects WHERE
Name = ‘ClientStatsTest’ AND Type = ‘P’)
DROP PROC ClientStatsTest
CREATE PROC ClientStatsTest(@N INT = 10)
— SET NOCOUNT ON
DECLARE @Count INT
SET @Count = 0
WHILE @Count < @N
SET @Count = @Count + 1
A trivially simple stored procedure, to be sure. But let’s note two things. One, the SET NOCOUNT ON statement is commented out. And two, the only SELECT statement in the procedure returns a single integer value.
345 byte was received from the server. Doesn’t seem so bad.
Now let’s take a look at what happens when we run the loop 10,000 times or 1,000,000 times.
When we ran through the loop 10,000 times 260,626 bytes were received from the server, and for one million loop iterations the number jumps to over 26 million bytes. Pretty excessive, considering all our procedure should return is a single row with a single column containing an integer.
SET statements within your stored procedure are treated like SELECT statements even though you never see the return values!
Now we will alter the stored procedure and uncomment the line for SET NOCOUNT ON. If we again run the loop a million times, the results are much more satisfying.
The number of bytes received from the server for one million iterations is 96, which is less than for ten iterations of the loop without SET NOCOUNT ON.
By default, SQL Server returns much more information to the client than the clearly visible “Rows Affected” message. Out of sight might mean out of mind, but it’s still taking up network bandwidth. The folks who have advised you to always SET NOCOUNT ON are more right than they know!
AUTHOR: Dan Buskirk
SQL Server Training