SQL SERVER – Find Most Active Database in SQL Server – DMV dm_io_virtual_file_stats
Posted
by pinaldave
on SQL Authority
See other posts from SQL Authority
or by pinaldave
Published on Mon, 19 Apr 2010 01:30:38 +0000
Indexed on
2010/04/19
1:33 UTC
Read the original article
Hit count: 808
sql
|SQL Authority
|SQL Query
|SQL Server
|SQL Tips and Tricks
|T SQL
|Technology
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.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Filed under: SQL, SQL Authority, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
© SQL Authority or respective owner
