SQL SERVER – Identify Disk Latency on SQL Server Box Using PowerShell

Couple of week back, I was playing around with using Powershell for some simple activities. The fall out of that was the ability to search the SQL Server Error Logs. Though we can search for anything, I just showed you a case of usage. In this blog post, I will talk about one of the most sought after request for searching what the disk latencies are. Let us see a powershell script for for Identify Disk Latency on SQL Server Box.

Disks are one of the most notorious when it comes to bring the server performance down. It is sometimes quite difficult to identify the problems. A lot of times, the first point of search would be using resource monitor. But in this blog, I thought let us try to automate this collection process using a PowerShell script. This ultimately shows how powerful this scripting language is and how one can use it to collect and process few Performance Counter values.

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

# do a check to ensure that 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 we have
# 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</pre>
<pre>

As part of the script we have gone ahead and started our collection of counters once every 10 seconds. In this script, I have used 2 mins interval for the collection process. We have collected two counters – “\LogicalDisk(*)\avg. disk sec/transfer” and “\PhysicalDisk(*)\avg. disk sec/transfer”.

SQL SERVER - Identify Disk Latency on SQL Server Box Using PowerShell PowerShell-DiskLatency-01

I have gone ahead and shown a typical output from my PC. I have two physical drives and 3 logical drives. And we can see the disk latencies in (milliseconds).

I am sure the output would vary in your environments. Do let me know if such scripts are useful and you would want me to write more about them. I am slowly starting to love this scripting language that I plan to explore the various ways people use this powerful features.

Do let me know via comments some of the ways you have used PowerShell in your environments and which of those are even used in Production?

Reference: Pinal Dave (https://blog.sqlauthority.com)

Disk, Powershell, SQL Server
Previous Post
SQL SERVER – CREATE TABLE Failed Because Column ‘C1024’ in Table ‘myTable’ Exceeds the Maximum of 1024 Columns
Next Post
SQL SERVER – What are T-SQL Window Functions? – Notes from the Field #082

Related Posts

8 Comments. Leave new

  • Pinal, I have tried the code but getting
    Get-Counter : Unable to connect to the specified computer or the computer is offline.
    At line:1 char:2
    + Get-Counter -ComputerName $ServerName -Counter “\LogicalDisk(*)\avg. disk sec/t …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidResult: (:) [Get-Counter], Exception
    + FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand

    Any idea or do I need to to configure something to make it work?
    Thanks in advance

    Reply
    • Pinal, it seems that Get-Counter does not like the presence of when you run script against localhost. I dropped “-ComputerName $ServerName” from line 18 and it worked fine for localhost. I also tried to replace [string]$ServerName = “localhost” by [string]$ServerName = “l”, it did not work neither. I think that there should be some logic to not use the -ComputerName switch if we need to run it against localhost

      Reply
      • Thanks Salam. I think there are couple of things. The Get-Counter diagnostics commandlet uses the -ComputerName as an optional parameter. The default if the local computer. So it might be working for you. The “localhost” name must get resolved as part of NetBIOS name, else we might get an error. The IP address can also resolve if you need. But you bring some interesting point. Next time let us know of any error message if you receive any. Thanks again for dropping a line.

  • Perry whittle
    May 31, 2015 3:10 pm

    What if you’re using mounted volumes?

    Reply
  • wolfgoeGoebel
    May 31, 2015 7:27 pm

    Pinal, I’m going to test this on one of our production environments, that uses Veritas Storage Foundations this week! These are very interesting data that we can get using powershell, indeed. I did some researching on PS over a year ago and found it the perfect solution for one problem I had back then, which was in colleting various data, like capacity for instance, in multiple servers, including SQLServer and Oracle. My problem back then was that I would have to create linked servers to each one of the servers, and, develope custom scripts and use them with OPENQUERY, for Oracle, especially. It is not reaaly a big problem but I decided to give PS a try, and I’m very pleased with the end result. This solution is now on “production”.

    Reply
  • Please send me your mail id

    Reply

Leave a Reply