The MS SQL server provides sys.dm_io_virtual_file_stats DMV. It will give the accumulate IO stats of the database files. These data counters accumulate the data since the SQL service start up.
Some time we need the delta data to see how is the IO delay when we run some sql statement.
Here is the script to show the delta of DB files IO stats.
The script will run 10 minutes, It will capture the IO stats sample very 10 seconds. The compare the recent two samples and then get the delta data.
You can change the run duration and interval. Or create the stored procedure for it.
SQL SCRIPT BEGIN HERE.
DECLARE @DBName nvarchar(30) = 'Tempdb', --Change the DBName you want to capture
@Interval_sec int = 10, --Change the interval if you want (seconds)
@Duration_min int = 10, --Change the run duration if you want (minutes)
@DelayLength char(8),
@EndTime Datetime
SET @DelayLength = CAST(CAST(DATEADD(ss,@Interval_sec,'00:00:00') AS Time) AS char(8))
SET @EndTime = DATEADD(MINUTE,@Duration_min, GETDATE())
IF OBJECT_ID('#dmv_file_IO_samples') IS NOT NULL
DROP TABLE #dmv_file_IO_samples
IF OBJECT_ID('#DMV_File_IO_History') IS NOT NULL
DROP TABLE #DMV_File_IO_History
--Captuer the first sample.
SELECT *, 1 AS sample, GETDATE() AS sample_time
INTO #dmv_file_IO_samples
FROM sys.dm_io_virtual_file_stats(DB_ID(@DBName),null)
WAITFOR DELAY @DelayLength --Waitfor 10 seconds
--Capture sample2
INSERT INTO #dmv_file_IO_samples
SELECT *, 2, GETDATE()
FROM sys.dm_io_virtual_file_stats(DB_ID(@DBName),null)
--Find the difference between sample 1 and 2 , and insert to #DMV_File_IO_History table
SELECT W2.database_id,
W2.file_id,
w2.sample_ms -w1.sample_ms AS Sample_ms_Delta,
w2.num_of_reads - w1.num_of_reads AS Num_of_Reads_Delta,
w2.num_of_bytes_read - w1.num_of_bytes_read AS Num_of_Bytes_Read_Delta,
w2.io_stall_read_ms - w1.io_stall_read_ms AS IO_Stall_Read_ms_Delta,
w2.num_of_writes -W1.num_of_writes AS Num_of_Writes_Delta,
w2.num_of_bytes_written -w1.num_of_bytes_written AS Num_of_bytes_Written_Delta,
w2.io_stall_write_ms - w1.io_stall_write_ms AS IO_Stall_Write_ms_Delta,
w2.io_stall - w1.io_stall AS IO_Stall_Delta,
DATEDIFF(ms, w1.sample_time, w2.sample_time) AS Interval_ms,
GETDATE() AS SampleTime
INTO #DMV_File_IO_History
FROM #dmv_file_IO_samples AS W1
INNER JOIN #dmv_file_IO_samples AS W2
ON W1.database_id = W2.database_id AND W1.file_id = W2.file_id
WHERE W1.sample = 1 AND W2.sample =2
ORDER BY 1,2
--Do the loop untill to @EndTime
WHILE (GETDATE() <= @EndTime)
BEGIN
DELETE #dmv_file_IO_samples WHERE sample = 1
UPDATE #dmv_file_IO_samples SET sample = 1 WHERE sample = 2
WAITFOR DELAY @DelayLength --Waitfor xx seconds
--Capture sample again.
INSERT INTO #dmv_file_IO_samples
SELECT *, 2, GETDATE()
FROM sys.dm_io_virtual_file_stats(DB_ID(@DBName),null)
--Find the difference between sample 1 and 2
INSERT INTO #DMV_File_IO_History
SELECT W2.database_id,
W2.file_id,
w2.sample_ms -w1.sample_ms AS Sample_ms_Delta,
w2.num_of_reads - w1.num_of_reads AS Num_of_Reads_Delta,
w2.num_of_bytes_read - w1.num_of_bytes_read AS Num_of_Bytes_Read_Delta,
w2.io_stall_read_ms - w1.io_stall_read_ms AS IO_Stall_Read_ms_Delta,
w2.num_of_writes -W1.num_of_writes AS Num_of_Writes_Delta,
w2.num_of_bytes_written -w1.num_of_bytes_written AS Num_of_bytes_Written_Delta,
w2.io_stall_write_ms - w1.io_stall_write_ms AS IO_Stall_Write_ms_Delta,
w2.io_stall - w1.io_stall AS IO_Stall_Delta,
DATEDIFF(ms, w1.sample_time, w2.sample_time) AS Interval_ms,
GETDATE() AS SampleTime
FROM #dmv_file_IO_samples AS W1
INNER JOIN #dmv_file_IO_samples AS W2
ON W1.database_id = W2.database_id AND W1.file_id = W2.file_id
WHERE W1.sample = 1 AND W2.sample =2
ORDER BY 1,2
END
SELECT * FROM #DMV_File_IO_History
ORDER BY database_id, file_id, SampleTime
DROP TABLE #dmv_file_IO_samples
DROP TABLE #DMV_File_IO_History
