NOCOUNT Performance: What SET NOCOUNT ON Saves in a Loop

NOCOUNT performance depends on the messages that SQL Server no longer sends. The setting costs one line in a stored procedure. What it saves depends on how many statements the procedure runs.

What the Setting Changes

After every INSERT, UPDATE, DELETE or SELECT, SQL Server sends the client a message such as “(1 row affected)”. The message carries the row count of the statement. SET NOCOUNT ON stops those messages. The statements run the same way, and @@ROWCOUNT still works. For the basics and the scope, read SET NOCOUNT ON in SQL Server: How to Hide Rows Affected Messages.

One message is cheap. A procedure with a loop sends one for every pass. Ten thousand passes send ten thousand messages. They cross the network, and the client has to read each one. In Management Studio, each one also becomes a line in the Messages tab.

A Loop That Sends Thousands of Messages

The demo counts the traffic instead of timing a screen. The script creates a database named NoCountLoopDemo with a table of 1,000 parcels. The procedure runs a WHILE loop of single row updates. A parameter chooses the setting, so the two calls differ in one line only. I prefer set-based code to loops. The loop is here to multiply the messages. Run the script on a test server.

IF DB_ID(N'NoCountLoopDemo') IS NULL CREATE DATABASE NoCountLoopDemo;
GO
USE NoCountLoopDemo;
GO
DROP TABLE IF EXISTS dbo.Parcels;
GO
CREATE TABLE dbo.Parcels (ParcelID int NOT NULL PRIMARY KEY, Label nvarchar(40) NOT NULL);
INSERT INTO dbo.Parcels (ParcelID, Label)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), N'Parcel'
FROM sys.all_columns;
GO
CREATE OR ALTER PROCEDURE dbo.TouchParcels @NoCount bit, @Loops int
AS
BEGIN
    IF @NoCount = 1 SET NOCOUNT ON; ELSE SET NOCOUNT OFF;
    DECLARE @i int = 1;
    WHILE @i <= @Loops
    BEGIN
        UPDATE dbo.Parcels SET Label = Label WHERE ParcelID = 1 + @i % 1000;
        SET @i += 1;
    END;
END;

Each connection keeps a count of the packets SQL Server wrote to it. The column is num_writes in sys.dm_exec_connections. The next script reads it before and after each call. With the setting off, the Messages tab receives 5,000 lines, so run it with care in Management Studio. Run the script twice, because the first run pays for log growth and warm up.

DECLARE @w0 int, @w1 int, @w2 int, @t0 datetime2, @t1 datetime2, @t2 datetime2;
SELECT @w0 = num_writes, @t0 = SYSDATETIME() FROM sys.dm_exec_connections WHERE session_id = @@SPID;
EXEC dbo.TouchParcels @NoCount = 0, @Loops = 5000;
SELECT @w1 = num_writes, @t1 = SYSDATETIME() FROM sys.dm_exec_connections WHERE session_id = @@SPID;
EXEC dbo.TouchParcels @NoCount = 1, @Loops = 5000;
SELECT @w2 = num_writes, @t2 = SYSDATETIME() FROM sys.dm_exec_connections WHERE session_id = @@SPID;
SELECT N'NOCOUNT OFF' AS Setting, @w1 - @w0 AS PacketsWritten, DATEDIFF(MILLISECOND, @t0, @t1) AS Ms
UNION ALL
SELECT N'NOCOUNT ON', @w2 - @w1, DATEDIFF(MILLISECOND, @t1, @t2);

The setting changed the traffic from 47 packets to none. It did not change the time. Both calls took about 700 milliseconds. Your milliseconds will differ, and so can the packet count. A second server wrote 24 packets for the same loop, and the setting still cut them to none.

Why the Time Did Not Move

The demo runs on the same machine as the server, over a connection with no delay. The client reads the messages as fast as they arrive, so the server never waits for it. On such a connection the loop costs the same either way. The messages cost time only when something between the server and the client is slow. A busy network, a remote office and a client that draws every message on screen are all slow places.

This is why tests disagree. On a warm local connection the gap stays small. A slow path can show a bigger one. Neither test is wrong, because they measure different clients. NOCOUNT performance depends on the client and the network. I trust the packet count more than a stopwatch here, because it does not depend on the reader.

A Real Case

A bank client ran a server with over 1 TB of RAM and 256 logical processors. One procedure was slow. It used a cursor and a WHILE loop, and it displayed a lot of rows. It had no SET NOCOUNT ON. Adding the line at the top of the procedure made it more than twice as fast. The client could not rewrite the procedure in the time available, so the one line was the fix.

The number from that case belongs to that server and that client. The demo above explains the mechanism, and it does not promise the same ratio. The saving grows with the number of statements the procedure runs. Some clients draw every message, such as a Messages tab with thousands of lines. Such a client can show a gap like the one in that case.

Where It Helps and Where It Does Not

The setting removes one message for each statement, so NOCOUNT performance follows the statement count. A procedure that runs 150 statements saves 150 messages. If each statement returns 10 to 100 rows, the rows dominate the traffic. The saved messages are a small part. A loop of single row updates is the opposite case. The messages are most of what the loop sends.

The best candidates are procedures that run many statements without returning rows. Loops, cursors, batch loaders and triggers fit that description. A procedure that runs one SELECT and returns one result set gains almost nothing, and loses nothing either.

Is It Worth Adding Everywhere?

You could argue that a setting with no measurable gain on a fast connection is not worth the line. I add it anyway. The cost is one line. The benefit appears on the slow path you did not test. Some code does depend on the message. Old applications can read the row count from it. Test the change before you add it to code you do not own.

Do you need to switch the setting off again at the end of the procedure? No. It applies inside the procedure and ends when the procedure returns, so the caller keeps its own value.

What to Remember

Put SET NOCOUNT ON at the top of every stored procedure. Judge NOCOUNT performance with the packet count if a stopwatch gives mixed answers. Expect the biggest gain in loops and cursors that run many statements, and over a slow network.

When you finish testing, run the cleanup script.

USE master;
GO
ALTER DATABASE NoCountLoopDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE NoCountLoopDemo;

A message is not free, it is a cost you pay once for every statement.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.