Hi,
I was wondering if anyone could shed any light on performance problems
we are having with sql 2005. We have 3 boxes which are all 64bit
itanium boxes (4 CPU) connected to a 145 spindle SAN using a 2gb FC
card. All 3 servers also have identical builds of windows 2003 sp1.
The performance on the 2005 boxes has always seemed to be worse than
the 2000 box for some reason. to checkout the san performance i ran
sqlio and when the box is quite ran a test using the normal io size.
The results were identical on all 3 servers, MB/Sec was about
140-160mb and latency was about 7-8 secs.
So the SAN configuration looks idenitical. Now if i run a table scan
from the sql 2000 machine and monitor the perfromance counters, the
latency on the scan is about 30ms and pulls back data at about 100mb/
sec. On the two SQL2005 machines I run the identical query (As the
databases are identical, sizes etc) the latency shoots upto 300-400ms
and MB/Sec drops to 20mb/sec.
Now i am confused, I've proved the SAN is running idenitcal by using
SQLIO, indexes etc have been taken out of the equation as i'm running
a table scan anyway to see the throughput and latency.
I did think that maybe it was because in some strange way the sql2005
databases were set to SQL 2000 compatibility but i disproved this by
creating the identical table in a new comp lev 90 database and setting
the page checksum setting. But the results were still the same latency/
throughput.
I've done this test four or five times so it doesn't look like there
is any other external san traffic or bottlenecks on there that are
having an effect. It also seems strange that its the same on the two
sql2005 machines.
I had started to think that maybe the disk is fragmented at the OS
level but on both sql 2005 it would seem a bit of a long shot that
they would both be fragmented and not the sql 2000 one. Also the Vdisk
is 1.5 TB so fragmenting this at the O/S level will take days.
Is there anything else that i'm missing at the SQL2005 level that i
need to check to see why latency seems to be so much slower on the SQL
2006 environments?
Thanks
Ian.
