Capture SQL IO Latency

<#
.SYNOPSIS

  Get-SQLIOLatencies

.DESCRIPTION

  The script to get statistics about the performance of your SQL storage for
  period of time. It will capture metrics like latency, number of reads/writes
  and average size for each database files on on the sql instance. The script
  can save the result in CSV if CSVPath is provided and the  result will be
  shown in Grid View. If CSVPath parameter is not provided the output will be
  listed for each request as type System.Data.DataRow.

  The script will impersonate the Windows user that is running the script in
  order to authenticate against the SQL! You can use SQL Authentication by
  providing parameter UserID and Password!

  The script is based on sql script by Paul S. Randal from sqlskills.com, I
  transformed this script po PowerShell with sole purpose to make it easier to
  run and receive useful output in PowerShell. I have permission from Paul to
  make and publish this powershell version. See Paul's copyright below.

  You can see the original sql script here:
  http://www.sqlskills.com/blogs/paul/capturing-io-latencies-period-time/

  Author: Ivan Yankulov [Senior SharePoint Engineer @ bluesource Information Limited]
  Contact: http://spyankulov.blogspot.com
  About this script: http://spyankulov.blogspot.com/2015/10/capture-sql-io-latencies-for-period-of.html
  How it Works: http://www.sqlskills.com/blogs/paul/capturing-io-latencies-period-time/

  ------------------------------------------------------------------------------
  Original SQL Script written by Paul S. Randal, SQLskills.com

  (c) 2014, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes (e.g. in a for-
  sale commercial tool). Use in your own environment is encouraged. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.

  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
  EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
  WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
  ------------------------------------------------------------------------------

.PARAMETER SQLServer

   SQL Server name

.PARAMETER Port

   SQL Port

.PARAMETER Minutes

   Number of minutes to capture data for

.PARAMETER CSVPath

   Full path where CSV output will be saved

.PARAMETER UserID

   SQL Username if SQL Authentication will be used

.PARAMETER Password

   SQL Password if SQL Authentication will be used

.EXAMPLE

  .\Get-SQLIOLatencies.ps1 -SQLServer DB\SPSQL -CSVPath E:\Latency.csv -Minutes 120

  This will capture data for 2 hours and the output will be CSV/GidView. For
  authentication to the SQL will be used the identity that is running the
  script.

.EXAMPLE

  .\Get-SQLIOLatencies.ps1 -SQLServer DB\SPSQL -Minutes 60

  This will capture data for 1 hour and the output will be in powershell only.
  For authentication to the SQL will be used the identity that is running the
  script.

.EXAMPLE

  .\Get-SQLIOLatencies.ps1 -SQLServer DB\SPSQL -CSVPath E:\Latency.csv -Minutes 120 -UserID duser -Password demo!234

  This will capture data for 2 hours and the output will be CSV/GidView. For
  authentication to the SQL will be used SQL Authentication with Username and
  Password.

 .LINK

  http://spyankulov.blogspot.com
  http://spyankulov.blogspot.com/2015/10/capture-sql-io-latencies-for-period-of.html
  http://www.sqlskills.com/blogs/paul/capturing-io-latencies-period-time/
#>

