}

SET NOCOUNT ON Is More Important Than You Think

4/24/2018

[:en]

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.

SET NOCOUNT ON screenshot

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

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. 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.

SET NOCOUNT ON screenshot 03

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.

SET NOCOUNT ON screenshot 04

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.



Conclusion

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

Related Training:
SQL Server Training [:]

Dan Buskirk

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.

Chat With Us