SQL SERVER – Find Most Active Database in SQL Server – DMV dm_io_virtual_file_stats

Few days ago, I wrote about SQL SERVER – Find Current Location of Data and Log File of All the Database. There was very interesting conversation in comments by blog readers. Blog reader and SQL Expert Sreedhar has very interesting DMV presented which lists the most active database in SQL Server. For quick reference he has included the size of the disk in KB, MB and GB as well.

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

If you like to read and practice with DMVs, I suggest to read the blog of my very good friend Glenn Berry. He is one DMV expert.

Read more: Journey to SQL Authority with Pinal Dave

Posted via email from jasper22's posterous