[CmdletBinding()]
Param(
  [parameter(Mandatory=$True)]
  [string]$SQLServer,
  [parameter(Mandatory=$True)]
  [string]$Minutes,
  [parameter(Mandatory=$False)]
  [string]$Port,
  [parameter(Mandatory=$False)]
  [string]$CSVPath,
  [parameter(Mandatory=$False)]
  [string]$UserID,
  [parameter(Mandatory=$False)]
  [string]$Password
)
BEGIN{
  If($Port){
    $dbServer = $SQLServer + "," + $Port
  }Else{
    $dbServer = $SQLServer
  }
  If($UserID -and $Password){
    $connectionString = "Data Source=$dbServer; User Id=$UserID; Password=$Password; Database=master"
  }Else{
    $connectionString = "Data Source=$dbServer; Integrated Security=SSPI; Database=master"
  }
  $waitSeconds = (New-TimeSpan -Minutes $Minutes).TotalSeconds
}
PROCESS{
  $debugMSG = @"

  *** CONNECTION *****

  Connection String = $connectionString

  Minutes           = $Minutes
"@

  Write-Verbose -Message $debugMSG

  $sqlCommand1 = @"
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##SQLskillsStats1')
    DROP TABLE [##SQLskillsStats1];

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##SQLskillsStats2')
    DROP TABLE [##SQLskillsStats2]
"@
  $sqlCommand2 = @"
SELECT [database_id], [file_id], [num_of_reads], [io_stall_read_ms],
       [num_of_writes], [io_stall_write_ms], [io_stall],
       [num_of_bytes_read], [num_of_bytes_written], [file_handle]
INTO ##SQLskillsStats1
FROM sys.dm_io_virtual_file_stats (NULL, NULL)
"@

  $sqlCommand3 = @"
SELECT [database_id], [file_id], [num_of_reads], [io_stall_read_ms],
       [num_of_writes], [io_stall_write_ms], [io_stall],
       [num_of_bytes_read], [num_of_bytes_written], [file_handle]
INTO ##SQLskillsStats2
FROM sys.dm_io_virtual_file_stats (NULL, NULL);
"@
  $sqlCommand4 = @"
WITH [DiffLatencies] AS
(SELECT
-- Files that weren't in the first snapshot
        [ts2].[database_id],
        [ts2].[file_id],
        [ts2].[num_of_reads],
        [ts2].[io_stall_read_ms],
        [ts2].[num_of_writes],
        [ts2].[io_stall_write_ms],
        [ts2].[io_stall],
        [ts2].[num_of_bytes_read],
        [ts2].[num_of_bytes_written]
    FROM [##SQLskillsStats2] AS [ts2]
    LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]
        ON [ts2].[file_handle] = [ts1].[file_handle]
    WHERE [ts1].[file_handle] IS NULL
UNION
SELECT
-- Diff of latencies in both snapshots
        [ts2].[database_id],
        [ts2].[file_id],
        [ts2].[num_of_reads] - [ts1].[num_of_reads] AS [num_of_reads],
        [ts2].[io_stall_read_ms] - [ts1].[io_stall_read_ms] AS [io_stall_read_ms],
        [ts2].[num_of_writes] - [ts1].[num_of_writes] AS [num_of_writes],
        [ts2].[io_stall_write_ms] - [ts1].[io_stall_write_ms] AS [io_stall_write_ms],
        [ts2].[io_stall] - [ts1].[io_stall] AS [io_stall],
        [ts2].[num_of_bytes_read] - [ts1].[num_of_bytes_read] AS [num_of_bytes_read],
        [ts2].[num_of_bytes_written] - [ts1].[num_of_bytes_written] AS [num_of_bytes_written]
    FROM [##SQLskillsStats2] AS [ts2]
    LEFT OUTER JOIN [##SQLskillsStats1] AS [ts1]
        ON [ts2].[file_handle] = [ts1].[file_handle]
    WHERE [ts1].[file_handle] IS NOT NULL)
SELECT
    DB_NAME ([vfs].[database_id]) AS [DB],
    LEFT ([mf].[physical_name], 2) AS [Drive],
    [mf].[type_desc],
    [num_of_reads] AS [Reads],
    [num_of_writes] AS [Writes],
    [ReadLatency(ms)] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
    [WriteLatency(ms)] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
    /*[Latency] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,*/
    [AvgBPerRead] =
        CASE WHEN [num_of_reads] = 0
            THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
    [AvgBPerWrite] =
        CASE WHEN [num_of_writes] = 0
            THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
    /*[AvgBPerTransfer] =
        CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
            THEN 0 ELSE
                (([num_of_bytes_read] + [num_of_bytes_written]) /
                ([num_of_reads] + [num_of_writes])) END,*/
    [mf].[physical_name]
FROM [DiffLatencies] AS [vfs]
JOIN sys.master_files AS [mf]
    ON [vfs].[database_id] = [mf].[database_id]
    AND [vfs].[file_id] = [mf].[file_id]
-- ORDER BY [ReadLatency(ms)] DESC
ORDER BY [WriteLatency(ms)] DESC;
"@
  $sqlCommand5 = @"
-- Cleanup
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##SQLskillsStats1')
    DROP TABLE [##SQLskillsStats1];

IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
    WHERE [name] = N'##SQLskillsStats2')
    DROP TABLE [##SQLskillsStats2];
"@

  $connection = New-Object System.Data.SqlClient.SqlConnection
  $connection.ConnectionString = $connectionString
  $connection.Open()
  $command = $connection.CreateCommand()

  $command.CommandText = $sqlCommand1
  $command.ExecuteNonQuery() | Out-Null
  $command.CommandText = $sqlCommand2
  $command.ExecuteNonQuery() | Out-Null

  [int]$max = $waitSeconds
  for ($i = $max; $i -gt 1; $i--) {

    Write-Progress -Activity "Gathering Stats" -SecondsRemaining $i

    Start-Sleep 1

  }
  $command.CommandText = $sqlCommand3
  $command.ExecuteNonQuery() | Out-Null

  $command.CommandText = $sqlCommand4
  $adapter = New-Object System.Data.sqlclient.sqlDataAdapter $command
  $dataset = New-Object System.Data.DataSet
  $adapter.Fill($dataSet) | Out-Null
  If($CSVPath){
    $dataSet.Tables[0] | Export-Csv -NoTypeInformation -Path $CSVPath
    $dataSet.Tables[0] | Out-GridView -Title $CSVPath
  }Else{
    Write-Output $dataSet.Tables[0]
  }
  $command.CommandText = $sqlCommand5
  $command.ExecuteNonQuery() | Out-Null
  $connection.Close()
}