1

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?

migrated from stackoverflow.com Mar 19 '13 at 2:03

This question came from our site for professional and enthusiast programmers.

  • It sounds like you just need to focus on the disk counters themselves, and not anything SQL specific for starters. But I'm just going on basic thoughts, so ... – jcolebrand Mar 19 '13 at 2:48
2

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.