SQL Server Performance Tuning

What Is SQL Server Parameter Sniffing?

Updated
7 min read
Written by
Mark Varnas

Parameter sniffing and its impact on query performance

SQL Server generates a query plan for a stored procedure using (sniffing) the parameters sent by the first time.

By ‘first time,’ it is meant that SQL Server is forced to compile a stored procedure because it is not present in the procedure cache.

All subsequent executions of that same query will go to the cache to reuse that same initial query plan. This saves SQL Server time from having to regenerate a new query plan (unless there is a reason for recompilation).

A potential issue occurs when the initial execution of a stored procedure produces a plan that’s suitable for that specific set of parameters but inefficient for others more commonly used.

An optimal execution plan for one set of parameters might be an index scan operation, whereas another set of parameters might be better resolved using an index seek operation.

Parameter sniffing can lead to inefficient execution plans, especially when a stored procedure is called with parameter values that have different cardinality.

When is it bad? When is it good?

Most of the time it’s good. It means SQL Server is caching and reusing query plans to make your queries run faster, saving the CPU and memory that would be used to compile the execution plan again.

Parameter sniffing is only bad when your data values are unevenly distributed and cached query plans are not optimal for all values.

When a stored procedure executes efficiently sometimes, but inefficiently at other, and you swear nothing else in the environment is changing, that’s often a case of parameter sniffing.

How to deal with it when it is bad?

There are many ways you can deal with bad parameter sniffing.

Here are just a few of them:

  • Dynamic SQL;
  • Dynamic SQL with parameters;
  • Dynamic SQL with OPTION (RECOMPILE)
  • Hint OPTIMIZE FOR UNKNOWN
  • Hint OPTIMIZE FOR VALUE
  • Hint OPTION (RECOMPILE)
  • Trace flag 4136
  • ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF

There often is not an obvious fix, and as a result, we implement hacks or poor choices to resolve the latency and allow us to move on with life as quickly as possible.

An immense percentage of the content available online encourages the administrator to take shortcuts that do not truly fix a problem.

Bad parameter sniffing case

Let’s kick off with a short demonstration of how parameter sniffing can influence your performance.

Here, we are creating the table Customers_Red9 with two non-clustered indexes (IX_Name and IX_Birthdate). The table has 1 million rows, that were filled with some random data using Redgate SQL Data Generator.

Now, we create a multi-purpose stored procedure that returns the top 100 rows for the given @Name and @Birthdate. Also, it returns all rows if @Name or @Birthdate is not supplied (top 100).

I will call this stored procedure with @Name = ‘Bobbie Dalton‘ parameter supplied (there is nothing in the procedure cache at this point), so the execution plan will be created with @Name = ‘Bobbie Dalton‘ and @Birthdate = NULL.

The execution time for the stored procedure call above is 121 ms.

The second procedure call will be supplying @Name = NULL and @Birthdate = ‘20060107’.

The execution time for this procedure call is 1757 ms.

This query spent 14,5x more time to execute. I suspect that the second procedure call is suffering from a bad parameter sniffing problem. Let’s try to prove that.

If we look at the execution plan of the second query, we will see that the index on the Name column is being used and not the index on Birthdate. The plan retrieved from the plan cache and that compiled value for @Birthdate is NULL and run-time value is ‘2006-01-07’.

We have confirmed that this is a bad parameter sniffing problem.

Now, how to deal with it?

I have already enumerated various methods of how to deal with bad parameter sniffing problems and now for this sample, I will use the hint OPTION (RECOMPILE).

The new stored procedure looks like this:

Running again the first query, using @pName parameter:

The second query, using @pBirthdate:

What the RECOMPILE hint does is force the SQL Server to generate a new execution plan every time these queries run.

This way for the two above procedure calls we will get two different execution plans as you can see below:

This approach eliminates our parameter sniffing issue by prompting the SQL Server to regenerate the query plan each time the query is executed.

The disadvantage here is that we lose all benefits from SQL Server caching execution plans to save CPU cycles.

This can be devastating for SQL Server resources in heavy transactional systems that execute the same procedure very often.

  • If your parameter-sniffed query is complex or is getting run frequently, RECOMPILE is probably a bad idea.
  • If your parameter-sniffed query doesn’t get run often, or if the query doesn’t run often enough to stay in the query plan cache anyway, then RECOMPILE sounds like a good solution.

Conclusion

It is important to be reminded that parameter sniffing is a feature of SQL Server, not a bug or something that is wrong.

Most of the time parameter sniffing is helping SQL Server to use less CPU and memory and when it goes bad on us you know how to deal with it.

We should not automatically seek workarounds, hacks, or cheats to make the problem disappear. Many quick fixes exist that will resolve a problem for now and allow us to move on to other priorities.

Before adding query hints, SQL Server trace flags, or otherwise hobbling the query optimizer, consider every alternate way to improve performance.

Local variables, dynamic SQL, RECOMPILE, and OPTIMIZE are too often cited as the best solutions, when in fact they are typically misused.

When a performance problem due to parameter sniffing occurs frequently, it is more likely the result of design and architecture decisions than a query optimizer quirk.

Instead of creating hacks to solve problems quickly, we can make code more durable at little additional cost to developers. The result will be saved time and resources in the long run and an application that performs more consistently.

Agree? Disagree? Let’s talk in the comments.

Article by
Mark Varnas
Founder | CEO | SQL Veteran
Hey, I'm Mark, one of the guys behind Red9. I make a living performance tuning SQL Servers and making them more stable.

Managed SQL Server services, consulting, and emergency support from expert DBAs to improve performance, predictability, and cost.

Get started with Red9 today.

Contact us

Discover More

SQL Server Health Check SQL Server Migrations & Upgrades SQL Server Performance Tuning SQL Server Security SQL Server Tips

Discover what clients are saying about Red9

Red9 has incredible expertise both in SQL migration and performance tuning.

The biggest benefit has been performance gains and tuning associated with migrating to AWS and a newer version of SQL Server with Always On clustering. Red9 was integral to this process. The deep knowledge of MSSQL and combined experience of Red9 have been a huge asset during a difficult migration. Red9 found inefficient indexes and performance bottlenecks that improved latency by over 400%.

Rich Staats 5 stars
Rich Staats
Cloud Engineer
MetalToad

Always willing to go an extra mile

Working with Red9 DBAs has been a pleasure. They are great team players and have an expert knowledge of SQL Server database administration. And are always willing to go the extra mile to get the project done.
5 stars
Evelyn A.
Sr. Database Administrator

Boosts server health and efficiency for enhanced customer satisfaction

Since adding Red9 to the reporting and DataWarehousing team, Red9 has done a good job coming up to speed on our environments and helping ensure we continue to meet our customer's needs. Red9 has taken ownership of our servers ensuring they remain healthy by monitoring and tuning inefficient queries.
5 stars
Andrew F.
Datawarehousing Manager
See more testimonials