Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

2008 SP2 Recompile Expand / Collapse
Author
Message
Posted Friday, March 22, 2013 9:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 9, 2013 8:19 AM
Points: 179, Visits: 751
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?

Post #1434345
Posted Friday, March 22, 2013 10:05 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:36 AM
Points: 31,362, Visits: 15,823
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1434357
Posted Saturday, March 23, 2013 8:51 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 9, 2013 8:19 AM
Points: 179, Visits: 751
Thanks Steve, but what I'm trying to find out is what problems occur with it in SQL 2008 SP1.
Post #1434620
Posted Saturday, March 23, 2013 9:32 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:36 AM
Points: 31,362, Visits: 15,823
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1434621
Posted Saturday, March 23, 2013 11:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 9:13 AM
Points: 40,609, Visits: 37,070
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1434624
Posted Monday, March 25, 2013 4:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 9, 2013 8:19 AM
Points: 179, Visits: 751
Thanks Gail.
Post #1434801
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse