Performance Surprises and Assumptions : SET NOCOUNT ON

If you've ever used Management Studio, this output message will probably look familiar:

This comes from SQL Server's DONE_IN_PROC message, which is sent at the successful completion of any SQL statement that has returned a result (including the retrieval of an execution plan, which is why you see two of these messages when you've actually only executed a single query).

You can suppress these messages with the following command:

SET NOCOUNT ON;

Why would you do that? Because these messages are chatty and often useless. In my Bad Habits and Best Practices presentations, I often talk about adding SET NOCOUNT ON; to all stored procedures, and turning it on in application code that submits ad hoc queries. (During debugging, though, you might want a flag to turn the messages back on, as the output can be useful in those cases.)

I always added the disclaimer that the advice to turn this option on everywhere isn't universal; it depends. Old-school ADO recordsets actually interpreted these as resultsets, so adding them to the queries after the fact could actually break application(s) that are already manually skipping them. And some ORMs (cough NHibernate cough) actually parse the results to determine the success of DML commands (ugh!). Please test your changes.

I know that at one point I had proven to myself that these chatty messages could impact performance, especially over a slow network. But it's been a long time, and last week Erin Stellato asked me if I had ever formally documented it. I haven't, so here goes. We'll take a very simple loop, where we'll update a table variable a million times:

SET NOCOUNT OFF;

DECLARE @i INT = 1;
DECLARE @x TABLE(a INT);
INSERT @x(a) VALUES(1);

SELECT SYSDATETIME();

WHILE @i < 1000000
BEGIN
  UPDATE @x SET a = 1;
  SET @i += 1;
END

SELECT SYSDATETIME();

A couple of things you might notice:

• The messages pane is flooded with instances of the (1 row(s) affected) message:

  

• The initial SELECT SYSDATETIME(); does not present itself in the results pane until after the entire batch has completed. This is because of the flooding.
• This batch took about 21 seconds to run.

Now, let's repeat this without the DONE_IN_PROC messages, by changing SET NOCOUNT OFF; to SET NOCOUNT ON; and run it again.

While the messages pane was no longer flooded with the row(s) affected messages, the batch still took ~21 seconds to run.

Then I thought, wait a second, I know what's going on. I'm on a local machine, with no network involved, using Shared Memory, I have only SSD and gobs and gobs of RAM...

So I repeated the tests using my local copy of SSMS against a remote Azure SQL Database - a Standard, S0, V12. This time, the queries took a lot longer, even after reducing the iterations from 1,000,000 to 100,000. But again there was no tangible difference in the performance whether DONE_IN_PROC messages were being sent or not. Both batches took about 104 seconds, and this was repeatable over many iterations.

Conclusion

For years, I had been operating under the impression that SET NOCOUNT ON; was a critical part of any performance strategy. This was based on observations I had made in, arguably, a different era, and that are less likely to manifest today.

That said, I will continue to use SET NOCOUNT ON, even if on today's hardware there is no noticeable difference in performance. I still feel pretty strongly about minimizing network traffic where possible. I should consider implementing a test where I have much more constrained bandwidth (maybe someone has an AOL CD they can lend me?), or have a machine where the amount of memory is lower than Management Studio's output buffer limits, to be sure that there isn't a potential impact in worst-case scenarios. In the meantime, while it might not change the perceived performance about your application, it might still help your wallet to always turn this set option on, especially in situations like Azure - where you may be charged for egress traffic.