Don't Count on Me

This post is in support of Tim Ford’s (blog|twitter) #iwanttohelp challenge. And also written because this has burned me twice in the past 3 months and by blogging about it, hopefully it’ll stick in my mind.

Setup

I’ve recently been doing a bunch of work with stored procedures, trying to improve performance that’s been suffering due to suboptimal queries. Some of this tuning has resulted in creating temporary tables. After making my changes and running the procedures in SSMS, everything looked good - data’s correct, performance metrics are all improved. Everyone wins!

Then I checked the web app. At first, it appeared to work OK. But on reloading the page with different parameters, I got no data, or the data from the previous parameters, or other data that was completely out of left field. Not good!

After a bit of head-scratching, I popped over to the SQL Server Slack and asked for ideas about why I’d be getting different results depending on how the procedure was called. After kicking a few ideas around, someone asked if the procedure included SET NOCOUNT ON. It didn’t, so I added it and my problems were solved!

Explanation

So what happened here?  When you execute a query against SQL Server, both your data and some additional information is sent back to the client. This additional information is sent via a separate channel which is accessible via the SqlConnection.InfoMessages (or if you’re still using classic ADO, the InfoMessage) event. When you run queries in SSMS, you see this information in the Messages tab of the results pane most often as X row(s) affected.

That’s where my new stored procedures were causing problems. Where the original procedures were returning only one event which corresponded to the number of records returned by the single query in each procedure. But now that I’m loading temp tables, I’m getting multiple messages back - at a minimum, a count of the records affected when loading the temp table plus a count of the records returned to the calling application.

I’m not sure what exactly my application was doing with this, but as soon as multiple messages were passed back through InfoMessage(s), it got very confused and started doing unexpected things with the query results. I suspect that it saw multiple events and attempted to use the data associated with the first one, of which there was none because it was just inserting into my temp table.

By starting the stored procedure with SET NOCOUNT ON, InfoMessages is disabled and this additional data isn’t transmitted to the client. It’s also said that this can improve performance (although it’s more about network traffic these days) but my primary interest in using it is to keep client applications that I can’t change from blowing up.

Something I find very interesting is that SSMS ships with two different templates for stored procedures and one does include SET NOCOUNT ON, while the other doesn’t.

Example

Here are three simple stored procedures to demonstrate the effect of this setting.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

CREATE OR ALTER PROCEDURE dbo.GetCounties AS print 'GetCounties';
  select s.name,c.countyname from states s join counties c on s.StateId = c.StateId;
go

CREATE OR ALTER PROCEDURE dbo.GetCounties2 AS
  create table #StatesCounties (
    StateName nvarchar(100)
    ,CountyName nvarchar(100)
  );
  print 'GetCounties2';
  insert into #StatesCounties
    select s.name as StateName,c.countyname
    from states s join counties c on s.StateId = c.StateId;
  
  select StateName,CountyName from #StatesCounties;
  go

CREATE OR ALTER PROCEDURE dbo.GetCounties3 AS
  SET NOCOUNT ON
  create table #StatesCounties (
    StateName nvarchar(100)
    ,CountyName nvarchar(100)
  );
  print 'GetCounties3';
  insert into #StatesCounties
    select s.name as StateName, c.countyname
    from states s join counties c on s.StateId = c.StateId;
  
  select StateName,CountyName from #StatesCounties;

And the result of running each, from the SSMS Messages tab.

GetCounties

(122 row(s) affected)
GetCounties2

(122 row(s) affected)

(122 row(s) affected)
GetCounties3

Note how the first reports the number of rows returned, while the second reports both the number of rows inserted into the temp table and the number returned from the query. In the last example, no messages are returned. In all cases, the print messages are returned because they’re explicitly output by my code.

Summary

• Unless you have a very specific need to get this alternate data stream in your calling application, use SET NOCOUNT ON in your stored procedures
• The next time you’re working in a stored procedure, add it if it’s not already there
• Add it to the template you use for creating new stored procedures