February 7, 2012 at 11:10 am
GilaMonster (2/7/2012)
mtassin (2/7/2012)
Really?For us the new variant of Parameter sniffing kicked us in the teeth when we jumped from SQL 2000 to 2008 R2.
What 'new variant of parameter sniffing'? The way SQL sniffs parameters didn't change much between 2000 and 2008.
We had effectively two choices... creating variables for each parameter and assigning the variables for hundreds of Stored procs, or TF 4136.
You have hundreds of procedures where the number of rows returned varies massively depending on the values of parameters passed (not whether they are passed or not, their values when they are passed)? So if you pass one value you get 5 or 10 rows, pass another you get 10000 kind of extremes? (and if you're using the dynamic SQL version of catch-all queries that would only happen if exactly the same set of parameters were not null on the different executions)
You've tested (without that traceflag) and seen that different parameters produce completely different optimal execution plans (testing done WITH RECOMPILE)?
It's very common to get a major performance drop after upgrading from SQL 2000, because the newer versions can't use the 2000 index/column statistics optimally, hence a complete update statistics of everything is required.
That last sentance gave me a sinking sensation... so I thought I'd give it a test. I know that we routinely rebuild statistics on our production servers, and roll backups back to our dev systems, so I figured I had nothing to lose.
Reached out to the dev server and threw DBCC TRACEOFF(4136)/DBCC FREEPROCCACHE() at it. No performance difference between the various stored procs... weird... no improvement, but no decrement either.
Of course none of this ever made much sense to me. When we were testing software on the Dev/Staging environment this issue didn't crop up. If it was the Statistics, then the Dev/Staging environment should have had the same poor performance issues as we roll backups to the dev/staging environments from production and we don't run any maintenance plans on them (i.e. to recompute statistics).
We decided we were ready for the upgrade and went for it. After the roll-out everything went south. (We're talking a query that on 2000 took sub 1s, having a duration of 30-45s).
We did quick research at that point trying to point to what it was, one work-around we came up with, was to effectively declare variables in the stored procs and assign them the parameter values. This improved performance, but we couldn't possibly have adjust the sp's everywhere to cover it, and still hit the roll-out for the next morning. That seemed to confirm our problem was with sniffing and some subtle change from 2000 to 2008 R2.
That's when I ran into 4136, which was added to 2008 to disable parameter sniffing's effects. So I used it on the production server to see if that would resolve the problem. Sure enough it did. Our sub 1s to 45s query returned to sub 1s.
I don't see different query plans with 4136 on or off, I see vastly different performance with respect to response times. So much so that I would be terrified to turn it off at this point.
February 7, 2012 at 11:15 am
mtassin (2/7/2012)
I don't see different query plans with 4136 on or off, I see vastly different performance with respect to response times. So much so that I would be terrified to turn it off at this point.
But, if the execution plans are exactly the same, then parameter sniffing isn't the issue.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
February 7, 2012 at 11:48 am
Grant Fritchey (2/7/2012)
mtassin (2/7/2012)
I don't see different query plans with 4136 on or off, I see vastly different performance with respect to response times. So much so that I would be terrified to turn it off at this point.
But, if the execution plans are exactly the same, then parameter sniffing isn't the issue.
I would agree, but there's a significant performance difference with the traceflag on vs off. Or at least 18 months ago there was. Again, at this point, I'm even more leery on turning it off in production.
February 8, 2012 at 2:11 am
Jason-299789 (2/7/2012)
A quick Question in SQL 2008 doesnt the OPTION(RECOMPILE) on a query force that query to recompile its plan based on the values thus eliminating the need for Parameter Sniffing?Is the overhead of re-evaluation the Query plan greater than the over head of Paramater Sniffing?
It does depend, but typically not so much as is often thought to be the case. The Parameter Embedding Optimization produces more benefits than simply doing away with parameter-sniffing issues. Using RECOMPILE on suitable versions of SQL Server allows the optimizer to produce a plan that is optimal for the current execution (with constants in place of variables) without having to worry about ensuring the cached plan is valid for any future execution with different values.
Side-effects of having constants instead of variables include a greater number of available optimizations, and the possibility for contradiction detection to remove whole areas of an otherwise fairly complex query. You would have to be executing a particular statement very many times per second before the usually small cost of compilation would catch up with the time saved. There are exceptions to this, so I don't offer it as an unqualified statement ""RECOMPILE is always better" but it is an option I look at early/first. The circumstances would have to be unusual to justify other approaches.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply