(DIR) Return Create A Forum - Home --------------------------------------------------------- ExcelSoft Database Professionals (HTM) 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 *****************************************************