[Tuning] Disk usage over time, checking deltas

Not sure where this came from, if I wrote it or someone else did. Practically, it looks before and after to tell you which files are getting used. The commented out bit was because I was investigating WRITELOG delays.

--as always, drag-copy to get the full text.

DECLARE @compare TABLE (NAME sysname, type_desc varchar(10), physical_name VARCHAR(200), sample_ms BIGINT, 

num_of_bytes_written BIGINT, num_of_bytes_read BIGINT, size_in_gb INT)

DECLARE @compare2 TABLE (NAME sysname, type_desc varchar(10), physical_name VARCHAR(200), sample_ms BIGINT, 

num_of_bytes_written BIGINT, num_of_bytes_read BIGINT, size_in_gb INT)



INSERT INTO @compare 

SELECT 

  master_files.NAME, 

  master_files.type_desc,

  master_files.physical_name, 

  vfs.sample_ms,

  vfs.num_of_bytes_written, 

  num_of_bytes_read, 

  size_on_disk_bytes/1024/1024/1024 AS Size_in_GB

FROM sys.dm_io_virtual_file_stats(null, null) vfs

INNER JOIN MASTER.sys.master_files 

 ON vfs.[file_id] = master_files.[file_id] 

 AND vfs.database_id = master_files.database_id

 --WHERE type_desc = 'log'

ORDER BY (num_of_bytes_read + num_of_bytes_written) DESC



WAITFOR DELAY '00:00:15'



INSERT INTO @compare2

SELECT 

  master_files.NAME, 

  master_files.type_desc,

  master_files.physical_name, 

  vfs.sample_ms,

  vfs.num_of_bytes_written, 

  num_of_bytes_read, 

  size_on_disk_bytes/1024/1024/1024 AS Size_in_GB

FROM sys.dm_io_virtual_file_stats(null, null) vfs

INNER JOIN MASTER.sys.master_files 

 ON vfs.[file_id] = master_files.[file_id] 

 AND vfs.database_id = master_files.database_id

-- WHERE type_desc = 'log'

ORDER BY (num_of_bytes_read + num_of_bytes_written) DESC





SELECT old.NAME, old.physical_name, new.sample_ms - old.sample_ms AS time_elapsed,

new.num_of_bytes_written - old.num_of_bytes_written AS delta_num_bytes_written,

new.num_of_bytes_read - old.num_of_bytes_read AS delta_num_bytes_read, old.size_in_gb

FROM @compare old INNER JOIN @compare2 new ON old.physical_name = new.physical_name

ORDER BY (new.num_of_bytes_written - old.num_of_bytes_written) DESC