DIR Return Create A Forum - Home
       ---------------------------------------------------------
       ExcelSoft Database Professionals
  HTML https://esdbp.createaforum.com
       ---------------------------------------------------------
       *****************************************************
   DIR Return to: Scripts
       *****************************************************
       #Post#: 108--------------------------------------------------
       I/O latency Information Capture 
       By: srinivasma_exceldbp Date: November 24, 2014, 11:31 pm
       ---------------------------------------------------------
       /*
       The script does the following:
       Captures IO latency in the server
       Creates two temporary tables
       Captures the output from sys.dm_io_virtual_file_stats into the
       first table
       Waits for a configurable delay ( 30 minutes in the example)
       Captures the output from sys.dm_io_virtual_file_stats into the
       second table
       */
       E
       IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
       WHERE [name] = N'##Stats1')
       DROP TABLE [##Stats1];
       IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
       WHERE [name] = N'##Stats2')
       DROP TABLE [##Stats2];
       GO
       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 ##Stats1
       FROM sys.dm_io_virtual_file_stats (NULL, NULL);
       GO
       WAITFOR DELAY '00:30:00';
       GO
       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 ##Stats2
       FROM sys.dm_io_virtual_file_stats (NULL, NULL);
       GO
       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 [##Stats2] AS [ts2]
       LEFT OUTER JOIN [##Stats1] 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 [##Stats2] AS [ts2]
       LEFT OUTER JOIN [##Stats1] 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;
       GO
       -- Cleanup
       IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
       WHERE [name] = N'##Stats1')
       DROP TABLE [##Stats1];
       IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
       WHERE [name] = N'##Stats2')
       DROP TABLE [##Stats2];
       GO
       *****************************************************