Tag Archives: Query Optimizer

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.