Nov 15 2010

Switching Off Parameter Sniffing

Or, another way to put it, in most cases, shooting yourself in the foot.

I was not aware that the cumulative update for SQL Server 2008 back in June included a switch that allows you to turn parameter sniffing off within SQL Server. Thanks to Kendra Little (blog|twitter) for letting me know about it (although she let me know by “stumping the chump” during my lightening talk at the Summit, thanks Kendra!).

When I first saw the switch, I thought about the places where switching off parameter sniffing could be helpful. But, as I thought about it, the more I realized that this was an extremely dangerous switch. Why? Because, most people only ever hear about parameter sniffing when they run into a problem. Someone says “Parameter sniffing” and you see people cringe. Too many people will take this information in and go, “Hey, I can just switch parameter sniffing off and I’ll have a much faster system, all the time.” But… even when you’re not hitting a problem with parameter sniffing, you’re still getting parameter sniffing. Here is where I see a problem. Let’s discuss what parameter sniffing is.

Parameter sniffing is applicable to stored procedures and parameterized queries. What happens is, when a value is passed to a parameter, the optimizer has the ability to read, or “sniff,” the value of the parameter. It can do this because it knows exactly what the value is when the proc/query is called. This is not applicable to local variables, because the optimizer can’t really know what those values might be, where as it knows exactly what the values of parameters are going in. Why does it do this? One word: statistics. Statistics are what the optimizer uses to determine how queries will be executed. If the optimizer is given a specific value, it can then compare that value to the statistics on the index or table in question and get as good an answer as is possible from those statistics as to how selective this value may be. That information determines how the optimizer will run the query and because it is using specific values, it’s looking at specific information within the stats. If the parameters are not sniffed, the statistics are sampled and a generic value is assumed, which can result in a different execution plan.

The problem with parameter sniffing occurs when you have out of date statistics or data skew (certain values which return a wildly different set of results compared to the rest of the data within the table). The bad statistics or skew can result in an execution plan that is not consistent with most of the data that the stats represent. However, for most of us, this is an edge case.

Most of the time we’re going to benefit from parameter sniffing because the specific values lead to more accurate, not less accurate, execution plans. Sampled data, basically an average of the data in the statistics, can lead to a more stable execution plan, but a less accurate one. Switching parameter sniffing off means that all queries will use sampled data, which can seriously impact performance negatively. Most of the time, most of us are benefitting wildly from the strengths of parameter sniffing and only occasionally are we seeing the problems.

Unless you know, and I mean know, not suspect, that your system has major and systematic issues with parameter sniffing, leave this switch alone and let the optimizer make these choices for you. If you don’t, it’s very likely that you’ll see a performance hit on your system.

4 Comments

  • By Christina Leo, November 17, 2010 @ 8:47 am

    Want to make Conor Cunningham’s head explode? Ask him about the 4136 trace flag in his session on troubleshooting. Yes, that was me he was chastising in the front row. He is definitely not a fan of this method of dealing with parameter sniffing.

    Conor’s point was that there are too many other ways to handle the parameter sniffing issue, such as, manually updating statistics on problem tables, using OPTIMIZE FOR UNKNOWN on problem stored procedures, taking advantage of filtered statistics and/or filtered indexes on tables with data distribution disparities. He flat-out said, “Don’t use it.”

    Of course, the real world application makes a difference, too. How many of these tools is the DBA able to control? If one has direct access to the code, then using 4136 may be more of a hacker’s way of dealing with the problem. On the other hand, if one is trying to prevent a third party app from causing the server to fall over every day at 4:00 PM, then using a nuclear bomb instead of a targeted strike may be the only option.

    Definitely keep in mind that this is a server wide setting. You can’t narrow the focus to a single database. As you indicated in the last paragraph, you better KNOW beyond a shadow of a doubt that there is no other way to solve the parameter sniffing problem.

  • By Grant Fritchey, November 17, 2010 @ 10:01 am

    Excellent comments.

    No, I don’t want to make Conor’s head explode. It’s far too useful intact.

    But yes, you’ve made the point even better than I did. It really, really, should not be used, ever. As you say, unless you KNOW this is what you need.

  • By Kendra Little, April 26, 2011 @ 7:18 pm

    I’m a little late responding to this. :)

    And ZOMG I feel like I must have been horribly rude in your lightning talk! I think I remember that it was one of those things where someone asked a question about turning it off and I could vaguely remember hearing about this trace flag being introduced in a CU. But that’s a very strange thing to be added in a CU, so I doubted my memory, and clearly stuck my foot in my mouth.

    This is an odd option to have added into the product. I can’t imagine ever wanting to use it, personally.

    But you do have a really great description of parameter sniffing and its relationship to statistics and data skew in the post!

  • By Grant Fritchey, April 27, 2011 @ 7:19 am

    I was joking. I loved it. I learned something new. I’m way to old to be in the lease uncomfortable saying “I don’t know.” I learned something new. It was great. Please, show up at more sessions and ask questions like that.

Other Links to this Post

RSS feed for comments on this post. TrackBack URI

Leave a comment