1 SQL与IO相关的操作:
SQL经常会有数据库页进出缓存池,这样就发生了实质的IO流量.同时,在事务被明确的提交前,日志记录需要写入磁盘.SQL SERVER 为各种目的可以使用TEMPDB,例如存储中间结果,排序,保持行的版本或其他.所以最号的IO子系统对于MSSQL性能非常重要.
2 判断IO的性能系统计数器:
(注意:如果你收集的时间间隔过长,这些平均值可能会倾向于过小.)
A PhysicalDisk Object:
Avg. Disk Queue Length表现在采样周期中所选择的物理磁盘队列中的物理读和写平均请求数量。如果你的I/O系统过载，更多的读/写操作将被等待。如果在很少使用 SQL Server时，你的磁盘队列长度经常超过2，这样你可能遇到了I/O瓶颈
Avg. Disk Sec/Read or Avg. Disk Sec/write 是平均每次从磁盘读取数据的时间
小于10 ms – 很好
在 10 - 20 ms 之间- 正常
在20 - 50 ms 之间- 缓慢，需要注意
大于 50 ms – 严重的I/O 瓶颈
B Physical Disk:
%Disk Time 是所选磁盘驱动器用于服务于读或写请求的总共时间的百分比。一般推荐是如果该值大于50%，则表现为I/O瓶颈。
Avg. Disk Reads/Sec 表现磁盘上读操作的速度。你需要确认该值小于85%的磁盘设计能力。磁盘访问时间指数高于能力的85%。
Avg. Disk Writes/Sec表现在磁盘上写操作的速度。确认该值小于85%的磁盘设计能力。磁盘访问时间指数高于能力的85%。
当使用以上计数器，你在使用RAID配置时，你需要使用下列公式调整结果值。
Raid 0 -- I/Os per disk = (reads + writes) / number of disks
Raid 1 -- I/Os per disk = [reads + (2 * writes)] / 2
Raid 5 -- I/Os per disk = [reads + (4 * writes)] / number of disks
Raid 10 -- I/Os per disk = [reads + (2 * writes)] / number of disks
例如，你有带有2个物理磁盘的RAID-1系统和下列计数器值。
Disk Reads/sec 80
Disk Writes/sec 70
Avg. Disk Queue Length 5
这种情况下，你遇到了(80 + (2 * 70))/2 = 110 I/Os per disk，你的磁盘队列长度为5/2=2.5，表明系统接近于I/O瓶颈。
3 判断IO的性能DMV视图:
A 你也可以通过考察锁等待来识别I/O瓶颈。当数据页通过读或写访问并且在缓存池中页不可用时，这些锁等待占用了大量的物理I/O等待。当页面没有在缓存池中找到时，一个异步I/O请求被发出，I/O的状态是被选中的。如果I/O已经完成，工作进程处理正常。否则，依赖于请求的类型，它会等待 PAGEIOLATCH_EX 或 PAGEIOLATCH_SH。从下列的DMV查询可以用于找到I/O锁等待的统计。
Select wait_type,waiting_tasks_count,wait_time_ms
from sys.dm_os_wait_stats where
wait_type like 'PAGEIOLATCH%' order by wait_type
wait_type waiting_tasks_count wait_time_ms signal_wait_time_ms
-----------------------------------------------------------------------
PAGEIOLATCH_DT 0 0 0
PAGEIOLATCH_EX 1230 791 11
PAGEIOLATCH_KP 0 0 0
PAGEIOLATCH_NL 0 0 0
PAGEIOLATCH_SH 13756 7241 180
PAGEIOLATCH_UP 80 66 0
(
PAGEIOLATCH_DT
在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“破坏”模式。
PAGEIOLATCH_EX
在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“独占”模式。
PAGEIOLATCH_KP
在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“保持”模式。
PAGEIOLATCH_NL
在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“空”模式。
PAGEIOLATCH_SH
在任务等待 I/O 请求中缓冲区的闩锁时发生。闩锁请求处于“共享”模式。
PAGEIOLATCH_UP
在任务等待 I/O 请求中的缓冲区闩锁时发生。闩锁请求处于“更新”模式。
)
下划线的是有兴趣的锁等待。当I/O完成时，工作者被放置到可用队列中。从I/O完成到工作者被实际的调度的时间在 signal_wait_time_ms列说明。如果你得waiting_task_counts and wait_time_ms偏离正常值太多，你可以识别为I/O问题。为了这样，通过使用性能计数器和关键DMV建立正常时运行时的性能基线就十分重要。 wait_types可以识别是否你的I/O子系统处在瓶颈状态，但是他们不能提供任何关于物理磁盘遇到问题的信息。
B 你可以使用下列DMV查询找到当前等待的I/O请求。你可以周期性的执行这些请求检查I/O子系统的健康情况，并找到涉及I/O瓶颈的物理磁盘。
Select
database_id,file_id,io_stall,io_pending_ms_ticks,scheduler_address
from
sys.dm_io_virtual_file_stats
(NULL, NULL)t1,sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
下面是示例的输出。它展示当时在给定的数据库上有3个未决定的I/O 。你可以使用database_id 和 file_id 来找到映射的物理磁盘文件。
io_pending_ms_ticks 表现了所有等待在未决定队列中的个别的I/O。
Database_id File_Id io_stall io_pending_ms_ticks scheduler_address
----------------------------------------------------------------------
6 1 10804 78 0x0227A040
6 1 10804 78 0x0227A040
6 2 101451 31 0x02720040
C
DMV查询能用于找到那个批处理/请求生成了最多的I/O。你将注意到我们没有说明物理写入。如果你了解数据库如何工作，这也没有问题。请求中的DML/DDL并不是直接将数据页写入磁盘。只有通过提交事务语句才能触发将页面物理写入磁盘。通常物理写通过Checkpoint语句完成或通过 SQL Server lazy writer完成。下列DMV查询可以用于找到生成最多I/O的5个请求。调整这些查询，使他们使用较少的逻辑度来完成操作，这样可以减少在缓存池上的压力。这允许其他的查询在缓存池中找到重复执行中所需要的重复数据（而不是通过物理I/O完成）。因此整体系统性能被提高了。
1
select top 5(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_phys_reads,
Execution_count,statement_start_offset as stmt_start_offset,
sql_handle,plan_handlefrom
sys.dm_exec_query_stats
order by(total_logical_reads + total_logical_writes) Desc
通过这个查看上面句柄的查询语句
(
select text
from sys.dm_exec_sql_text(0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB)
)
当然你可以改变查询或取数据的不同视角。例如，在一个查询中声称最多I/O请求的5个请求，你可以通过下列表达式排序：
(total_logical_reads + total_logical_writes)/execution_count
除此之外，你可能希望通过物理I/O排序或其他的操作。然而，逻辑读/写数量来帮助决定是否查询选择的计划被优化过。例如，它可以做一个表扫描而不是使用索引。一些查询，例如使用嵌套循环连接可能有很高的逻辑计数器但是能更好的适应缓存，因为他们重新访问了同样的页面。
2
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_phys_reads,
execution_count,
(SELECT SUBSTRING(text, statement_start_offset/2 + 1,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
ELSE statement_end_offset
END - statement_start_offset)/2)
FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
plan_handle
FROM sys.dm_exec_query_stats
ORDER BY (total_logical_reads + total_logical_writes) DESC
D io_stall_read_ms等待读和io_stall_write_ms等待写如果和基准数字有明显差异 就需要分析(在正常情况下先捕抓,以供以后比较)
SELECT
database_id,
file_id,
io_stall_read_ms,
io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL)
4 IO在企业管理器里的相关设置:
5 解决
A 增加I/O带宽。
B 为当前的磁盘阵列添加更多的物理驱动器和/或使用更快的磁盘代替当前的磁盘。这帮助提升读写访问时间。但是添加的磁盘数量不能比当前I/O控制器所支持的数量大。
C 添加快速或额外的I/O控制器。考虑为当前的控制器添加更多的缓存（如果有可能）
D 考察执行计划并查看那个计划占用了更多的I/O。这样可以找到更好的查询进化（例如，索引）可以减少I/O。如果缺少索引，你可以运行Database Engine Tuning Advisor找到缺失的索引。
