I just had a sudden flashback to my high school English class…oh wait, this is SQL Server, not Shakespeare…
Today I’d like to cover the best practice of using the command SET NOCOUNT ON at the top of all of your stored procedures. What does it do? Why is this a best practice?
SET NOCOUNT ON is a T-SQL statement that toggles the return of the number of rows affected by each DML statement…you know, this one:
(1 row(s) affected)
This setting is set off by default (it’s a connection property), so you will usually see these messages when running queries and when executing stored procedures. Your DBA can set NOCOUNT on by default for all connections to your SQL Server, but I don’t think you want to do that (because then you will need to set it off when you do want to see them).
SET NOCOUNT ON turns “off” the return of the number of rows affected, and SET NOCOUNT OFF turns it back “on”…I know, it sounds backwards, but that’s what you get when the command (i.e. “nocount”) is in the negative…negative times negative = positive… but I digress… This is handy information to have when you are performing ad hoc queries or data fix scripts, but a T-SQL best practice is to turn off these messages for stored procedures.
So the question is… why? The answer is pretty easy…performance. SQL Server has to return this “number of rows affected” statement back to the calling program for every DML statement issued…not a big deal when it’s just you and your local development environment, but when your stored procedure is executed by the 400 users using your application…all of those little messages add up. These messages are basically ignored by your application, so the extra network traffic alone reason enough to justify adding this statement to your stored procedures.
How much does this affect performance? The Books Online just indicate that “it can provide a significant performance boost”. Yeah, but how much is “significant”? Of course I wanted to put it to the test, or find someone who already did. Does it really make my procedure run faster if I turn off those messages?
I found this article, which for me hit the nail on the head…Brian Moran did an effective job of testing and showing that, even on his sample laptop (i.e. no network traffic!), using SET NOCOUNT ON improved execution time (since no messages needed to be created/sent). Here is a link to his article:
This article was written in 2001 and tested against SQL 2000; could Microsoft have improved this by now? 15 years is an eternity in this business…I had to test it out…So I did. I wrote my own stored procedure based on Brian’s example (see the article for details), and ran it on my own laptop here in 2016 using SQL Server 2014.
My results? I ran my procedure a number of times. My average time of execution with no SET statement was 418.2 milliseconds. My average time of execution using SET NOCOUNT ON was 150 milliseconds. So returning all of those affected records statements to my application (my application was SSMS, but it could be any code that connects to SQL Server) cost me over 250 milliseconds for each execution. I didn’t measure the network traffic that would be generated by these statements. Remember, these statements are sent from your database server to your application or application server (for us, a web server), and then discarded, causing unnecessary traffic and work for your application.
You say that’s small potatoes…it’s less than half a second no matter which way I executed! True, but this is one procedure, called one time, from one user…multiply the savings out by the number of procedures running on your production database server at any given time and this quarter of a second turns into real savings on the server; resources you can use more wisely elsewhere!