February 7, 2012 at 2:19 am
I have 2 requests. The first one do an "index seek", and the 2nd do an "Cluster Index Scan" ... Do you know why ?
select *
from dbo.ASPStateTempSessions
where expires < GETU-TCDATE()-385
GO
-----------------------------------------------------------
DECLARE @now datetime
SET @now = GETUTCDATE()-385
select *
from dbo.ASPStateTempSessions
where expires < @now
GO
February 7, 2012 at 2:30 am
hervouet (2/7/2012)
I have 2 requests. The first one do an "index seek", and the 2nd do an "Cluster Index Scan" ... Do you know why ?select *
from dbo.ASPStateTempSessions
where expires < GETU-TCDATE()-385
GO
-----------------------------------------------------------
DECLARE @now datetime
SET @now = GETUTCDATE()-385
select *
from dbo.ASPStateTempSessions
where expires < @now
GO
Try Gail's blog posts on the subject: -
February 7, 2012 at 2:54 am
Well that saves me the effort of finding the links... 😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2012 at 7:22 am
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?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 7, 2012 at 7:24 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?
If you read through Gail's blog posts (links in my first post) you'll see that she covers this.
February 7, 2012 at 7:42 am
Cadavre,
While Gail mentions the use of the RECOMPILE hint she does so in 6 lines at the very bottom of part 2, but doesnt go into the implications which is what I was trying to get at.
Hence the question about whether the overhead of the Query Recompile outweighs the associated overhead of paramater sniffing.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
February 7, 2012 at 7:50 am
It depends. If a recompile which takes few milliseconds and results in query to return resultset in minutes (originally hours), the choice is very much clear.
February 7, 2012 at 8:02 am
Jason-299789 (2/7/2012)
Hence the question about whether the overhead of the Query Recompile outweighs the associated overhead of paramater sniffing.
It depends. That's only answer possible. Depends on how expensive the query is to compile, how often it runs, how often it encounters the parameter sniffing problem. In some cases the overhead of the recompiles will be acceptable and will outweigh the parameter sniffing problem, in others it won't, Every situation here will be different in terms of costs and overheads
I will admit, recompile would not be my first or even second option for dealing with parameter sniffing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2012 at 8:30 am
Thank you for your posts ! I found the explanation !
February 7, 2012 at 8:53 am
GilaMonster (2/7/2012)
I will admit, recompile would not be my first or even second option for dealing with parameter sniffing.
Personally dealing with parameter sniffing I prefer Traceflag 4136. 🙂
February 7, 2012 at 9:03 am
mtassin (2/7/2012)
GilaMonster (2/7/2012)
I will admit, recompile would not be my first or even second option for dealing with parameter sniffing.
Personally dealing with parameter sniffing I prefer Traceflag 4136. 🙂
Personally that would be my very, very, very, very last resort if everything else failed to fix the problem and only considered after huge amounts of testing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2012 at 9:14 am
GilaMonster (2/7/2012)
mtassin (2/7/2012)
GilaMonster (2/7/2012)
I will admit, recompile would not be my first or even second option for dealing with parameter sniffing.
Personally dealing with parameter sniffing I prefer Traceflag 4136. 🙂
Personally that would be my very, very, very, very last resort if everything else failed to fix the problem and only considered after huge amounts of testing.
Really? For us the new variant of Parameter sniffing kicked us in the teeth when we jumped from SQL 2000 to 2008 R2.
Especially since we had gone down the path of
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ for all of the crazy search stored procs we have (it seems every screen in our apps have a search feature with between 6 and 12 optional parameters).
and the parameter sniffing was murdering these queries. We had effectively two choices... creating variables for each parameter and assigning the variables for hundreds of Stored procs, or TF 4136.
Not a single query improved in performance from parameter sniffing.
We could have also added things like OPTIMIZE FOR UNKNOWN. The search type stored procs were having the most trouble, but many others were murdered performance wise in the transition.
Of course we're unique... One part-time DBA/DB Dev (Me) and 3 developers... and we had a scheduled release day for the migration that we weren't allowed to slip when we ran into this.
February 7, 2012 at 9:26 am
mtassin (2/7/2012)
Really?
Yes. Absolutely. Parameter sniffing is a good thing in the vast majority of situations, it lets the optimiser get a better idea of the cardinalities of various query operators and generate a better plan. That traceflag disables parameter sniffing server-wide. It's hobbling the optimiser (intentionally). About the only time I would consider that would be as a temporary 'fix' while investigating and resolving root causes.
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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2012 at 9:32 am
GilaMonster (2/7/2012)
I will admit, recompile would not be my first or even second option for dealing with parameter sniffing.
I'm pretty much in the opposite camp now, but that's mainly because the vast majority of catch-all queries I deal with are parameters in big reports that take in excess of 5 seconds and are executed infrequently. That, and thankfully all the environments I actively develop for are on 2008 SP2. I'm happy to have a bit more compile time for the ease of development/maintenance.
It's one of those things where it's best to have a few approaches in your toolkit and know what the factors are that guide which one is likely to suit the situation best.
I wouldn't touch TF 4136 with a barge pole - it's effectively forcing all queries (catch-all's or not) to be optimised for unknown parameters.
Edit: Sorry, have missed the point a bit - thought this was about catch-all queries. Still wouldn't touch TF 4136 though 🙂
February 7, 2012 at 10:18 am
mtassin (2/7/2012)
GilaMonster (2/7/2012)
I will admit, recompile would not be my first or even second option for dealing with parameter sniffing.
Personally dealing with parameter sniffing I prefer Traceflag 4136. 🙂
Really?
Wow.
I mean, that's a server wide setting. So even though one database might have severe problems, do ALL databases have the same problems? If not, you just shot performance in the face on all those databases. Seriously, parameter sniffing is largely a fantastic thing that radically improves performance. I mean if you have an isolated environment where there is only the problem database and you're able to determine that the majority of your code base will benefit, yeah, sure it's a possibility. But that's an extremely rare situation. Most everyone else will never see a situation like that.
BTW, recompile isn't my first choice either. For me, it really depends on what the data distribution looks like as well as the queries. If the data distribution is so out of wack that I can't find a plan that satisfies 80-90% of cases, then I might consider recompile... or, if I were still on 2000.
----------------------------------------------------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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply