SQL Server 性能诊断（2）- I/O

丶Loaf
229 阅读11分钟

1. 概述

SQL Server主要是在一些什么情况下需要和硬盘进行交互？

  1. 对于内存中没有缓存的数据，第一次访问时需要将数据所在的页面从数据文件中读取到内存里。
  2. 在任何Insert/Update/Delete提交之前，SQL Server需要保证日志记录能够写入到日志文件里
  3. 当SQL Server做检查点（Checkpoint）的时候，需要将内存缓冲区中已经发生过修改的数据页面同步到硬盘中的数据文件里
  4. 当SQL Server缓冲区（Buffer Pool）空间不足的时候，会触发Lazy Writer，主动将内存里的一些很久没有使用过的数据页面和执行计划清空。如果这些页面上发生的修改还未由检查点（checkpoint）写回硬盘， Lazy Writer会将其写回
  5. 一些特殊的操作，例如DBCC CHECKDB、Reindex、Update Statistics、数据库备份等，会带来比较大的硬盘读/写

检查点（Checkpoint）和Lazy Writer 区别是什么？

检查点是为了缩短恢复时间，Lazy Writer是为了缓解缓冲池的压力。所以，Checkpoint是定时执行的，与内存是否存在压力无关。

SQL Server主要的磁盘读写行为

SQL Server的I/O动作有下面这些特点：

  • 对于SELECT操作，只要数据缓存在内存里，就不应该有任何硬盘读写（理想情况）。
  • SQL Server基本只读数据文件，读的量和内存的缓存能力有直接关系，也和用户须要读取的数据量有关系。
  • SQL Server的写分两块，数据文件和日志文件。数据文件写的数目，主要和数据修改量有关，Lazy Writer也和内存是否有压力有关系。日志文件写的数目，完全由数据修改量决定，和内存是否有压力没有关系，可以粗略的认为日志文件是Write Through的。

所以可以比较笼统地讲，SQL Server发生的读数量，和内存是否有压力有直接关系。内存越没有压力，读的数量就会越少（注意不是写，这点非常重要，很多人会有这样的误解）。而SQL Server发生的写数量，和用户已经完成的数据修改量有直接关系。如果一个数据库大部分操作都是查询，SQL Server发生的写操作会非常少。这和内存是否有压力关系倒不是非常大。

其他对SQL Server I/O产生影响的错误：

  1. SQL Server的Recovery Interval（sp_configure）
  2. 数据/日志文件的自动增长和自动收缩
  3. 数据文件里页面碎片程度
  4. 表格上的索引结构
  5. 数据压缩
  6. 数据文件与日志文件是否放在同一块磁盘上
  7. 一个数据文件组是否有多个文件，并且放在不同的物理磁盘上

针对磁盘的性能监视器收集指标主要有两个，LogicalDisk和PhysicalDisk：

  • LogicalDisk记录的是按照逻辑盘符，每个逻辑盘的读写计数器
  • PhysicalDisk以物理磁盘为单位。

如果某个物理磁盘是由RAID组成的磁盘阵列，那么Windows只会列出一整块物理磁盘。如果物理磁盘上有若干个逻辑分区，这些分区将以一组为单位出现。

SQL Server I/O性能分析思路：

  • 优先解决内存瓶颈 ，避免内存不足导致额外的磁盘压力。
  • 检查磁盘子系统性能 ，确保硬件支持当前负载。
  • 明确 SQL Server 的 I/O 贡献 ，将问题精确定位到数据库层。
  • 分解 I/O 问题 ，分析主要的 I/O 类型及其触发条件。
  • 优化引发 I/O 的 SQL 逻辑，通过查询调整和索引优化，减少不必要的磁盘访问。

具体方法如下：

  1. 确定是否存在内存瓶颈

目标：明确内存资源的不足是否导致了过多的 I/O。

  • 检查方法：
    • Windows 层面：监控 Paging（分页）活动，查看指标如 Page Faults/sec 和 Pages/sec。高分页活动意味着内存不足。
    • SQL Server 层面：监控 Buffer Cache Hit Ratio，如果低于 90%，可能需要更多内存。
    • 查看 Target Server Memory 和 Total Server Memory 的差异。若总内存低于目标内存，说明内存可能不足。
  • 解决方案：
    • 提升服务器的物理内存。
    • 调整 SQL Server 的 Max Server Memory 配置，防止与操作系统争夺内存。
    • 减少大型查询或优化索引使用，以降低内存压力。
  1. 分析磁盘子系统性能

目标：确认磁盘性能是否正常，是否支持系统负载。

  • 检查方法：
    • Windows 层面：
      • 监控 Disk Read/Write Latency：建议小于 5ms。
      • 使用工具如 Performance Monitor 检查 Avg. Disk sec/Read 和 Avg. Disk sec/Write 是否过高。
      • 查看 Disk Queue Length：队列过长可能表示 I/O 压力。
    • 硬件层面：
      • 检查 RAID 配置、磁盘类型（SSD vs HDD）、缓存设置等。
      • 确认磁盘读写吞吐量与硬件规格是否匹配。
  • 解决方案：
    • 增加磁盘数量或优化 RAID 配置。
    • 升级到更快的存储设备（例如从 HDD 升级到 SSD）。
    • 调整存储阵列缓存策略，提高写入性能。
  1. 分析 SQL Server 的 I/O 活动

目标：明确 SQL Server 对系统 I/O 的贡献程度。

  • 检查方法：
    • 使用 DMVs（动态管理视图）分析 SQL Server 的 I/O 消耗：
      • 查看 sys.dm_io_virtual_file_stats 获取数据文件和日志文件的读写统计。
      • 查看等待统计 sys.dm_os_wait_stats，特别是 I/O 相关的等待类型，如 PAGEIOLATCH_* 或 WRITELOG。
    • 分析 I/O 密集操作发生的时段与负载高峰是否一致。
    • 确认 SQL Server 是否在正常工作模式下对磁盘进行大量操作。
  1. 定位具体 I/O 操作类型

目标：找到 SQL Server 中导致 I/O 的主要操作类型。

  • 检查方法：
    • 通过 sys.dm_os_buffer_descriptors 确认缓存中页的使用情况，分析热点数据。
    • 检查以下操作是否是主要的 I/O 消耗来源：
      • Page Reads：高频的逻辑或物理读，可能是由于未优化的查询或索引缺失。
      • Page Writes：后台线程（Lazy Writer）频繁将脏页写回磁盘。
      • Checkpoints：大批量的脏页写回操作，通常发生在批量事务或大事务完成时。
      • Log Writes：频繁的事务日志写入，可能由事务太小或高频提交引起。
  • 工具：
    • 使用扩展事件或 SQL Trace 跟踪具体操作的频率和影响。
    • 查看 sys.dm_exec_requests 和 sys.dm_exec_query_stats 捕获耗费 I/O 的查询。
  1. 优化 I/O 密集查询

目标：降低引起大量 I/O 的查询或指令。

  • 检查方法：
    • 使用 Query Store 分析高 I/O 消耗的查询。
    • 检查执行计划中是否存在：
      • 未使用索引导致的 Table Scan。
      • 数据溢出导致的 TempDB 使用增加。
    • 使用 SET STATISTICS IO ON 检查查询的逻辑读和物理读次数。
  • 优化方案：
    • 创建和优化索引，减少全表扫描。
    • 分析事务逻辑，尽量合并小事务，避免频繁提交。
    • 重写查询以减少临时表或大结果集的创建。

2. 性能监视器

-- （1）系统级IO分析
PhysicalDisk
  % idle time
  % disk time
  % disk read time
  % disk write time
  Avg. disk sec/read
  Avg. disk sec/write
  Avg. disk sec/transfer
  Avg. disk bytes/transfer
  Avg. Disk Queue Length
  Avg. disk read queue length
  Avg. disk write queue length
  Disk Bytes/sec
  Disk Transfers/sec
  Disk Reads/sec
  Disk Writes/sec
  Disk Read Bytes/sec
  Disk Write Bytes/sec
  Current Disk Queue Length
-- （2）SQL Server I/O分析
SQL Server:Buffer Manager
  Page Reads/sec和Page Writes/sec
  Lazy Writes/sec
  Checkpoint Writes/sec
  Readahead Pages/sec
SQL Server:Access Methods
  Freespace Scans/sec
  Page Splits/sec
  Page Allocations/sec
  Workfiles/sec
  Worktables/sec
  Full Scans/sec
  Index Searches/sec
SQL Server:Databases
  Log Flushes/sec
  Log Bytes Flushed/sec
  Log Flush Wait Time
  Log Flush Waits/sec
SQL Server:SQL Statistics
  Batch Requests/sec
SQL Server:Databases
  Active Transactions

-- （1）系统级IO分析指标解释：

  1. % Disk Time
  • 含义：表示磁盘在采样区间内处于读写状态的百分比。
  • 特点：
    • 理论上，其值应小于100%，并且为% Disk Read Time 和 % Disk Write Time 的总和。
    • 在磁盘非常繁忙时，该值可能远大于100%，这是由于现代磁盘可能包含多个磁头（spindle）。
  • 使用建议：该计数器仅能作为趋势参考，准确性较低。
  1. % Idle Time
  • 含义：表示磁盘在采样区间内处于空闲状态的百分比。
  • 特点：
    • 当磁盘完全空闲时，其值为100%。
    • 当磁盘满负荷工作时，其值为0%。
  • 使用建议：通过该值反推% Disk Time，更可靠。
  1. Disk Bytes/sec
  • 含义：每秒钟磁盘读写的数据量（单位：Bytes）。
  • 计算方式：为 Disk Read Bytes/sec 和 Disk Write Bytes/sec 的总和。
  • 参考值：
    • 好：20 ~ 40 MB/s（高性能磁盘可超过200 MB/s）。
    • 一般：10 ~ 20 MB/s。
  • 使用建议： 可用于评估磁盘的吞吐量，结合其他指标综合分析。
  1. Avg. Disk sec/transfer
  • 含义：磁盘每次读写操作的平均耗时。
  • 参考值：
    • 很好：< 10 ms。
    • 一般：10 ~ 20 ms。
    • 有点慢：20 ~ 50 ms。
    • 非常慢：> 50 ms。
  • 使用建议：直接反映磁盘的 I/O 延迟，是衡量磁盘性能的重要指标。
  1. Avg. Disk sec/read 和 Avg. Disk sec/write
  • 含义：分别表示磁盘每次读操作和写操作的平均耗时。
  • 参考值：与 Avg. Disk sec/transfer 相同。
  • 使用建议：进一步细分读写性能问题，发现瓶颈所在。
  1. Avg. Disk Queue Length
  • 含义：表示在某个时间点磁盘队列的长度，即正在等待磁盘处理的 I/O 请求数量。
  • 特点：
    • 理论上该值不应长时间大于2。
    • 随着存储体系的演变（如 SAN），该指标的直接参考意义下降。
  • 使用建议：对于现代存储系统，结合硬件结构和实际磁盘数量评估该值的意义。

