SQL Server – Workaround for Parameter Sniffing

parametersql server

Our system has roughly 500 "Customers" that have extreme differences in record counts. Here is a (greatly simplfied) sample query that could return 0 – 100000 rows depending on the parameter passed. This query runs find but I am fairly certain it suffers from parameter sniffing depending on which parameter gets cached.

exec sp_executesql N'
               SELECT *
                 FROM Widgets
                WHERE CustomerId=@0
',N'@0 nvarchar(40)',@0=N'bda43162-2d98-4e79-8e81-7056f6df5e51'

If I modify the query to include the parameter as a selection it seems to cache the query for each individual customer.

exec sp_executesql N'
               SELECT ''bda43162-2d98-4e79-8e81-7056f6df5e51'', *
                 FROM Widgets
                WHERE CustomerId=@0
',N'@0 nvarchar(40)',@0=N'bda43162-2d98-4e79-8e81-7056f6df5e51'

The performance is greatly improved because each customer gets it own version of the query cached. Are there any side effects of this approach?

Assumptions:

  • It must be dynamic SQL for this portion of the system
  • This query is run frequently
  • The number of customers will not grow quickly

EDIT: I considered using OPTION (RECOMPILE) but I did not want to recompile every time if I could get the benefits of the compiled query with this approach.

Best Answer

Parameter sniffing means that one set of parameters produces a dramatically different execution plan than the other, and that if the wrong plan is cached, you get adverse performance effects.

This answer is based on your simplified query - to get accurate advice for your query, you'll need to post the query and the two different plans that resulted from parameter sniffing. (I'd always rather get to the accurate root cause rather than troubleshooting a simplified example, but I've gotta work with the code you posted, so here it goes.)

Your query only has one table in it (assuming that Widgets isn't a view):

SELECT * FROM Widgets WHERE CustomerId=@0

That means if you have a nonclustered index on CustomerID, some CustomerID values might produce a plan with a nonclustered index seek followed by a key lookup, whereas other parameters will do a clustered index scan across the Widgets table.

There are a few ways you could fix this scenario, and I'm going to list them in a generally safest-to-most-risky way:

Use OPTION (RECOMPILE) on the query. This does require a code change to add the line to the query, but then every execution of this query should get the most appropriate plan. The risk is higher CPU use for plan execution (although that generally won't matter in a single-table, single-predicate query like this where the plan will be ultra simple to generate).

Cache every variety of the plan. You noted passing the query in as a string will get each parameter to cache its own individual plan. While that will work today, it does bloat the plan cache (taking up more of SQL Server's memory). The risk here is that someone will turn on Forced Parameterization, a database-level option that will parameterize all of your queries whether they're sent in as strings or not, and suddenly you're back to troubleshooting this issue again.

The rest of these are valid solutions, but not for your single-table, single-predicate query. I'm only listing them here for posterity and clarity:

Use the OPTIMIZE FOR UNKNOWN query hint, or as we like to call it, optimize for mediocre. Requires a query change, and gives you a generally-good-enough plan. This will avoid random changes of the query plan due to parameter sniffing, but the risk is that it still won't be the most performant plan.

Use the OPTIMIZE FOR query hint with a specific CustomerID. This also requires a code change, and you would optimize the query for one of your larger customers. This will get a query plan that's great for big customers, and not-so-good for small customers. Small customer performance will go down, but the big customers won't cripple the system. The risk is that your customer distribution will change, and this will no longer be the right plan for the app as a whole.

Use a query plan guide. You can get exactly the query plan you want, and then pin the plan guide to memory. Here's the Books Online section on plan guides. I'm not usually a big fan of this because if your indexes change, the query plan won't take advantage of the new indexes. If your query changes, the plan guide will no longer be in effect. Suddenly the system might perform terribly, and people will have forgotten that a plan guide was helping before.

Use a stored procedure with manual logic. Have branches that call different stored procedures, one for large customers and one for small customers. This is only used for much larger, more complex queries that can have variations between minutes and hours (or not completing at all).

Related Question