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.

“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.

Reference: Pinal Dave (https://blog.sqlauthority.com)