Tag Archives: SQLServer

A SQL Stored Procedure Parameter Sniffing Gotcha

This is another one of those occasional posts that is primarily for my own benefit to remind me of a particular problem, but that I’m posting publicly in case it could be of use to someone else.

On one of our systems we have a stored procedure to pull back all of the staff details for a particular project. Initially the screen used LINQ queries, but as anybody who has used LINQ can tell you in certain situations the queries it produces can become quite unwieldy and slow, so in places like that we’ve swapped to using stored procedures. The stored procedure is really simple consisting of one query that takes the two stored procedure parameters to identify the project the staff list is required for. Anyway, on our test systems the stored procedure has been running really well returning the staff details in under a second.

However that hasn’t been the case on the live system. The same query on our biggest project has been slow. Not just slightly slow, go make a cup of coffee (including picking and grinding the coffee beans), do the Times Jumbo Crossword type slow. But when you take the query that the stored procedure uses out and run it directly in a SQL Management Studio query window it returns in under a second, indeed if the same project in our User Acceptance Test server which is essentially an older copy of the live database returns in a similar high speed. It’s something in particular about the live server.

Not surprisingly this has caused a good deal of head scratching, but on Friday afternoon I finally solved the mystery and found what was causing the slow down thanks to this blog post.

To understand what is going on you need to remember a few things about how SQLServer works:

  • SQLServer processes queries differently depending on a number of factors including how many results it thinks the query is going to produce, the indexes on the tables, how the data is arranged in the tables and how the data is arranged on the disk to name a few.
  • When you create a stored procedure SQLServer builds these execution plans only once, the first time the query is run and uses these execution plans every subsequent time the stored procedure is called.
  • If you use a stored procedure parameter in a query within that procedure the query optimiser uses the values of those parameters in the execution plan, if you use local variables the query optimiser creates a more generic plan. (This is called Parameter Sniffing)

Having asked around, most SQLServer users are aware of the query optimiser, many are aware that SQLServer builds the query execution plan once – although they may not know exactly when, but relatively few, including a good few DBA’s will be aware of the difference in the way parameters and local variables are treated by the optimiser.

When you bear in mind that we have a mixture of different sized projects in our system, it starts to become rather obvious what has happened and why the query is running very slowly on one server but not on others. On some servers the first call of the stored procedure was for a small project, whilst on others it was a big project, as a result the SQLServer’s have created different execution plans and that is favouring particular project sizes. Unfortunately on the live server the query plan is totally unsuitable for the project with hundreds of staff members, hence the hideously slow performance.

All I did was change the parameters in the query to be local variables, and then set the value of those local variables to be the value of the parameters – two extra lines and a tweak of the query, and the query started returning in under a second as for all the other servers. By virtue of having a generic query plan the performance of the query is not going to be quite as good as one targeting a particular project size, but in a system where we are storing a wide variety of project sizes a generic plan is what is needed.

At this point, having found the problem I started looking at other stored procedures that could potentially exhibit similar problems – as a general rule I’d recommend not putting parameters directly into queries.

If you want a more detailed explanation, complete with a simple worked example of what to do, check out this SQL Garbage Collector post.

A Request

If anybody is writing .Net code that in the future I am going to have to find an error in, can they refrain from doing things like this:

catch (Exception e)
throw new ApplicationException("ConnectionException has occured: " + e.Message);

The particular application I was working on this morning used this particular gem pretty well everywhere. The problem was that we were getting an unhandled ApplicationException – the re-thrown error wasn’t being trapped anywhere else – and since the above bit of code throws away the stack trace from the original exception, we were left trying to guess where the error was actually occurring.

The irony is that the code was written by a contractor who apparently had a pretty stellar CV, and amongst other things quite regularly criticised other people on the team over their apparent bad programming. His contract wasn’t renewed, and since then we’ve been discovering quite how bad his programming really is – a definite lesson that however good a contractor appears on paper, you need to properly monitor what they are doing.

Other gems in this particular application include every SQL statement being built by concatenating strings together – and no, not a StringBuilder in sight. Using ToString() to do the conversions where needed, including quite a few points where he calls ToString() on a string – indeed a large amount of the data is just held as strings anyway, only being converted back to integers or dates in the stored procedures when SQLServer needs the right types. He had also written his own replace function, which he was using instead of String.Replace although it was functionally identical, and that wasn’t the only place he’d hand crafted code that replicated functionality in the framework. The code was littered with other common errors, for example it’s a good job we don’t have anybody with the surname O’Reilly…

Since we were fixing a specific problem, much as I would like to, there wasn’t the opportunity to fix all of these other problems, essentially we have to wait until something else breaks, and fix it then. And the source of the problem today? A stored procedure that was trying to convert one of the multitude of strings being passed through back to an integer in order to use it. That conversion was failing and throwing an error that was passed back to the client and helpfully chucked away by the error handling code I started with – the only clue we had as to where it was going wrong was that the error message seemed like it was coming from SQLServer.