OPTION (RECOMPILE, QUERYTRACEON 8649)

Question

OPTION (RECOMPILE, QUERYTRACEON 8649)

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Jason-299789 (10/11/2012)
One last question, was this always the case or is there a case to say that performance enhancements with the query engine over the last few revisions have caused this to blur the lines more than say under SQL 6.5/2000?

As far as I recall, it's the case in SQL 2000 as well. Was probably different in 6.5, as SQL 7 is when the optimiser was significantly rewritten

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

aadharjoshi (10/11/2012)
GilaMonster, Its extremely necessary to improve query performance..even if it process execution paralleled.

Then you probably don't want to change to joins, because that will not have the desired effect.

Please post table definitions, index definitions and execution plan, as per http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

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

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

GilaMonster (10/11/2012)
Jason-299789 (10/11/2012)
One last question, was this always the case or is there a case to say that performance enhancements with the query engine over the last few revisions have caused this to blur the lines more than say under SQL 6.5/2000?
As far as I recall, it's the case in SQL 2000 as well. Was probably different in 6.5, as SQL 7 is when the optimiser was significantly rewritten

Thanks Gail, Its nice to get rid of some of these old misconceptions.

_________________________________________________________________________
SSC Guide to Posting and Best Practices