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?
Best Answer
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.