# SET NOCOUNT ON and performance

> Does SET NOCOUNT ON speed up a procedure? Bytes, packets and time measured on SQL Server 2025 over a simulated slow network, and against row-by-row code.
>
> Source : https://www.pachadata.com/en/docs/questions/set-nocount-on-performance/
> Author : Rudi Bruchez · published 2026-11-07 · updated 2026-11-09
> Markdown version of the page, for tools and LLMs. The HTML page is the reference.


*Co-written with Claude, who ran the testing and research.*

> TL;DR :
>
> Without `SET NOCOUNT ON`, a procedure that runs 100,000 `UPDATE` statements in a loop sends 3.9 MB to the client; with it, 75 bytes. These messages go out in full packets, one after the other, without waiting for a reply: so there is no impact on query execution performance. But they cost bandwidth.


## The question

A common best practice for stored procedures is to always start them with `SET NOCOUNT ON`, which stops SQL Server from sending the client, after each statement, the message that gives the number of rows affected (the "(1 row affected)" in SSMS).
This message is the `DONE_IN_PROC` token of the TDS protocol. The [`SET NOCOUNT` documentation](https://learn.microsoft.com/sql/t-sql/statements/set-nocount-transact-sql) says it "can provide a significant performance boost" for procedures that contain loops, because network traffic is greatly reduced. The [`CREATE PROCEDURE`](https://learn.microsoft.com/sql/t-sql/statements/create-procedure-transact-sql) documentation says the opposite: there is no measurable performance benefit on today's hardware.

What does `SET NOCOUNT ON` save, and in which cases? And can it be used to speed up row-at-a-time processing (*RBAR*, *row by agonizing row*)?

## The lab

SQL Server 2025 CU10 (17.0.5035.5), Developer edition, a disposable Linux container limited to 3 GB. A `q_nocount` database in simple recovery, a table of 1,000 rows, and two procedures identical except for one line: each runs `@n` single-row `UPDATE` statements in a `WHILE` loop, inside a transaction, and only the second starts with `SET NOCOUNT ON`.

The client is a Python program (`pymssql` 2.4.6) running on the host. It goes through a TCP relay written for the occasion, which counts the bytes sent by the server and can delay each segment by 10 ms in each direction (20 ms round trip), then limit the bandwidth from server to client to 10 Mbit/s. The number of packets written by the server comes from the `num_writes` column of `sys.dm_exec_connections`, read before and after the measurement. Each measurement was taken three times. The full script is at the end of the page.

## The traffic sent to the client

| Call | Bytes received by the client | Packets written by the server |
|---|---|---|
| 1 call, 100,000 `UPDATE`, without `SET NOCOUNT ON` | 3,907,759 | 956 |
| 1 call, 100,000 `UPDATE`, with | 75 | 2 |
| 1,000 calls of 10 `UPDATE`, without | 496,021 | 1,001 |
| 1,000 calls of 10 `UPDATE`, with | 54,021 | 1,001 |

Without `SET NOCOUNT ON`, each loop iteration costs the client 39 bytes. The [TDS specification](https://learn.microsoft.com/en-us/openspecs/windows_protocols/ms-tds/43e891c5-f7a1-432f-8f9f-233c4cd96afb) gives a `DONE_IN_PROC` token 13 bytes, and says one is sent for each statement executed in a procedure. Three tokens per iteration matches the count of statements in the loop: the `UPDATE`, the increment of `@i` and the `WHILE` test. I did not decode the stream to check it. `SET NOCOUNT ON` removes them all, including those of statements that touch no rows.

The packet count shows how they are sent. The 3.9 MB make 956 packets, that is full packets of 4,096 bytes, the default size: the server does not flush its buffer after each statement, it fills a packet and sends it. When the procedure is called 1,000 times with 10 statements, each response fits in one packet, with or without the messages: 1,001 packets in both cases.

## Latency and bandwidth

Time measured by the client, three runs:

| Call | Network | Without `SET NOCOUNT ON` | With |
|---|---|---|---|
| 1 call, 100,000 `UPDATE` | relay, no delay | 0.41 to 0.76 s | 0.38 to 0.73 s |
| 1 call, 100,000 `UPDATE` | 20 ms round trip | 0.45 to 0.48 s | 0.42 to 0.44 s |
| 1 call, 100,000 `UPDATE` | 20 ms and 10 Mbit/s | 3.92 to 3.96 s | 0.42 to 0.71 s |
| 1,000 calls of 10 `UPDATE` | relay, no delay | 2.02 to 2.16 s | 2.00 to 2.18 s |
| 1,000 calls of 10 `UPDATE` | 20 ms round trip | 28.5 to 31.0 s | 30.2 to 41.1 s |
| 1,000 calls of 10 `UPDATE` | 20 ms and 10 Mbit/s | 31.0 to 32.1 s | 29.9 to 32.8 s |

Latency changes nothing: the messages follow the results in the same stream, and the client does not have to reply to each one. The 1,000 calls cost 1,000 round trips in both variants, and that is what makes the 30 seconds.

Bandwidth changes everything once the volume is large. 3.9 MB at 10 Mbit/s take a little over 3 seconds to transmit, and that is the extra time the call took. For a procedure called often with few statements, the difference is 442 bytes per call, invisible even at 10 Mbit/s.

## Server time and row-at-a-time processing

Same table, this time with 100,000 rows, each updated once. The client is `sqlcmd`, inside the container, and the times come from `sys.dm_exec_procedure_stats`, over five runs:

| Procedure | Average time | Minimum and maximum | Average CPU time |
|---|---|---|---|
| loop of 100,000 `UPDATE`, without `SET NOCOUNT ON` | 399 ms | 389 to 406 ms | 398 ms |
| loop of 100,000 `UPDATE`, with | 402 ms | 388 to 412 ms | 401 ms |
| a single `UPDATE` of the 100,000 rows | 97 ms | 85 to 136 ms | 97 ms |

A second series of five runs, made later with the script on this page as published, gave longer times on a busier machine: 684 ms without `SET NOCOUNT ON`, 714 ms with, and 126 ms for the set-based `UPDATE`. The difference between the two loops remains noise, one way and then the other.

For the server, producing the messages costs nothing measurable. With `SET NOCOUNT ON`, the loop stays just as slow; the option removes the 39 bytes per processed row sent to the client, which only matter on a slow link. The same work written as one set-based statement is four to five times faster, with or without the option.

There are two good reasons to put `SET NOCOUNT ON` at the top of every procedure:

1. to reduce useless network traffic;
2. without it, the row count the application receives adds up the rows of every statement, triggers included. With a trigger that runs two statements, `ExecuteNonQuery` returned 3 for a one-row `INSERT`, instead of 1.

## Limits of the measurements

- Measured on a single version, SQL Server 2025 CU10, on Linux, and a single driver, `pymssql`. A client that processes each message more slowly, such as the SSMS Messages pane, was not measured.
- The network is simulated by a Python relay on the same machine. Its bandwidth limit delays the data after reading it: the server never waited for the client (no `ASYNC_NETWORK_IO` wait), whereas on a real slow link it would.
- The packet size is the default, 4,096 bytes.
- I did not decode the TDS stream: the three tokens per loop iteration are inferred from the size and the specification, not observed.

## The script

The script creates and drops its disposable database. It contains a `DBCC FREEPROCCACHE`, which clears the plan cache of the whole instance: run it only on a test instance.

```sql
-- SET NOCOUNT ON: server time of a loop, with and without, against a set-based UPDATE
SET NOCOUNT ON;
USE master;
IF DB_ID(N'q_nocount') IS NOT NULL
BEGIN
    ALTER DATABASE q_nocount SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE q_nocount;
END;
CREATE DATABASE q_nocount;
ALTER DATABASE q_nocount SET RECOVERY SIMPLE;
GO
USE q_nocount;
CREATE TABLE dbo.Compteur (id int PRIMARY KEY, valeur int NOT NULL);
INSERT dbo.Compteur (id, valeur)
SELECT TOP (1000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0 FROM sys.all_columns;
GO
-- A procedure that runs @n single-row UPDATE statements, in a transaction
CREATE OR ALTER PROCEDURE dbo.BoucleSansNocount @n int AS
BEGIN
    DECLARE @i int = 0;
    BEGIN TRAN;
    WHILE @i < @n BEGIN UPDATE dbo.Compteur SET valeur += 1 WHERE id = 1 + @i % 1000; SET @i += 1; END;
    COMMIT;
END;
GO
CREATE OR ALTER PROCEDURE dbo.BoucleAvecNocount @n int AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @i int = 0;
    BEGIN TRAN;
    WHILE @i < @n BEGIN UPDATE dbo.Compteur SET valeur += 1 WHERE id = 1 + @i % 1000; SET @i += 1; END;
    COMMIT;
END;
GO

DROP TABLE IF EXISTS dbo.Ligne;
CREATE TABLE dbo.Ligne (id int PRIMARY KEY, valeur int NOT NULL);
INSERT dbo.Ligne SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0 FROM sys.all_columns a CROSS JOIN sys.all_columns b;
GO
CREATE OR ALTER PROCEDURE dbo.LigneParLigneSansNocount AS
BEGIN
    DECLARE @i int = 1;
    BEGIN TRAN;
    WHILE @i <= 100000 BEGIN UPDATE dbo.Ligne SET valeur += 1 WHERE id = @i; SET @i += 1; END;
    COMMIT;
END;
GO
CREATE OR ALTER PROCEDURE dbo.LigneParLigneAvecNocount AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @i int = 1;
    BEGIN TRAN;
    WHILE @i <= 100000 BEGIN UPDATE dbo.Ligne SET valeur += 1 WHERE id = @i; SET @i += 1; END;
    COMMIT;
END;
GO
CREATE OR ALTER PROCEDURE dbo.Ensembliste AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.Ligne SET valeur += 1;
END;
GO
DBCC FREEPROCCACHE;  -- disposable container: resets the procedure statistics
GO
EXEC dbo.LigneParLigneSansNocount;
EXEC dbo.LigneParLigneAvecNocount;
EXEC dbo.Ensembliste;
GO 5
SELECT OBJECT_NAME(object_id) AS proc_, execution_count,
       total_elapsed_time / execution_count / 1000 AS ms_moyen,
       min_elapsed_time / 1000 AS ms_min, max_elapsed_time / 1000 AS ms_max,
       total_worker_time / execution_count / 1000 AS cpu_ms_moyen
FROM sys.dm_exec_procedure_stats WHERE database_id = DB_ID();
GO
```

The Python program that measures bytes, packets and times through the relay, to run after the previous script. It connects to port 11990 on the local machine and reads the `sa` password from the `SQLCMDPASSWORD` variable.

```python
"""SET NOCOUNT ON measurement: client time, bytes received, packets written by the server.
A local TCP relay adds a fixed delay in each direction (simulated latency) and counts the bytes."""
import asyncio, os, sys, time, threading, statistics
import pymssql

CIBLE = ("127.0.0.1", 11990)
recu = 0  # bytes server -> client

async def relais(lecteur, ecrivain, delai, compter, debit=None):
    global recu
    file = asyncio.Queue()
    async def ecrire():
        while True:
            t, data = await file.get()
            if data is None:
                ecrivain.close(); return
            attente = t - time.monotonic()
            if attente > 0:
                await asyncio.sleep(attente)
            ecrivain.write(data); await ecrivain.drain()
            if debit:  # bytes per second: wait for the segment's transmission time
                await asyncio.sleep(len(data) / debit)
    tache = asyncio.create_task(ecrire())
    while True:
        data = await lecteur.read(65536)
        if not data:
            await file.put((0, None)); break
        if compter:
            recu += len(data)
        await file.put((time.monotonic() + delai, data))
    await tache

def lancer_relais(port, delai, debit=None):
    async def client(r, w):
        sr, sw = await asyncio.open_connection(*CIBLE)
        await asyncio.gather(relais(r, sw, delai, False), relais(sr, w, delai, True, debit), return_exceptions=True)
    async def main():
        srv = await asyncio.start_server(client, "127.0.0.1", port)
        async with srv: await srv.serve_forever()
    threading.Thread(target=lambda: asyncio.run(main()), daemon=True).start()
    time.sleep(0.3)

def mesure(port, sql, appels):
    global recu
    cn = pymssql.connect(server="127.0.0.1", port=port, user="sa", password=os.environ["SQLCMDPASSWORD"], database="q_nocount", autocommit=True)
    cn.cursor().execute("EXEC dbo.BoucleAvecNocount @n = 1")  # warm-up
    cur = cn.cursor()
    cur.execute("SELECT num_writes FROM sys.dm_exec_connections WHERE session_id = @@SPID"); w0 = cur.fetchone()[0]
    recu = 0
    t0 = time.perf_counter()
    for _ in range(appels):
        cur.execute(sql)
        while cur.nextset():
            pass
    dt = time.perf_counter() - t0
    octets = recu
    cur.execute("SELECT num_writes FROM sys.dm_exec_connections WHERE session_id = @@SPID"); w1 = cur.fetchone()[0]
    cn.close()
    return dt, octets, w1 - w0 - 1  # -1: the response to the first reading

if __name__ == "__main__":
    lancer_relais(12000, 0.0)     # relay with no delay
    lancer_relais(12010, 0.010)   # 10 ms each way, 20 ms round trip
    lancer_relais(12020, 0.010, 10e6 / 8)  # same, plus 10 Mbit/s from server to client
    scenarios = [
        ("1 call, 100,000 UPDATE", 100000, 1),
        ("1,000 calls, 10 UPDATE", 10, 1000),
    ]
    passes = int(sys.argv[1]) if len(sys.argv) > 1 else 3
    for nom, n, appels in scenarios:
        for port, reseau in ((12000, "relay 0 ms"), (12010, "relay 10 ms"), (12020, "10 ms + 10 Mbit/s")):
            for proc in ("BoucleSansNocount", "BoucleAvecNocount"):
                res = [mesure(port, f"EXEC dbo.{proc} @n = {n}", appels) for _ in range(passes)]
                temps = ", ".join(f"{r[0]:.2f}" for r in res)
                print(f"{nom} | {reseau} | {proc} | {temps} s | {res[-1][1]} bytes | {res[-1][2]} packets", flush=True)
```

Finally, dropping the database:

```sql
USE master;
ALTER DATABASE q_nocount SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE q_nocount;
```


---

## About the author

Rudi Bruchez, independent SQL Server consultant since 2006: performance audits, tuning, migrations, high availability and training. For an audit or hands-on help on your server: https://www.pachadata.com/en/contact/ (remote audit: https://www.pachadata.com/en/services/remote-sql-server-audit/).