-- （2）SQL Server I/O分析指标解释：

  1. 缓冲池相关
    Page Reads/sec: 每秒从磁盘读入的页面数。反映磁盘读取负载。
    Page Writes/sec: 每秒写入到磁盘的页面数。反映磁盘写入负载。
    Lazy Writes/sec: Lazy Writer 每秒写入磁盘的页面数。高值可能表明内存压力较大。
    Checkpoint Writes/sec: 每秒由检查点（Checkpoint）写入磁盘的脏页数。反映定期刷盘行为。
    Readahead Pages/sec: 每秒 SQL Server 预读的页面数。预读是为了优化 IO 性能，通常是顺序读时的优化措施。
  2. 辅助访问操作
    Freespace Scans/sec: 堆（Heap）结构中搜索空闲空间的次数。如果该值高，可能需要优化表设计，增加聚集索引。
    Page Splits/sec: 由于插入数据导致的页面分裂次数。高值通常意味着频繁的数据写入，可通过重建索引或调整填充因子降低此值。
    Page Allocations/sec: 为新对象（表、索引等）分配页面的次数。此值反映新对象创建的频率。
  3. 工作文件和表相关
    Workfiles/sec: SQL Server 为哈希操作等生成的临时工作文件数。过高的值可能与索引优化不足有关。
    Worktables/sec: SQL Server 每秒创建的临时工作表数。反映查询复杂性或临时表使用情况。
  4. 数据扫描相关
    Full Scans/sec: 每秒全表扫描的次数。高值可能表明索引设计不合理或查询优化不足。
    Index Searches/sec: 每秒通过索引完成的查询次数。较高值表明索引被有效使用。
  5. 日志活动相关
    Log Flushes/sec: 每秒日志写入的次数。反映事务日志的写入频率。
    Log Bytes Flushed/sec: 每秒写入日志的字节数。反映日志写入负载。
    Log Flush Wait Time: 写日志时等待磁盘响应的总时间。理想情况下应为 0。
    Log Flush Waits/sec: 每秒因日志写入等待的事务数。高值表明日志磁盘性能可能不足。
  6. Batch Requests/sec: SQL Server每秒完成的批处理（Batch）数目。
  7. Active Transactions: SQL Server里打开的还没有提交的事务数目。

3. 动态性能视图

检查当前所有的等待累积值

Select  wait_type,
        waiting_tasks_count,
        wait_time_ms
from sys.dm_os_wait_stats
where    wait_type like 'PAGEIOLATCH%'
order by wait_type;

select * from sys.dm_exec_requests;

sys.dm_os_wait_stats的字段定义

可以通过运行下面的查询得到每个文件的信息，了解哪个文件经常要做读（num_of_reads/ num_of_bytes_read），哪个经常要做写（num_of_writes/num_of_bytes_written），哪个文件的读写经常要等待（io_stall_read_ms/io_stall_write_ms/ io_stall）。

select db.name as database_name, f.fileid as file_id,
f.filename as file_name,
i.num_of_reads, i.num_of_bytes_read, i.io_stall_read_ms,
i.num_of_writes, i.num_of_bytes_written, i.io_stall_write_ms,
i.io_stall, i.size_on_disk_bytes
from sys.databases db inner join
sys.sysaltfiles f on db.database_id = f.dbid
inner join sys.dm_io_virtual_file_stats(NULL, NULL) i
on i.database_id = f.dbid and i.file_id = f.fileid;

sys.dm_io_virtual_file_stats的字段定义

当前SQL Server中每个处于挂起状态的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

4. 错误日志排查

这个消息表明，SQL Server已向磁盘的发出读取或写入请求，但是该请求返回所用的时间已超过15秒。也就是说，某个磁盘的读写请求过了15秒还没做完。

SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds
to complete on file [F:...

条件允许可以借助磁盘性能分析工具，比对两端磁盘性能差异。

参考文献：

《SQL Server 2012事实与管理实战指南》

avatar
文章
阅读
粉丝