Sharing SQL Server related scripts is something that can be very helpful in various scenarios. One such scenario is when you need to find the IO utilization per database file. This information can be useful in situations where you have consolidated multiple databases or have heavily partitioned your database.

Let’s take a look at a simple script that can help you retrieve the IO utilization per database file:

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'
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

This script retrieves information such as the database ID, database name, logical file name, file ID, file type, logical disk, size on disk in MB, number of reads, number of writes, number of bytes read, number of bytes written, IO stall in minutes, IO stall for reads in minutes, IO stall for writes in minutes, IO stall read percentage, IO stall write percentage, sample hours, and IO stall percentage of the overall sample.

By running this script, you can get a clear understanding of the IO utilization per database file, which can help you optimize your SQL Server performance.

Have you ever encountered similar requirements? Do you have any scripts that you find useful in the SQL Server world? Feel free to share your thoughts and scripts in the comments section below!