In a VLDB environment, diagnosing performance issues is both an art and science. There are several correct ways to identify the reasons contributing to the performance issue and help define the scope. Sql Server as a database engine is a resource intensive software. Not because of Sql Server, but what Sql Server, as a database engine, is designed to honor i.e. Several thousands of requests coming in from several web/app servers simultaneously.

There several components, moving together, contributing to good or bad performance.

Primarily,

• I/O
• CPU
• Memory
• Network latency
• etc

Luckily, there are several Operating Systems performance counters available that enable us to rule-out some factors and only focus on the ones directly contributing to the problem. Gather these performance metrics to analyze and arrive at a conclusion based on empirical evidence.

Avg. Disk sec/Transfer

This allows us to see if the physical disks are contributing to the slow I/O. A drive (LUN) attached to a machine could have several physical disks assigned to it. Together they help in retrieving data when requested. This counter shows if the disks are performing to the level expected for Sql Server’s optimal performance. If there is a lot of traffic coming into this drive causing higher Avg. Disk Queue Length numbers that is a different problem. But when a request comes to a physical disk, it is expected to respond within a range. If it goes beyond that then its time to look into the physical disks.

The standard I measure is this:

With this ranges, we know how good the disks are, so we could eliminate them as a contributing factor to the performance issues.

If you see 15-30 millisecond response and still experiencing performance issues, then the problem is with something else, not this disks. Not the disks aligned to this LUN (drive) where the database files are located.

But if you see, 40-50 millisecond response time or more than 50 millisecond response time, then you know this is a contributing factor. May not be the sole factor to all the performance issue, but one factor that you know, with evidence, to be part of the problem.

Avg. Disk Queue Length

If you see large Avg. Disk Queue Length, then may be there is a lot of traffic coming into this drive. See if you can add more disks to this LUN (talk to SAN administrator) Or see if you can split this traffic into separate drives by adding (or moving) new database files on a new drive. But if you see this high (> 2 per disk) along with poor performance (40-50 ms) on ‘Avg. Disk sec/Transfer‘, then both are contributing to the problem.

Hope this helps,
_Sqltimes