Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
Any reason this is dynamic SQL? Often a simpler approach is just to use OPTION (RECOMPILE); on the query (this makes you pay for compilation costs every time, but gets you a plan based on the current parameters' distribution). This can work in the dynamic SQL you have or if you're not using dynamic SQL. I haven't seen this approach and without understanding exactly how it works I'm very hesitant to clap and say "Yes, do this, it's awesome!" Have a read of this post. –  Aaron Bertrand Aug 16 at 13:07
    
This is really the back end of an API call that allows for dynamic filtering of the recordset by a dynamic where clause. I modified the question to mention OPTION (RECOMPILE). –  Shaun Bowe Aug 16 at 13:10
    
"the benefits of the compiled query with this approach" - at the cost of storing a plan for every single value of CustomerId? I think your priorities may be a little skewed. If the plans are large, this is quite wasteful. If the plans are small, I highly doubt the recompile cost is something you're ever going to notice in any type of performance profiling. –  Aaron Bertrand Aug 16 at 13:15
2  
ok, that's not an assumption I would just make. If you want to be sure about that, you should test it. Also, read the post I linked, because there are other alternatives, too. My preference just happens to be OPTION (RECOMPILE) because no system I know of is CPU-bound and I'd rather spend CPU than memory to get decent performance across the board. –  Aaron Bertrand Aug 16 at 13:21
1  
@AaronBertrand: re "because no system I know of is CPU-bound and I'd rather spend CPU than memory to get decent performance". That point is so important, and so often misunderstood and ignored by SQL developers, as to merit being an answer to this question all on it's own. –  Pieter Geerkens Aug 16 at 13:59

1 Answer 1

up vote 6 down vote accepted

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).

share|improve this answer
    
It's funny I read quite a few articles on your site before posting this question. The query I displayed above is a simplified version of my actual query that contains 9 tables. I didn't want to post it because I figured it would muddy the waters. For now I have gone with the OPTION (RECOMPILE) approach. My only worry (maybe unfounded) is I am running this query very frequently and using OPTION (RECOMPILE) may add additional overhead that I could solve by caching the additional query plans. I hadn't heard of Forced Parameterization. That makes a case for OPTION (RECOMPILE) as well. –  Shaun Bowe Aug 16 at 14:15
1  
@ShaunBowe Hahaha, awesome. Jes Schultz Borland has a really good one there about parameter sniffing called The Elephant and the Mouse. "Very frequently" is a little tricky - my general rule is if it's happening more than 10-100 times a second, then you want a good long term fix. –  Brent Ozar Aug 16 at 17:59
    
This is running maybe once or twice a second at peak so I guess that doesn't count as very frequently. I'll give it a shot and see if it improves performance. Thanks again. –  Shaun Bowe Aug 16 at 18:33
    
What edition are you working with? eg Standard, Enterprise. I have an idea this is about more than just parameter sniffing but my answer depends on your edition. –  wBob Aug 18 at 14:25
    
We are currently using Standard Edition. –  Shaun Bowe Aug 18 at 21:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.