SQL SERVER – Script: Finding Cumulative IO Per Database File

Sharing SQL Server related scripts is something I tend to do from time to time. At the SQLPass one of the attendees for my session around 42 tips asked, if I have any script that is handy to find the IO utilization per database file because they had couple of scenario’s where such information was useful. I got curious to know what are those scenarios. During the break while on the line I asked, and got the response. Here is my script for cumulative IO per database file.

“Pinal, I actually work for a financial institute and we have large SQL Server installations. For the question I asked, I thought you might have something handy that I can take a look at. There are actually 2 scenario’s that get enabled here:

1. First is a classic place where I have an SQL Server where I have consolidated a number of databases and want to make sure the experience of users accessing different databases (aka different applications) is not compromised because I can go ahead and distribute IO hungry files on different files.
2. The second scenario is wherein I have done heavy partitioning on my database and now I have far too many files. Though the thought process remains the same as 1^st I want the IO stats per file so that I can again look at distributing them around.”

Trust me, this made complete sense and I came back to this blog to check if I had something similar for this requirement. Well, then I thought I must write something simple to get this sorted. Here is a simple script that I made and feel free to modify the same for your requirements:

SELECT f.database_id, DB_NAME(f.database_id) AS database_name, f.name AS logical_file_name, f.[file_id], f.type_desc,
   CAST (CASE
       -- Handle UNC paths (e.g. '\\fileserver\mydbs\sqlauthority_dw.ndf')
       WHEN LEFT (LTRIM (f.physical_name), 2) = '\\'
           THEN LEFT (LTRIM (f.physical_name),CHARINDEX('\',LTRIM(f.physical_name),CHARINDEX('\',LTRIM(f.physical_name), 3) + 1) - 1)
           -- Handle local paths (e.g. 'C:\Program Files\...\master.mdf')
           WHEN CHARINDEX('\', LTRIM(f.physical_name), 3) > 0
           THEN UPPER(LEFT(LTRIM(f.physical_name), CHARINDEX ('\', LTRIM(f.physical_name), 3) - 1))
       ELSE f.physical_name
   END AS NVARCHAR(255)) AS logical_disk,
   fs.size_on_disk_bytes/1024/1024 AS size_on_disk_Mbytes,
   fs.num_of_reads, fs.num_of_writes,
   fs.num_of_bytes_read/1024/1024 AS num_of_Mbytes_read,
   fs.num_of_bytes_written/1024/1024 AS num_of_Mbytes_written,
   fs.io_stall/1000/60 AS io_stall_min,
   fs.io_stall_read_ms/1000/60 AS io_stall_read_min,
   fs.io_stall_write_ms/1000/60 AS io_stall_write_min,
   ((fs.io_stall_read_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_read_pct,
   ((fs.io_stall_write_ms/1000/60)*100)/(CASE WHEN fs.io_stall/1000/60 = 0 THEN 1 ELSE fs.io_stall/1000/60 END) AS io_stall_write_pct,
   ABS((sample_ms/1000)/60/60) AS 'sample_Hours',
   ((fs.io_stall/1000/60)*100)/(ABS((sample_ms/1000)/60))AS 'io_stall_pct_of_overall_sample' -- Number of milliseconds since the machine was started.
FROM sys.dm_io_virtual_file_stats (DEFAULT, DEFAULT) AS fs
INNER JOIN sys.master_files AS f ON fs.database_id = f.database_id AND fs.[file_id] = f.[file_id]ORDER BY 18 DESC
GO

The sample output (partially shown) is as:

Have you had similar requirements and have you used scripts similar to these before? I think necessity is the mother of all scripts in the SQL Server world. Hope to see some of your scripts via the comments section.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.