SET NOCOUNT ON Is More Important Than You Think

5/1/2023

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 often appears in the Management Studio Message pane. However, the "rows affected" message is only the visible tip of a much more giant iceberg. SQL Server is a regular chatterbox sending too much useless information to the client. Therefore, SET NOCOUNT ON is far more important than most people realize.

Let's 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.

Testing It

Of course, we'll need a test store procedure; let's create one that returns one row. We'll include a loop that can iterate various times as determined by an input parameter.

IF EXISTS (SELECT * FROM sys.sysobjects WHERE
Name = 'ClientStatsTest' AND Type = 'P')
DROP PROC ClientStatsTest
GO
CREATE PROC ClientStatsTest(@N INT = 10)
AS
-- SET NOCOUNT ON
DECLARE @Count INT
SET @Count = 0
WHILE @Count < @N
BEGIN
SET @Count = @Count + 1
END
SELECT @Count
GO

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.

Scaling Our Experiment

Now let's 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 procedures should return 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!

Tweaking the Experiment

We will alter the stored procedure and uncomment the line for SET NOCOUNT ON. Then, if we run the loop a million times again, the results are much more satisfying.

The number of bytes received from the server for one million iterations is 96, less than ten iterations of the loop without SET NOCOUNT ON.

Conclusion

By default, SQL Server returns much more information to the client than the visible "Rows Affected" message. Out of sight might mean out of mind, but it's still taking up network bandwidth. So the folks who have advised you to always SET NOCOUNT ON are more suitable than they know!

 

Dive deeper into this topic with SQL Training from Learning Tree!

 

This piece was originally posted on Apr 24, 2018, and has been refreshed with updated styling.