March 22, 2013 at 9:51 am
Just read this from Gail Shaw ....
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/
'No, you’ll still get the non-optimal plan. In 2005 the optimiser is required to create plans that are safe for reuse, even if they won’t be reused (because of recompile). Only in 2008 SP2 onwards does the recompile work properly – plans optimal for that particular parameter set.
Gail said this on March 24th, 2011 at 23:08'
How does it not work properly in SQL 2008 SP1?
March 22, 2013 at 10:05 am
It wasn't fixed until SP2. That's how. Fixed won't be back ported to another fix. That's the reason the next fix comes out.
March 23, 2013 at 8:51 am
Thanks Steve, but what I'm trying to find out is what problems occur with it in SQL 2008 SP1.
March 23, 2013 at 9:32 am
If you mean with the catch all query? I think it's as Gail mentioned, you don't necessarily get a good plan and can end up scanning the table or getting an unstable plan. WITH RECOMPILE doesn't help and the option doesn't work if you are SP1.
However you can go dynamic SQL.
March 23, 2013 at 11:35 am
Bobby Glover (3/23/2013)
Thanks Steve, but what I'm trying to find out is what problems occur with it in SQL 2008 SP1.
In 2008 SP1 the behaviour is exactly the same as in 2005, ie you still get the poor plan.
Basically, what happened is this:
SQL 2008 RTM: Attempt at creating optimal plan not safe for reuse. Resulted in an incorrect results bug. ie, not something you want to do.
SQL 2008 SP1: Feature to create optimal plan not safe for reuse removed because of said incorrect results bug
SQL 2008 SP2: Feature re-added correctly.
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
March 25, 2013 at 4:01 am
Thanks Gail.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply