Mahmoud ElMansy: knowledge meant to be free

Find Most Active Database in SQL Server 2008

In this post I will talk about “Finding Most Active Database in SQL Server–DMV dm_io_virtual_file_stats”.
The following TSQL uses “sys.dm_io_virtual_file_stats” view -one of the sys Views for SQL2008- that shows the amount of reads and writes for each file in the database .
This query will help us determining the most busy file and this can help in performance considerations as deciding whether to put this file in faster IO Drive or to make
“Data Partitioning” or any other possible alternative,
and some nice Detailed information about the file.

databaseName,File_LogicalName,
File_type_desc,physical_name,
num_of_reads,num_of_bytes_read,
io_stall_read_ms,num_of_writes,
num_of_bytes_written,
io_stall_write_ms,io_stall,size_on_disk_bytes,
size_on_disk_KB,size_on_disk_MB,size_on_disk_GB

SELECT
DB_NAME(mf.database_id) AS databaseName,
name AS File_LogicalName,
CASE
WHEN type_desc = 'LOG' THEN 'Log File'
WHEN type_desc = 'ROWS' THEN 'Data File'
ELSE type_desc
END AS File_type_desc
,mf.physical_name
,num_of_reads
,num_of_bytes_read
,io_stall_read_ms
,num_of_writes
,num_of_bytes_written
,io_stall_write_ms
,io_stall
,size_on_disk_bytes
,size_on_disk_bytes/ 1024 AS size_on_disk_KB
,size_on_disk_bytes/ 1024 / 1024 AS size_on_disk_MB
,size_on_disk_bytes/ 1024 / 1024 / 1024 AS size_on_disk_GB
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.FILE_ID = divfs.FILE_ID
ORDER BY num_of_Reads DESC