Most Active Databases in SQL Server
I recently had someone ask what the most active databases were in our SQL Server. Well… we knew which was the most active (based on our application) but there were a number of databases (including tempdb’s) that we were interested in quickly discovering some high level usage stats on.
The following quick and dirty dynamic function query not only includes the number of reads and writes, but some useful io, filesize and stall figures also.
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_filesAS mf ON mf.database_id = divfs.database_id AND mf.FILE_ID = divfs.FILE_ID ORDER BY num_of_Reads DESC
I’ve also enjoyed this excellent article on leveraging sys.dm_io_virtual_file_stats DMV over at MSDN Blogs: http://blogs.msdn.com/b/dpless/archive/2010/12/01/leveraging-sys-dm-io-virtual-file-stats.aspx