Disks are notorious for causing server performance issues, and identifying the problems can be challenging. While resource monitor is often the first point of search, automating the collection process using PowerShell can provide a more efficient solution. In this blog post, we will explore a PowerShell script that can help identify disk latencies on a SQL Server box.

First, let’s take a look at the PowerShell script:

param (
    [string]$ServerName = "localhost",
    [int]$SampleFrequencySeconds = 10,
    [int]$CollectionDurationSeconds = 120
)

# Check if the collection duration is greater than or equal to the frequency rate
if ($CollectionDurationSeconds -lt $SampleFrequencySeconds) {
    Write-Error "CollectionDurationSeconds cannot be less than SampleFrequencySeconds"
    exit
}

# Loop through all of the drives, sampling them
$DrivesOutput = for ($i = 0; $i -lt [int]($CollectionDurationSeconds / $SampleFrequencySeconds); $i++) {
    Get-Counter -Counter "\LogicalDisk(*)\avg. disk sec/transfer" |
        Select-Object -ExpandProperty CounterSamples |
        Where-Object {$_.InstanceName -ne "_total"} |
        Select-Object InstanceName,
            @{Name = "Type"; Expression = {"LOGICAL"}},
            CookedValue
    Get-Counter -Counter "\PhysicalDisk(*)\avg. disk sec/transfer" |
        Select-Object -ExpandProperty CounterSamples |
        Where-Object {$_.InstanceName -ne "_total"} |
        Select-Object InstanceName,
            @{Name = "Type"; Expression = {"PHYSICAL"}},
            CookedValue

    # Sleep for the specified frequency before continuing in the loop
    Start-Sleep -Seconds $SampleFrequencySeconds
}

# Group by the drive and calculate the average for each drive
# Round to the nearest [ms]
$DrivesOutput |
    Group-Object InstanceName, Type |
    Select-Object @{Name = "InstanceName"; Expression = {$_.Group.InstanceName[0]}},
        @{Name = "Type"; Expression = {$_.Group.Type[0]}},
        @{Name = "DiskLatencyMs"; Expression = {[int](($_.Group.CookedValue | Measure-Object -Average).Average * 1000)}}
    |
    Sort-Object InstanceName

In this script, we start the collection of counters every 10 seconds for a duration of 2 minutes. We collect two counters: “\LogicalDisk(*)\avg. disk sec/transfer” and “\PhysicalDisk(*)\avg. disk sec/transfer”. The output will vary depending on your environment, but it will display the disk latencies in milliseconds.

If you find this script useful, let me know in the comments. I am also interested in hearing about the ways you have used PowerShell in your environments, especially those that are used in production. PowerShell is a powerful scripting language, and exploring its various features can be beneficial for SQL Server administrators.

Stay tuned for more articles on SQL Server concepts and ideas!