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