Performance Counters for SQL Server Related to iSCSI Disk Access

Question

I am planning to move my SQL Server databases (plus TLogs and TempDB) to a new LUN on our iSCSI SAN. The current LUNs used by these files are on their own two disk RAID 1 disk group and I'm going to a larger but shared 14 disk RAID 10 disk group. I want to measure the performance of the current configuration and the new configuration as I move each database over and ensure that I am not starting to hit any disk performance issues (or see if I am actually increasing the performance).

There are a bunch of posts on the internet on SQL performance counters such as this one, but I am really interested in just the few that are related to network/disk usage and any latency or limits that are associated with disk reads/writes. What are some of the important SQL Server or Windows performance counters that I should look at to create a current baseline/comparison for iSCSI disk access for SQL Server?

Answer 1

I agree with Mr. Denny.

When analyzing disk performance issues I would look into this counters.

Avg. Disk sec/Read - is the average time, in seconds, of a read of data from the disk.

Avg. Disk sec/Write - is the average time, in seconds, of a write of data to the disk.

Ideally you would be looking less than 15ms latency but in reality I have seen it as high as 30ms in Tier II storage.

Another counter is Avg. Disk Queue Length and you want see that number lower if possible.

Answer 2

You need to be looking at seconds per read and seconds per write. Those numbers will tell you how long the storage is taking to respond.