Check I/O information per database file (T-SQL)

Introduction

This script will demonstrate how to list I/O information about all the database files. Such as read load, write load or overall I/O usage.

Scenarios

The script will list all the files I/O information about every database which can be stored in UNC paths or local paths.

Script

You can use this script in this way:

1. Open SQL Server Management Studio (SSMS) and connect SQL Server

2. Copy the code in CheckIOStatus.sql and run the script.

After the script runs, we’ll get the following figure:

Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.

SQL

Edit|Remove

CASE 
        -- Handle UNC paths (e.g. '\\fileserver\readonlydbs\dept_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

CASE  
        -- Handle UNC paths (e.g. '\\fileserver\readonlydbs\dept_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

Prerequisites

SQL Server 2008R2