August 22, 2011 at 4:22 pm
We have a SQL Server 2008 R2 database deployed in 2 different environments.
Server A is a virtual environment with 32GB of memory, 8 processors and an older SAN (I don't know the specs on the SAN, but I can get them if necessary). The SAN is shared amongst many database instances.
Server B is a hardware cluster with 40 processors, 128 GB of memory and a brand new SAN which is currently only being used by this instance.
Same user database, same application. Users were stating that processes and reports were taking twice as long to run on Server B.
I started troubleshooting by looking at IO performance, comparing it between the 2 environments. Per sys.dm_io_virtual_file_stats:
Server A
Avg Read Latency = 20ms
Avg Bytes/Read = 650000
Server B
Avg Read Latency = 72ms
Avg Bytes/Read = 180000
So clearly, according to SQL Server, Server A has a much higher throughput and much lower latency. This would jive with the reports of poor performance. I decided to run a series of SQLIO tests in both environments, expecting the results to echo what I saw in sys.dm_io_virtual_file_stats. But they don't. In fact, across the board SQLIO reports higher latency, fewer IOs/Sec and fewer MBs/Sec on Server A than on Server B.
Server A
sqlio -kR -t8 -s120 -o16 -frandom -b64 -BH -LS f:\ServerA_data\SQLIO\testfile.dat
IOs/sec: 2598.23
MBs/sec: 162.38
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 48
Max_Latency(ms): 604
Server B
sqlio -kR -t8 -s120 -o16 -frandom -b64 -BH -LS f:\ServerB_data\SQLIO\Testfile.dat
IOs/sec: 7024.75
MBs/sec: 439.04
latency metrics:
Min_Latency(ms): 1
Avg_Latency(ms): 17
Max_Latency(ms): 369
So can someone please explain why SQLIO would indicate better IO performance on Server B while SQL Server indicates better IO performance on Server A?
Colleen
August 22, 2011 at 5:01 pm
The best way to look at storage performance is with perfmon. Take a look at the sec\read, sec\write, reads\sec, writes\sec & queue depth counters to start. Its very possible that one of the technologies on the new SAN is "optimizing" your database by moving blocks of data to slower disks over time. When you're testing with SQLIO the data is newly written to the SAN so it hasn't had a chance to mess with it yet. Just a guess.
August 22, 2011 at 5:22 pm
Thanks Dan. I have perfmon collecting a number of stats right now, as the users plan on running some more tests overnight, so I'll have a better look at the big picture in the morning. It just seemed odd to me that SQLIO and sys.dm_io_virtual_file_stats seem to be saying something completely different. I thought maybe I was missing something obvious.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply