Re: Poor performance after upgrading to sql server 2005

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: Performance problems -- need guidance on scaling
    ... So we're now CPU ... I have read that putting the catalog and SQL table on separate RAID ... Our current server will support only a single CPU. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Very Small CPU use by SQL Server 2000 on Win2003
    ... Run profiler on the server an see at what rate the queries are coming ... run Windows Performance monitor and check if you are not maxing out ... Low CPU = Disks can't keep up ... Mike Epprecht, Microsoft SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: slow DNS caused extreme SQLS memory pressure+CPU?
    ... RAM in my 64-bit SQL Server box. ... The DNS problem occurring at almost the same time is simply ... The CPU on the SQLS ...
    (comp.databases.ms-sqlserver)
  • deadlock and high cpu - chicken or the egg
    ... I was asked to look into a performance problem on a newly migrated DB ... The db server was moved from a local-physical-nt4-sybase to remote (10 ... The client side application had to be modified to work with MS SQL. ... Can they be generated by the high CPU ...
    (comp.databases.ms-sqlserver)
  • Re: Severe performance issues with replication.
    ... You've checked CPU and disk, but how about memory (SQL Server's favorite ... Perfomon counters: SQL: BUffer Manager ... Yes it is on the same server as the publisher. ... stopped all replication on the live server and the cpu usage went down ...
    (microsoft.public.sqlserver.replication)