MS SQL Server分析資料庫的I/O性能

　　1、sys.dm_io_virtual_file_stats

select  db_name(vfs.database_id) as database_name ,

vfs.database_id ,

vfs.file_id ,

io_stall_read_ms / nullif(num_of_reads, 0) as avg_read_latency ,

io_stall_write_ms / nullif(num_of_writes, 0) as avg_write_latency ,

io_stall / nullif(num_of_reads + num_of_writes, 0) as avg_total_latency ,

num_of_bytes_read / nullif(num_of_reads, 0) as avg_bytes_per_read ,

num_of_bytes_written / nullif(num_of_writes, 0) as avg_bytes_per_write ,

vfs.io_stall ,

vfs.num_of_reads ,

vfs.num_of_bytes_read ,

vfs.io_stall_read_ms ,

vfs.num_of_writes ,

vfs.num_of_bytes_written ,

vfs.io_stall_write_ms ,

size_on_disk_bytes / 1024 / 1024. as [size_on_disk_mbytes(mb)] ,

mf.physical_name

from    sys.dm_io_virtual_file_stats(db_id('master'), 1) as vfs

join sys.master_files as mf on vfs.database_id = mf.database_id

and vfs.file_id = mf.file_id

order by avg_total_latency desc

　　如果sys.dm_io_virtual_file_stats(null,null)那麼會顯示所有預設資料庫和曾經附加過的資料庫的資訊，這裡隻想看master資料庫的i/o資訊，對資料庫上執行後結果分析如下：

　　2、sys.dm_io_cluster_shared_drives和sys.dm_io_cluster_valid_path_names

　　如果你的資料庫架構采用了叢集部署，那麼可以通過這兩個查到關于叢集節點的資訊。未來sys.dm_io_cluster_shared_drives将被廢棄，采用sys.dm_io_cluster_valid_path_names代替。

最新内容請見作者的github頁：http://qaseven.github.io/