MS SQL Server - SQL Server 2005 Query Optimizer

Author

SQL Server 2005 Query Optimizer

Lijun Zhang

2006-10-24, 6:32 pm

We are planning to upgrade our SQL Server from 2000 to 2005. We have just
started testing the performance on 2005 and found that 2005 query optimizer
choose wrong query plan over several queries so far. We can force the query
to use the right plan. But what worried me is the query is so simple and the
query optimizer should be able to choose the right path. Does Microsoft has
a good develop team to write 2005?

Our current version on 2005 is 9.00.2153. Does anybody know if there are any
hotfixes after sp1, or will Microsoft release sp2 soon? Thanks.

Lijun

Arnie Rowland

2006-10-24, 6:32 pm

Somehow, I kinda, sorta, mostly, suspect, that Microsoft has "a good develop
team to write 2005". Of course, based upon your own research,
un-collaborated, it is probably NOT a good idea to cast aspersions upon
other people's work. Maybe something didn't go 'right' for you.

Asking questions is good, seeking more information is good, assuming the
'bad' "'develop team" is not so good.

The query optimizer has been re-written.
The SQL 2005 Query Processor may choose a different query plan than SQL 2000
may have chosen.

1. Have you rebuilt the indexes after moving the database to SQL 2005?
2. Have you rebuilt the statistics after moving the database to SQL 2005?
3. Have you changed the database compatibility level to 9 after moving the
database to SQL 2005?

(These steps are necessary, and incorrect execution plans may be determined
based on 'old' indexes and statistics.)

4. How do you know that it is the wrong plan?

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

"Lijun Zhang" <nospam@nospam.com> wrote in message
news:e1FZj5z4GHA.3376@TK2MSFTNGP05.phx.gbl...
> We are planning to upgrade our SQL Server from 2000 to 2005. We have just
> started testing the performance on 2005 and found that 2005 query
> optimizer
> choose wrong query plan over several queries so far. We can force the
> query
> to use the right plan. But what worried me is the query is so simple and
> the
> query optimizer should be able to choose the right path. Does Microsoft
> has
> a good develop team to write 2005?
>
> Our current version on 2005 is 9.00.2153. Does anybody know if there are
> any
> hotfixes after sp1, or will Microsoft release sp2 soon? Thanks.
>
> Lijun
>
>

Lijun Zhang

2006-10-24, 6:33 pm

I assume that a "good develop team" will produce a production that has
better conformance than the old version. Now we have to test every single
applications on our system and compare the performance and fix the problems.
We have more than 500 stored procedures, functions, triggers, not mention
countless add hoc queries.

1. Have you rebuilt the indexes after moving the database to SQL 2005? Yes.
Every indexes of the user tables.

2. Have you rebuilt the statistics after moving the database to SQL 2005? No
need. Since all the indexes are rebuilt.

3. Have you changed the database compatibility level to 9 after moving the
database to SQL 2005? Yes. We changed the compatibility to 9. After we find
the performance is worse than 2000, we re-restore the database and did not
change the compatibility level. The performance is worse than level 9. So
now all our performance testing is under compatibility 9.

4. How do you know that it is the wrong plan? For example, on one query,
the where clause is like "WHERE column1 = 'XX' AND column2 < 'YY' AND
column4 = 'ZZ' ". The table have a index with column (column1, column2,
column3, column4). 2005 choose the index with columns (column2, column5,
column6). Use the right index, the query only search through 30 to 500
something records depend on the value of 'XX', 'YY', 'ZZ'. The wrong path
will search more than 300, 000 records, which is more than half of the
table, almost a table scan. Another query not only choose the wrong index,
but also choose the wrong join order. These are the only two queries I am
working on so far. The problem is almost every application the QA have
tested so far is slower than 2000. BTW, we are using the same box to compare
2000 and 2005.

Lijun

"Arnie Rowland" <arnie@1568.com> wrote in message
news:e5D6XR04GHA.900@TK2MSFTNGP04.phx.gbl...
> Somehow, I kinda, sorta, mostly, suspect, that Microsoft has "a good
develop
> team to write 2005". Of course, based upon your own research,
> un-collaborated, it is probably NOT a good idea to cast aspersions upon
> other people's work. Maybe something didn't go 'right' for you.
>
> Asking questions is good, seeking more information is good, assuming the
> 'bad' "'develop team" is not so good.
>
> The query optimizer has been re-written.
> The SQL 2005 Query Processor may choose a different query plan than SQL
2000
> may have chosen.
>
> 1. Have you rebuilt the indexes after moving the database to SQL 2005?
> 2. Have you rebuilt the statistics after moving the database to SQL 2005?
> 3. Have you changed the database compatibility level to 9 after moving the
> database to SQL 2005?
>
> (These steps are necessary, and incorrect execution plans may be
determined
> based on 'old' indexes and statistics.)
>
> 4. How do you know that it is the wrong plan?
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "Lijun Zhang" <nospam@nospam.com> wrote in message
> news:e1FZj5z4GHA.3376@TK2MSFTNGP05.phx.gbl...
just[color=darkred]
>
>

Sponsored Links