Re: Poor performance after upgrading to sql server 2005
- From: Rich Wood <RichWood@xxxxxxxxxxxxxxxx>
- Date: Wed, 1 Mar 2006 13:57:31 -0800
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
- Follow-Ups:
- Re: Poor performance after upgrading to sql server 2005
- From: Andrew J. Kelly
- Re: Poor performance after upgrading to sql server 2005
- References:
- Re: Poor performance after upgrading to sql server 2005
- From: Andrew J. Kelly
- Re: Poor performance after upgrading to sql server 2005
- Prev by Date: Re: How To: Upgrading VS SQL 2005 Express to SQL 2005 Server?
- Next by Date: Re: SQL Server 2000, Raid 1 or Raid 5 for Applications
- Previous by thread: Re: Poor performance after upgrading to sql server 2005
- Next by thread: Re: Poor performance after upgrading to sql server 2005
- Index(es):
Relevant Pages
|
