Posted by Ben on 10/25/07 21:49

Regarding the optimizer, is that an official information?

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns99D4EEC7CD33Yazorman@127.0.0.1...
> Ben (pillars4@sbcglobal.net) writes:
>> a) Backuped our SQL Server 2000 database
>> b) Created an empty database in SQL Server 2005
>> c) Restored our SQL Server 2000 database to the empty SQL Server 2005
>> database
>
> Not that it's a big deal, but step b was not needed. RESTORE will
> create the database if it does not exist.
>
>> Below are the things that I have tried so far, which did not work:
>> 1) Rebuilt all the indexes
>> 2) Took out the "Refresh" lines that were not needed in the app code
>> 3) Set the compatibility level from 80 to 90
>> 4) Dropped the "dtproperties" system table, which are not being used
>> anymore in SQL Server 2005
>>
>> Any ideas would be great! Thanks!
>
> You will need to analyse the query plans for these slow queries to see
> what is slow in them. If possible compare the query plans to what you had
> in SQL 2000. Maybe you need to improve your indexing, maybe the queries
> needs tweaking.
>
> You can examine the query plan by running the query from Mgmt Studio
> with Show Execution Plan enabled. You can also use Profiler and catpure
> the Performance:Showplan XML event. This is particularly useful, if
> it's difficult to extract the query from the application.
>
> So why do these queries perform worse with SQL 2005? Well, an optimizer
> is a guessing game. From statistics sampled about the data, the optimizer
> makes an estimation what is the best query plan. Each new version of
> SQL Server comes with new neat tricks in the optimizer, but there always
> queries where these improvements backfire.
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

[Back to original message]