The case against SET NOCOUNT ON

Whenever you execute a SQL query on Microsoft SQL Server, you receive a message like this after the results:

5 row(s) affected.

The number of rows reflects the rows returned or affected, if you are using INSERT or UPDATE. SQL Server doesn’t return a plain text response but a 9-byte packet called DONEINPROC. The performance impact of this packet has been a subject for debate for a while.

There is a statement to prevent that packet from being returned in the session. It’s called SET NOCOUNT ON. If NOCOUNT is set, no queries in the same connection would return the number of rows, until connection is closed or reset.

The debate over using NOCOUNT setting has been overblown because somebody in the past said it was a good idea to set it. Heck even Microsoft suggested it once in their book Improving .NET Application Performance and Scalability. I’ve seen numerous popular software that starts every query with a SET NOCOUNT ON statement.

There was an excellent blog post by Dale Burnett that delved deep into how SET NOCOUNT ON worked. It concluded that if we are running a single-result query continuously the bandwidth savings could be significant. It also said that other than that it might not be worth it. But I claim that using SET NOCOUNT ON would lose you bandwidth always.

First, SELECT queries always include the row count regardless of NOCOUNT setting unless you’re opening the results as a cursor. You don’t gain or lose anything in SELECTs with NOCOUNT. There goes 90% of your queries.

INSERTs are no brainer, you already know the row count. There is only the case of INSERT using SELECT as the data source which might interest you about the number of rows inserted. There are other options than row count to retrieve that information. For instance, you can run SELECT separately into a temporary or in-memory table and retrieve the row count from there. You can run a counting query separately too. But just accessing the “impacted row count” is probably the fastest way.

UPDATEs are where the affected row count information shines. ORMs like LINQ to SQL actually uses that behavior to implement optimistic concurrency:

UPDATE field1 = value2 WHERE id = idAND field1 = value1

Although field1 is not needed in the WHERE part of the query, it is included. This way, if its value changes before the query executes the query, it would then return “0 row(s) affected”, allowing the client to detect the conflict and handle it.

Of course an ORM could run a SELECT first to see if it’s changed but it would neither be atomic nor fast. The row count information is a critical piece of information to implement that functionality.

Why NOCOUNT is slower

DB connections are expensive to establish and limited in numbers. If you keep a DB connection open throughout the lifetime of your code, you are wasting resources. If you want to open connection each time to execute a query, now you are spending a lot of time opening connections.

To avoid these two problems “connection pooling” is used. The process opens a specified number of connections, and hands them over to you whenever you ask for one. When you close the connection, it is not actually closed but “reset” by calling sp_reset_connection stored procedure so it stays in a fresh state for the next connection.

To get maximum efficiency from connection pooling, one must keep the connections open as short as possible. It is practically a loop of “open, execute, close” operations.

Because of that pattern with connection pooling, SET NOCOUNT ON needs to be included at the beginning of each statement your application needs to execute. That means you are at least adding 15 bytes to your query text to save from 9 bytes of response. You are actually consuming more bandwidth when trying to turn off the row count information.

I've seen “parsing overhead in TDS layer” argument brought up. DONEINPROCis a fixed memory structure and it is not an extra token, it is a replacement for other response formats. There is no measurable overhead. On the other hand, there is actually more parsing overhead to parse the text “SET NOCOUNT ON” for uncached queries.

All in all

• The default behavior to include row count information in the results can make certain INSERT/UPDATE scenarios faster.
• SET NOCOUNT ON doesn’t save you bandwidth on modern DB programming models. In fact it consumes more bandwidth for no benefit.
• It doesn’t gain you performance. It could even hinder it due to higher bandwidth requirements and more parsing required.
• It doesn’t make any difference at all for SELECT queries as long as you’re not using a cursor.

My suggestion is, avoid stigmatizing row count information. I doubt there is any legitimate use for SET NOCOUNT ON at all for plaintext queries. I’m not sure about stored procedures either. I suggest everyone what my grandfather used to tell me: “Stick to the defaults kid, and avoid premature optimization”. Or, something like that.