IO瓶颈

下列系统监视计数器来检测物理磁盘级IO瓶颈：

1. 物理磁盘对象：磁盘队列平均长度（Avg.Disk Queue Length） 指的是抽样间隔内所选中的物理磁盘上排除等待的物理读/写请求的平均数据。如果IO系统超负荷了，更多的读/写操作就会陷入等待。如果每个物理磁盘的磁盘队列长度总是超过SQL Server巅峰使用期时的数值2，那么很可能就出现了IO瓶颈。
2. 物理磁盘对象：磁盘每次读/写平均用时（Avg.Disk Sec/Read(write)） 指的是每次从磁盘中读取或写入数据时的平均用时（以秒计）。通用指标如下：
   1. 小10ms，非常好；
   2. 10ms-20ms之间，还过得去；
   3. 20ms-50ms之间，很慢需要多加注意；
   4. 大于50ms则被认为是严重的IO瓶颈。
3. 物理磁盘对象：磁盘每秒读/写数（Disk Reads(Writes)/Sec） 指的是磁盘上读/写操作的速度。一定要确保该数字小于磁盘容量的85%。当超过磁盘容量的85%时，磁盘访问时间将呈指数级增长。

下面DMV查询可以用来确认文件级的IO性能信息：

SELECT database_id, DB_NAME(database_id), file_id, io_stall_read_ms, io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)

io_stall_read_ms 和 io_stall_write_ms这两列表示的是SQL Server启动后等待向文件发出读和写指令的时间。为了获取有意义的数据，需要在短时间内对这些数据进行快照。然后将它们同基线数据相比较。

 

还可以通过检查等待锁来确认全部的IO瓶颈。最重要的等待锁是PAGEIOLATCH 和 PAGEIOLATCH_EX。当一个任务在锁存器上等待一个IO请求中的缓冲区时，这两种等待锁就会启动。

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type LIKE 'PAGEIOLATCH%'
ORDER BY wait_type

wait_time_ms 列包括一个工作进程在悬挂状态下花费的时间和在可运行状态下花费的时间，而 singnal_wait_time_ms 表示的只是一个工作进程在可运行状态下花费的时间。因此，这两者（wait_time_ms – signal_wait_time_ms）的差别实际上代表了等待IO完成所花的时间。上面查询返回的是SQL Server启动后的等待。要想获得有意义的数据，同样需要在短时间内对这些数据进行快照，然后对比。

 

IO瓶颈的隔离和排查，下面DMV查询返回引发IO最多的前10位查询或批处理。

SELECT TOP 10
      (total_logical_reads/execution_count) AS avg_logical_reads,
      (total_logical_writes/execution_count) AS avg_logical_writes,
      (total_physical_reads/execution_count) AS avg_physical_reads,
      execution_count,
      (SELECT SUBSTRING(text, statement_start_offset/2+1,
            (CASE WHEN statement_end_offset = -1 THENLEN(CONVERT(nvarchar(max), text)) * 2
            ELSE statement_end_offset END - statement_end_offset)/2)
      FROM sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes) DESC

在许多可以通过减少基IO数目以改进查询性能的方法中，首先应该检查有用的索引有没有丢失。下面DMV查询就可以用来确认丢失的索引及它们的有用性：

SELECT t1.object_id,t2.user_seeks, 52.user_scans, t1.equality_columns,t1.inequality_columns
FROM sys.dm_db_missing_index_details AS t1,sys.dm_db_missing_index_group_stats AS t2, sys.dm_db_missing_index_groupsAS t3
WHERE database_id = DB_ID() AND object_id = OBJECT_ID('tablename') ANDt1.index_handle = t3.index_handle AND t2.group_handle =t3.index_group_handle

转载于:https://www.cnblogs.com/ideas/archive/2011/02/11/1951539.html