SQL Server服务器I/O问题的初步诊断

目录

SQL Server服务器I/O问题的初步诊断

由于SQL Server数据库是运行在操作系统之上的程序，因此对于I/O问题，可以从两个地方进行抓取：windows性能计数器（注：SQL Server2016已支持Linux上的运行）与SQL Server 动态管理视图

--显示SQL server自上次启动以来所有被使用的数据库文件的延时--file_ID 可从sp_helpfileselect     DB_NAME(DATAbase_ID) as 'database name',    FILE_ID,    io_stall_read_ms/num_of_reads as 'avg read transfer/ms',    io_stall_write_ms/num_of_writes as 'avg write transfer/ms'from sys.dm_io_virtual_file_stats(-1,-1)where num_of_reads > 0        and num_of_writes >0;

从下图可以看出磁盘的读存在一定的延时，若读超过100ms，则基本可确认磁盘存在I/O问题

另外更为准确的是从windows性能计数器评估磁盘I/O：

Rule Description Value Source Problem Description 1 Average Disk sec/read 大于20 ms Perfmon object Physical Disk Reads should take 4-8 ms without any IO pressure. 2 Average Disk sec/write 大于20 ms Perfmon object Physical Disk Writes (sequential) can be as fast as 1 ms for transaction log.—如果是用来放LDF文件的磁盘，这个值最好不要大于3毫秒。 3 If Top 2 values for wait stats are any of the following: ASYNCH_IO_COMPLETION/IO_COMPLETION/LOGMGR/WRITELOG/PAGEIOLATCH_x Top 2 Sys.dm_os_wait_stats If top 2 wait_stats values include IO, there is an IO bottleneck. Special care needs to be taken on log file

参考资料