Dangers of Parameter Sniffing in MSSQL

Here we’ll consider quite a specific area of SQL optimisation, that of stored procedures that accept a number of parameters. While there’s a wealth of information available online about optimisation, in this situation certain factors come into play to make things more interesting.

So you’ve found that there’s a problem in your SQL solution – its CPU usage is high or spiking, and queries are sometimes taking a long time to complete. You’ve run SQL Server Profiler to look at the Duration of “SP:Completed” events, and sure enough some of the execution times are big.
But sometimes they’re small.
And it varies for calls to the same stored procedure… perhaps by a factor of hundreds!
What’s going on?!

It’s possible that SQL Server performing parameter sniffing is causing your stored procedures to run slow. It’s also possible that in attempting to fix things, you’ll do some parameter sniffing of your own and make things worse!

So what is parameter sniffing?

First, if you don’t already, you need to understand a little about execution plans. These are created and used internally by SQL Server, and essentially tell SQL Server how to execute a specific query (e.g. which index to use at a certain point, whether to use an index seek or a full table scan, etc.).
There are two crucial points that affect us:

  • The execution plan is typically created once and then re-used for subsequent calls to the same query.
  • When optimising the execution plan, the parameters that are passed in for that particular call are taken into account. This is parameter sniffing.

So parameter sniffing isn’t necessarily a bad thing – SQL Server does it with all the best intentions, and in most circumstances it helps to execute queries more efficiently.
But by now you’ve probably realised how problems can arise:

SQL Server optimises the execution plan for a stored procedure call, and that call executes nice and fast. However, a new call to the same stored procedure – but with different parameters – will re-use the execution plan from before. But now, the old execution plan could be de-optimised for the new parameters (sometimes very significantly), and the second call executes very slowly.

Note that there’s not necessarily anything more taxing about the parameters in the second call. You’ll likely find that if the calls were made in reverse order, it would still be the first one executed that performs well, and the second one executed that performs badly.
This is why the situation can be very unpredictable – chance differences in which parameters are used to build the execution plan can result in huge differences in execution time for subsequent queries.

What can we do about it?

There are a few different approaches that can be taken, and which one(s) to use will need to be considered for your specific situation.

  • Split your stored procedure into many, so that each can be more specialised.
    This will reduce the number of parameters you need to pass in, and reduce the variance in the execution paths of your stored procedure. Execution plans will still be created, but will be less likely to perform badly for different parameter sets.
  • Tell SQL Server exactly how to optimise the execution plans.
    If you have a good understanding of both the nature of your data and the variety of parameters that your stored procedure will be called with (and only if!), you can use the query hint OPTIMIZE FOR. You can therefore tailor the execution plan to suit an ‘average’ parameter set. Note that this is only available for SQL 2005 onwards.
  • Tell SQL Server to create a new execution plan every time.
    Use WITH RECOMPILE on the call to the stored procedure or on its definition. This will cause execution plans to be created for every call, and so never re-used. You can expect some extra overhead for the plan creation, but it’s unlikely to be significant compared to the duration of the queries themselves.
    If using SQL 2005 onwards, you can also use the RECOMPILE query hint to instruct SQL Server to recompile just a single statement within your stored procedure. Used carefully, this can yield the best results, as it avoids re-use of troublesome parts of the execution plan while allowing other parts to be re-used effectively.
  • Prevent SQL Server from successfully sniffing your parameters.
    At the start of your stored procedure, declare a variable for each parameter, set the values directly from the parameters, and then use the variables instead of the parameters within your query. This sidesteps SQL Server’s parameter sniffing and forces it to create execution plans that cater for unknown parameters, which generally provides good results.

You’ll probably want to try a couple of different techniques to see which works best in your situation.

Is that the end of the story?

In addition to increasing the stability of your database by avoiding problems caused by parameter sniffing, it’s always a good idea to reduce the execution time of stored procedures across the board where possible. There’s a lot of literature available about this so I won’t go into details, but will leave you with a piece of advice for when tuning parameterised stored procedures:

Make sure you test your changes with representative data and representative parameters.

Use SQL Server Profiler if necessary to monitor the variety of parameters being used on your live environment.
If you fail to take the variance of parameters into account, you’ll essentially be doing your own parameter sniffing – that is, optimising (in this case the stored procedure itself rather than the execution plan) for a specific parameter set, and potentially de-optimising for others.

Query hints and keywords can stop SQL Server from optimising badly, but no such measures exist for a poorly-informed database developer…

Some useful articles




Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s