I/O performance of MS SQL Server Analysis database

The system sometimes responds noticeably slowly, sometimes due to an increase in the number of visitors, sometimes due to the frequent disk I/O access times of the database. MS SQL Server provides some dynamic management views and functions for us to analyze disk I/O performance.

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_st All_read_ms, Vfs.num_of_writes, Vfs.num_of_bytes_written, Vfs.io_stall_write_ms, Size_on_d isk_bytes/1024/1024.        As [Size_on_disk_mbytes (MB)], Mf.physical_namefrom sys.dm_io_virtual_file_stats (The 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_idorder by Avg_total_latEncy DESC 

If Sys.dm_io_virtual_file_stats (null,null) displays information about all the default databases and the databases that have been attached, just want to look at the I/O information for the master database and perform the following analysis on the database:

2, Sys.dm_io_cluster_shared_drives and Sys.dm_io_cluster_valid_path_names

If your database schema uses cluster deployment, you can find information about the cluster nodes through both. Future sys.dm_io_cluster_shared_drives will be discarded and replaced by Sys.dm_io_cluster_valid_path_names.