IO DMVs

How is the storage layer doing? This collection covers sys.dm_io_virtual_file_stats, sys.dm_io_pending_io_requests and sys.dm_db_partition_stats. The first query joins virtual file stats to master files for a per-file read/write/stall breakdown across every database; the second shows IO that is still queued right now (a good live stress indicator); the third rolls everything up by drive letter so you can see which physical drives are carrying the load; and the last reports used versus reserved space for every index in the current database.

/*
sys.dm_io_virtual_file_stats
sys.dm_io_pending_io_requests 
sys.dm_db_partition_stats
*/


-------------------------------
-- Get file i/o stats - tip 2350
-------------------------------
SELECT
db_files.database_id,
db_files.FILE_ID,
DB_NAME(db_files.database_id) AS Database_Name,
db_files.physical_name AS File_actual_name,
num_of_reads AS Number_of_reads,
num_of_bytes_read AS Bytes_Read,
io_stall_read_ms AS Read_time_stall_ms,
num_of_writes AS Number_of_writes,
num_of_bytes_written AS Bytes_written,
io_stall_write_ms AS Write_time_stall_ms,
io_stall AS Read_Write_stall_ms,
size_on_disk_bytes / POWER(1024,2) AS size_on_disk_MB
FROM 
sys.dm_io_virtual_file_stats(NULL,NULL) dm_io_vf_stats ,
sys.master_files db_files
WHERE 
db_files.database_id = dm_io_vf_stats.database_id
AND db_files.[file_id] = dm_io_vf_stats.[file_id];

-------------------------------
-- Get pending i/o stats - tip 2329
-------------------------------
select database_id, 
       file_id, 
       io_stall,
       io_pending_ms_ticks,
       scheduler_address 
from sys.dm_io_virtual_file_stats(NULL, NULL) iovfs,
     sys.dm_io_pending_io_requests as iopior
where iovfs.file_handle = iopior.io_handle


-------------------------------
-- Get i/o stats by drive
-------------------------------
SELECT TOP 100 PERCENT WITH TIES
       LEFT(saf.[Filename], 1) + ':' as DriveLetter
     , Count(saf.FileID) as NumFiles
     , CAST (Sum(saf.[size] / 128.0) as numeric(18,3)) as SizeMB
              -- convert to Megabytes from 8-k pages
     , Sum(num_of_reads) as NumberReads
     , Sum(num_of_writes) as NumberWrites
     , Sum(num_of_bytes_read) as BytesRead
     , Sum(num_of_bytes_written) as BytesWritten
     , Sum(io_stall) as IoStallMS
     , Avg(vfs.sample_ms/1000) as SecondsInMeasurement 
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs 
         inner join master..sysaltfiles saf
             on vfs.database_id = saf.DbId 
               and vfs.file_id = saf.FileID
    GROUP BY LEFT(saf.[Filename], 1) + ':'
    ORDER BY DriveLetter asc


-------------------------------
-- Get space used by index
-------------------------------
select  b.name, c.name, a.partition_number, a.used_page_count * 8192 / 1024 / 1024 as spaceUsedMB, a.reserved_page_count * 8192 / 1024 / 1024 as spaceReservedMB, a.row_count
from sys.dm_db_partition_stats a 
inner join sys.objects b on a.object_id = b.object_id
inner join sys.indexes c on a.object_id = c.object_id and a.index_id = c.index_id
where b.type_desc = 'USER_TABLE'
order by 5 desc