一. 概述
sql server 作为关系型数据库, 需要进行数据存储, 那在运行中就会不断的与硬盘进行读写交互. 如果读写不能正确快速的完成, 就会出现性能问题以及数据库损坏问题. 下面讲讲引起 I/O 的产生, 以及分析优化.
二. sql server 主要磁盘读写的行为
2.1 从数据文件 (.mdf) 里, 读入新数据页到内存. 前页讲述内存时我们知道, 如果想要的数据不在内存中时, 就会从硬盘的数据文件里以页面为最小单位, 读取到内存中, 还包括预读的数据. 当内存中存在, 就不会去磁盘读取数据. 足够的内存可以最小化磁盘 I/O, 因为磁盘的速度远慢于内存.
2.2 预写日志系统 (WAL), 向日志文件(.ldf) 写入增删改的日志记录. 用来维护数据事务的 ACID.
2.3 Checkpoint 检查点发生时, 将脏页数据写入到数据文件 , 在 sp_configure 的 recovery interval 控制着 sql server 多长时间进行一次 Checkpoint, 如果经常做 Checkpoint, 那每次产生的硬盘写就不会太多, 对硬盘冲击不会太大. 如果隔长时间一次 Checkpoint, 不做 Checkpoint 时性能可能会比较快, 但累积了大量的修改, 可能要产生大量的写, 这时性能会受影响. 在绝大多数据情况下, 默认设置是比较好的, 没必要去修改.
2.4 内存不足时, Lazy Write 发生, 会将缓冲区中修改过的数据页面同步到硬盘的数据文件中. 由于内存的空间不足触发了 Lazy Write, 主动将内存中很久没有使用过的数据页和执行计划清空. Lazy Write 一般不被经常调用.
2.5 CheckDB, 索引维护, 全文索引, 统计信息, 备份数据, 高可用同步日志等.
三. 磁盘读写的相关分析
3.1 sys.dm_io_virtual_file_stats 获取数据文件和日志文件的 I/O 统计信息. 该函数从 sql server 2008 开始, 替换动态管理视图 fn_virtualfilestats 函数. 哪些文件经常要做读 num_of_reads, 哪些经常要做写 num_of_writes, 哪些读写经常要等待 io_stall_*. 为了获取有意义的数据, 需要在短时间内对这些数据进行快照, 然后将它们同基线数据相比较.
- SELECT 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(null, null)
- WHERE num_of_reads> 0 AND num_of_writes> 0
io_stall_read_ms: 用户等待文件, 发出读取所用的总时间(毫秒).
io_stall_write: 用户等待在该文件中完成写入所用的总时间毫秒.
3.2 windows 性能计数器: Avg. Disk Sec/Read 这个计数器是指每秒从磁盘读取数据的平均值
<10 ms - 非常好
10 ~ 20 ms 之间 - 还可以
20 ~50 ms 之间 - 慢, 需要关注
> 50 ms - 严重的 I/O 瓶颈
3.4 I/O 物理内存读取次数最多的前 50 条
SELECT TOP 50
qs.total_physical_reads,qs.execution_count,
- qs.total_physical_reads/qs.execution_count AS [avg I/O],
- qs. creation_time,
- qs.max_elapsed_time,
- qs.min_elapsed_time,
- SUBSTRING(qt.text,qs.statement_start_offset/2,
- (CASE WHEN qs.statement_end_offset=-1
- THEN LEN(CONVERT(NVARCHAR(max),qt.text))*2
- ELSE qs.statement_end_offset END -qs.statement_start_offset)/2) AS query_text,
- qt.dbid,dbname=DB_NAME(qt.dbid),
- qt.objectid,
- qs.sql_handle,
- qs.plan_handle
from sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.total_physical_reads DESC
3.5 使用 sp_spaceused 查看表的磁盘空间
exec sp_spaceused 'table_xx'
reserved: 保留的空间总量
data: 数据使用的空间总量
index_size: 索引使用空间
Unused: 未用的空间量
3.6 监测 I/0 运行状态 STATISTICS IO ON;
四 磁盘读写瓶颈的症状
4.1 errorlog 里报告错误 833
4.2 sys.dm_os_wait_stats 视图里有大量等待状态 PAGEIOLATCH_* 或 WriteLog. 当数据在缓冲区里没有找到, 连接的等待状态就是 PAGEIOLACTH_EX(写) PAGEIOLATCH_SH(读), 然后发起异步操作, 将页面读入缓冲区中. 像 waiting_tasks_count 和 wait_time_ms 比较高的时候, 经常要等待 I/O, 除在反映在数据文件上以外, 还有 writelog 的日志文件上. 想要获得有意义数据, 需要做基线数据, 查看感兴趣的时间间隔.
- select wait_type,
- waiting_tasks_count,
- wait_time_ms ,
- max_wait_time_ms,
- signal_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: 该等待类型的总等待时间 (包括一个进程悬挂状态(Suspend) 和可运行状态 (Runnable) 花费的总时间)
max_wait_time_ms: 该等待类型的最长等待时间
signal_wait_time_ms: 正在等待的线程从收到信号通知到其开始运行之间的时差(一个进程可运行状态 Runnable 花费的总时间)
i/o 等待时间 ==wait_time_ms - signal_wait_time_ms
五 优化磁盘 I/O
5.1 数据文件里页面碎片整理. 当表发生增删改操作时索引都会产生碎片(索引叶级的页拆分), 碎片是指索引上的页不再具有物理连续性时, 就会产生碎片. 比如你查询 10 条数据, 碎片少时, 可能只扫描 2 个页, 但碎片多时可能要扫描更多页(后面讲索引时在细说).
5.2 表格上的索引. 比如: 建议每个表都包含聚集索引, 这是因为数据存储分为堆和 B-Tree, 按 B-Tree 空间占用率更高. 充分使用索引减少对 I/0 的需求.
5.3 数据文件, 日志文件, TempDB 文件建议存放不同物理磁盘, 日志文件放写入速度比较快的磁盘上, 例如 RAID 10 的分区
5.4 文件空间管理, 设置数据库增长时要按固定大小增长, 而不能按比例, 这样避免一次增长太多或太少所带来的不必要麻烦. 建议对比较小的数据库设置一次增长 50MB 到 100MB. 下图显示如果按 5% 来增长近 10G, 如果有一个应用程序在尝试插入一行, 但是没有空间可用. 那么数据库可能会开始增长一个近 10G, 文件的增长可能会耗用太长的时间, 以至于客户端程序插入查询失败.
5.5 避免自动收缩文件, 如果设置了此功能, sql server 会每隔半小时检查文件的使用, 如果空闲空间 > 25%, 会自动运行 dbcc shrinkfile 动作. 自动收缩线程的会话 ID SPID 总是 6(以后可能有变) 如下显示自动收缩为 False.
5.6 如果数据库的恢复模式是: 完整. 就需要定期做日志备份, 避免日志文件无限的增长, 用于磁盘空间.
来源: https://www.cnblogs.com/MrHSR/p/9102479.html