SQL Server Performance Forum – Threads Archive

Query plan worse in 2005

Hey guys – Been testing 2005, and I like it very much. One troublesome thing — there’s a particular query we have that performs much, much worse in 2005 than on the same data in 2000. Superficially, the optimizer in 2005 seems to pick a worse query plan, for this particular case, than the 2000 optimizer. Anyone else bump into this issue? The query is pretty simple; in SS 2000 it would perform about 12,000 reads (according to statistics IO). But in 2005, it does more like 12,000,000 reads (no kidding!) because the order of joins is different. The estimated cost in the query plan in 2005 seems to be off somehow, or something else is throwing the optimizer off, such that the estimated cost is lower, but the actual number of reads is much higher. Any tips? Share my pain? <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

I had the same issue with some large queries.
I recreated all the statistics again then it worked. —————————————-
http://spaces.msn.com/members/dineshasanka

True, did you run UPDATE STATISTICS WITH FULLSCAN & REINDEXED when you had restored the database on SQL 2005? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Did, but it didn’t help in my case — however, what I ultimately found was interesting: – The specific query that had this problem "needed" a new index in 2005 in order for the optimizer to create a good query plan. Without that index I was getting repeated clustered index scans inside a loop join. The index did make sense in the context. – For some reason (fluke?) in SQL Server 2000 the query was performing acceptably without the index. Ironically, the 2000 optimizer picked a better execution plan than 2005 even in the absence of the index. – The new index helped to improve the performance of the query on both platforms. For 2000 it was a modest but visible improvement, but on 2005 it meant the difference between 12,000,000 reads / bad query plan and 600 reads / good query plan, which took it from dog slow to instantaneous. I found this surprising, but there’s a lesson in it for upgrading: the same query on the new platform could need index tweaks because of the revisions to the optimizer, and there is the chance (small?) that a few queries that perform well on 2000 might not perform well on 2005 with a "straight" upgrade.

there are significant differences in the optimizer cost model between 2000 & 2005
i do like the 2005 more than the 2000 because the 2000 model had some strange anomalies. the part i do not like about both are the fixed assumptions on disk io required, random to sequential perf, etc that may cause either to generate a poor plan any ways, give ITW/DTA a try for bad plans,
i’d like to hear about whether people find DTA versus ITW in terms of effectiveness,
i may do my own tool on this if DTA is not effective

Hi Joe In this case DTA and my brain came up with the same solution. I started it working, and thought about the problem at the same time, and we got to the same end point. Could be luck, or coincidence <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Have you referred the same to Microsoft (out of curiosity) and would like to hear what they will say.
Have you compared the steps in the execution plans for 2k & 2k5? Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>