SET NOCOUNT ON Is More Important Than You Think

2023-05-01

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.

SET NOCOUNT ON screenshot 02 — *345 byte was received from the server. It doesn't seem so bad.*

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.

Written by Dan Buskirk

"The pleasures of the table belong to all ages." Actually, Brillat-Savaron was talking about the dinner table, but the quote applies equally well to Dan’s other big interest, tables of data. Dan has worked with Microsoft Excel since the Dark Ages and has utilized SQL Server since Windows NT first became available to developers as a beta (it was 32 bits! wow!). Since then, Dan has helped corporations and government agencies gather, store, and analyze data and has also taught and mentored their teams using the Microsoft Business Intelligence Stack to impose order on chaos. Dan has taught Learning Tree in Learning Tree’s SQL Server & Microsoft Office curriculums for over 14 years. In addition to his professional data and analysis work, Dan is a proponent of functional programming techniques in general, especially Microsoft’s new .NET functional language F#. Dan enjoys speaking at .NET and F# user’s groups on these topics.