Re: Poor performance after upgrading to sql server 2005

The databases are exactly the same, so the number of rows generated by the
two queries are also exactly the same. As far as the HEAP, the indexes that
were fragmented which I tried to rebuild are all clustered indexes, not
HEAPs, as reported in the dm_db_index_physical_stats table. Since this is an
exact copy of a sql 2000 database with the same indexes etc., after
optimizing the indexes and re-generating the statistics, I don't understand
why a query with a sub-query would run so much slower in sql 2005. Unless sql
2005 treats sub-queries fundamentally differently.

RE setting the MAXDOP, this is from Books OnLine:

Parallel index operations are available only in SQL Server 2005 Enterprise
Edition.

What can I do with MAXDOP in sql 2005 standard edition? Looking at perfmon
output I can see that multiple CPUs are being used. I have tried disabling
all but one CPU but without any performance differences in the tests I've run.

Here are the advanced options:

affinity mask -2147483648 2147483647 0 0
allow updates 0 1 0 0
awe enabled 0 1 0 0
c2 audit mode 0 1 0 0
cost threshold for parallelism 0 32767 5 5
Cross DB Ownership Chaining 0 1 0 0
cursor threshold -1 2147483647 -1 -1
default full-text language 0 2147483647 1033 1033
default language 0 9999 0 0
fill factor (%) 0 100 0 0
index create memory (KB) 704 2147483647 0 0
lightweight pooling 0 1 0 0
locks 5000 2147483647 0 0
max degree of parallelism 0 32 0 0
max server memory (MB) 4 2147483647 2023 2023
max text repl size (B) 0 2147483647 65536 65536
max worker threads 32 32767 255 255
media retention 0 365 0 0
min memory per query (KB) 512 2147483647 1024 1024
min server memory (MB) 0 2147483647 2023 2023
nested triggers 0 1 1 1
network packet size (B) 512 65536 4096 4096
open objects 0 2147483647 0 0
priority boost 0 1 0 0
query governor cost limit 0 2147483647 0 0
query wait (s) -1 2147483647 -1 -1
recovery interval (min) 0 32767 0 0
remote access 0 1 1 1
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
scan for startup procs 0 1 0 0
set working set size 0 1 0 0
show advanced options 0 1 1 1
two digit year cutoff 1753 9999 2049 2049
user connections 0 32767 0 0
user options 0 32767 0 0


Thanks again,

Rich Wood

"Andrew J. Kelly" wrote:

If they are not exactly the same as in the types of operations and the order
etc it can make a big difference in the CPU for a given step. Especially if
the number of rows at that step is different. The costs are calculated
differently between the two versions so you can't compare costs. You might
want to SET STATISTICS IO ON before each query and compare those to see what
may be different.

Standard Edition still honors MAXDOP as long as you have more than 1 CPU for
queries and such. It is just things like Reindexing and DBCC's that will
not use parallelism in Std edition. As for rebuilding the index I am
willing to bet you have a HEAP (table without a clustered index). If so
reindexing will not fix fragmentation for the Heap. Thanks for the
sp_configure but to be really useful you need to show the advanced options.

--
Andrew J. Kelly SQL MVP


"Rich Wood" <RichWood@xxxxxxxxxxxxxxxx> wrote in message
news:3BEFA6B5-F057-4BA3-BA9A-B8AEF97263F8@xxxxxxxxxxxxxxxx
The execution plans are not exactly the same -- drilling down into the
execution plans shows that for the same step, the SQL 2005 db has a higher
CPU cost. I suppose the units of measure may not be exactly the same. I
can't
change the MAXDOP since it's SQL 2005 standard. Disk IO is not the issue
since the database fits in memory and looking at perfmon output, it's not
even accessing the disk.

Digging into this a little deeper, I've found that minor differences in
the
t-sql result in huge differences in performance between the two databases.
Most of these seem to be related to sub-queries. I don't mind replacing
all
the sub-queries in all of our database code but it would be a pain and I'd
really like to understand exactly what is happening. By the way, when I
look
at the dm_db_index_physical_stats table I can see some fragmented indexes,
but when I rebuild them with the ALTER INDEX statement and the REBUILD
option
there is no change in the index fragmentation reported in the physical
table.

By the way, I have also run tests where the SQL 2005 db significantly
outperforms the SQL 2000 db, for example, when replacing a sub-query with
a
join or with a cached result in a table variable.

Here is the sp_configure output:

allow updates 0 1 0 0
clr enabled 0 1 0 0
cross db ownership chaining 0 1 0 0
default language 0 9999 0 0
max text repl size (B) 0 2147483647 65536 65536
nested triggers 0 1 1 1
remote access 0 1 1 1
remote admin connections 0 1 0 0
remote login timeout (s) 0 2147483647 20 20
remote proc trans 0 1 0 0
remote query timeout (s) 0 2147483647 600 600
server trigger recursion 0 1 1 1
show advanced options 0 1 0 0
user options 0 32767 0 0

Thanks for your response.

Rich Wood

"Andrew J. Kelly" wrote:

Are you saying the execution plan is identical on both machines? Are you
using the graphical showplan to determine this or some other way? Is
parallelism involved? Have you played with MAXDOP? What is your disk
configuration like and where are the files? Can you show the output of
sp_configure?

--
Andrew J. Kelly SQL MVP


"Rich Wood" <RichWood@xxxxxxxxxxxxxxxx> wrote in message
news:3C36BDDF-4696-4B81-9743-4A5BB63C4F53@xxxxxxxxxxxxxxxx
I have two machines, one running windows server 2000 and sql server 2000
standard and the other running windows server 2003 64-bit and sql
server
2005
64-bit. The sql 2000 machine has two 2.4 GHz processors with 512 K
cache
and
4 GB memory and the sql 2005 machine has two dual core 2.8 GHz
processors
with 2 MB L2 cache and 12 GB memory.

I set up the same database on both machines and ran a series of
production
jobs to compare performance and in every case the sql 2000 db
outperformed
the sql 2005 db. Sometimes the difference was more than 20 times.

We also ran a series of benchmark tests to compare the cpu, memory and
disk
performance independent of sql server and in every case the sql 2005
machine
significantly outperformed the sql 2000 machine.

Comparing actual execution plans between the two servers, it looks like
the
sql 2005 db uses more cpu than the sql 2000 db to perform the same
operations. We have optimized the indexes and updated the statistics on
the
sql 2005 machine. What else could be causing this problem?

Thanks,
Rich Wood






.

Relevant Pages

  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... But SQL does not have a pointer data type or the ... > being told to design a database. ... But why is little Cindy Lou Who employee ...
    (comp.databases.theory)
  • Re: DBMS and lisp, etc.
    ... Naively implemented with SQL, again for 10 ... (1 query for the initial orders, 1 query for each order for its ... soon as you upgrade to the SQL database. ... (eq (order-customer orderA) ...
    (comp.lang.lisp)
  • SQL Examiner 2009 R2 3.1
    ... Its database version control makes it possible to MS SQL ... Server developers to compare and synchronize database structures ... database structure changes between a variety of applications. ...
    (comp.software.shareware.announce)
  • Re: dbdebunk Quote of Week comment
    ... > a lot of really bad SQL programmers. ... a surrogate key should support the primary key. ... But SQL does not have a pointer data type or the ... > being told to design a database. ...
    (comp.databases.theory)
  • Re: dbdebunk Quote of Week comment
    ... But SQL does not have a pointer data type or the ... More and more programmers who have absolutely no database training are ... But why is little Cindy Lou Who employee ...
    (comp.databases.theory)