数据库系统严重依赖服务器的资源: CPU, 内存和硬盘 IO, 通常情况下, 内存是数据的读写性能最高的存储介质, 但是, 内存的价格昂贵, 这使得系统能够配置的内存容量受到限制, 不能大规模用于数据存储; 并且内存是易失性的, 不能持久化存储数据, 这使得内存只能作为运行时的高速缓存, 而硬盘是永久存储数据的理想介质, 价格低廉, 在系统停电时, 能够保持数据不丢失. 但是, 硬盘是低速的存储介质, 输入和输出 (IO) 速度比内存低很多. 因此, 在实际运行的数据库系统中, 相对于内存而言, 硬盘的 IO 有更大可能性成为系统性能的瓶颈.
内存和硬盘都是存储资源和 IO 资源, 由于内存的容量相对较小, IO 速度快, 因此, 内存更有可能成为争用的存储资源; 而硬盘容量大, IO 速度快, 因此, 硬盘更有可能成为系统争用 IO 资源. SQL Server 为了平衡存储和 IO 资源的争用, 在把数据从硬盘读取到内存后, 会把数据缓存到内存中, 当重复访问数据时, 不需要从硬盘, 而是直接从内存中获取. 由于这个机制, 为系统配置足够多的内存可以最小化硬盘 IO, 因为硬盘读取数据的速度远远低于内存, 所以, 尽可能减少硬盘 IO 可以在很大程度上提供系统的性能.
一, 硬盘 IO 的延时
对于 SQL Server 数据库系统, 限制查询响应的主要因素是硬盘的延时, 根据硬盘的物理构造(磁道和扇区), 延时可以分为寻道延时和旋转延时:
寻道延时: 硬盘的物理刺头移动并定位到所需数据的时间,
旋转延时: 硬盘旋转到所需数据的时间, 通常用 MB/S, 或 IO 吞吐量来衡量
在 OLTP 系统中, 数据更新操作较多, 每次读取的数据量少, 目标数据的位置相对随机(随机读写), 因此, 对于寻道延时要求更高, 硬盘需要花费更多的寻道时间.
在 DSS/DW 系统中, 事务的运行时间更长, 数据相对静态, 不常更新, 读操作比写操作的要求更高, 顺序读操作占比很高, 因此, IO 吞吐量更重要, 可以通过硬盘的盘面来增加顺序访问的 IO 吞吐量.
二, 根据 WaitType 侦测 IO 性能
SQL Server 引擎把 IO 作为一个资源来看待, 在多任务的现代数据库系统中, 同一时刻会接收到很多查询请求, 每一个查询请求都需要申请系统资源 (CPU, 内存和 IO), 才能继续执行下去, 然而系统的资源是有限的, 当查询争用资源时, 有些查询请求资源得到满足, 顺利执行下去, 有些查询请求的资源得不到满足, 该查询就被阻塞, 处于等待资源分配的状态. 当出现 IO 性能问题时, 查询语句会被硬盘 IO 阻塞, 这使得执行计划被迫挂起(或阻塞) 来等待资源, SQL Server 通过 DMV 来显示系统运行的状态, 用等待类型来表示不同的阻塞信息.
1, 数据文件的 IO
如果 SQL Server 出现 IO 性能问题, 那么在 SQL Server 内部通过 DMV sys.dm_exec_requests 的 wait_type, 来反馈 IO 问题. 如果查询请求的 wait_type 长时间处于 PageIOLatch_XX, 那么说明系统不能很快把数据读取到内存中.
PAGEIOLATCH_xx : 用于描述数据页的 IO 争用, 说明系统正在从硬盘加载数据到内存的 Buffer Pool 中
当 SQL Server 要去读或写一个 Page 的时候, 首先会在 Buffer Pool 里寻找, 如果在 Buffer Pool 中找到了, 那么读写操作会继续进行, 没有任何等待. 如果没有找到, 那么 SQL Server 就会设置 Wait_Type 为 PageIOLatch_EX(写)或 PageIOLatch_SH(读), 然后发起一个异步 IO 操作, 将页面读入 Buffer Pool 中, 在 IO 没有完成之前, Request 将会保持在 PageIOLatch_EX(写)或 PageIOLatch_SH(读)的等待状态. IO 消耗的时间越长, 等待的时间越长.
2, 日志文件的写入
日志文件以写为主, 工作量由修改命令激发的事务数量决定. 当 SQL Server 要写事务到日志文件时, 如果 Disk 不能及时完成 IO 请求, 那么事务就无法提交, SQL Server 不得不进入 WriteLog 等待状态, 直到事务被成功记录到日志文件中, 才会提交当前的事务.
如果 request 经常出现 WriteLog 的 Wait type, 说明事务日志的写请求不能被 Disk 及时完成, 这种情况, 对 SQL Server 整体性能影响较大.
WRITELOG: 在数据被修改时, 在 Log Cache 和 Buffer Cache 中都会有记录, 如果在 Log Cache 中的数据在 checkpoint 时写入硬盘, 就会发生这种等待.
LOGBUFFER 等待: 很少出现, 当一个任务正在等待存储日志到 Log Buffer 中时, 就会出现 LOGBUFFER 等待, 出现这种等待, 说明日志所在的硬盘无法响应请求. 如果把日志文件放在一个非常慢的硬盘上, 而数据文件放在一个非常快的硬盘上, 就会出现这种等待.
3,AYSNC_IO_COMPLIETION 和 IO_COMPLIETION 也是 IO 瓶颈的潜在指标
AYSNC_IO_COMPLIETION: 标识任务正在等待 IO 请求来完成操作, 当一个应用程序连接 SQL Server, 在处理数据时变得非常慢, 很可能就会出现这种类型的等待.
IO_COMPLIETION: 发生在一个任务正在等待用于非数据页 IO 的 IO 操作上, 非数据页, 一般是指日志文件, 通常发生在修改大量修改, 或者内存中存在大量的脏数据时.
三, 影响读写性能的因素
数据库系统对 IO 的性能依赖较高, 那么影响数据库系统读写性能的因素有哪些呢?
1, 物理硬盘的 IO 能力
机械硬盘的 IO 速度没有固态硬盘快, 可以考虑把数据库系统的机械硬盘更新为固态硬盘.
2, 内存对硬盘 IO 的影响
在 SQL Server Engine 访问数据时, 如果相应的 data 不存在于 Buffer Pool, 那么 Buffer Manager 从 Disk 中的 Data File(mdf 或 ndf)中将相应的 data page 读取到内存中. SQL Server 将 data page 缓存起来. 理想情况下, 只要 SQL Server 能够使用的内存充足, SQL Server 会将所有读取到内存的中 Data Page 缓存到 Buffer Pool 中. 对于读取操作, 只要相应的数据都缓存在内存中, Select 就不会有任何硬盘 IO.
当 Buffer Pool 空间不足时, SQL Server 激活 LazyWriter, 主动将内存中一些很久没有使用的 Data Cache 和 Plan Cache 清除, mark 为 Free buffer, 供其它 Data Page 使用. 如果这些 Page 上的修改还没有被 CheckPoint 写回 Disk, 那么 LazyWrite 会将其写回.
3, 碎片和压缩
如果数据页面或 index 页面的碎片很多, 每个页面存储的数据行较少, 那么 SQL Server 需要读写更多的 Page. 如果数据在页面里存储的非常紧凑, 存储相同数据所消耗的 Page 越少, 并且可以充分利用 SQL Server 预读的优势, 减少 IO.
压缩技术不仅使数据占用的 Disk 空间减少, 而且能够减少 IO. 由于数据在写入 Disk 之间经过压缩处理, 存储相同数据所消耗的 Page 减少, 读取的 Data Page 会减少. 压缩技术在一定程度上能够降低 IO, 但需要付出一定的代价: 额外消耗少量的 CPU 和内存来解压缩.
4, 利用多个物理硬盘实现 Data File 的并发读写
在 DB 中的 FileGroup 创建多个 File, 将这些 File 存放到不同的 Physical Disk 上. File 分布到不同的 Physical Disk 上, IO 也会分布到不同的 Physical Disk 上, 这样能够实现数据的并发读取, 提高读取性能.
对于日志文件, SQL Server 会频繁的写事务日志. 只要数据库发生修改, 就会不断地写入日志文件. 如果不能及时完成日志文件的 IO, 会导致事务的延迟提交, 对性能的影响较大, 所以, 尽量将日志文件放到写入速度快的 Disk 上. SQL Server 顺序写事务日志, 在一个时间点, SQL Server 只会写一个日志文件. 在不同的 Physical Disk 上创建多个 log file 对性能基本没有帮助.
5, 工作负载
日志文件以写为主, 工作量由修改命令申请的事务数量决定, 日志文件是顺序写的, 写入速度快于随机写. 如果日志记录不能及时写入, 那么 Request 会处于 WriteLog 等待状态, 对系统整体性能影响较大.
数据文件写入的数据量由修改量决定, SQL Server 除了设置 bulk logged 恢复模式之外, 没有太大的调整选项.
数据文件读取的数据量, 由访问的数据量和 Buffer Pool 中缓存的数据量共同决定. 如果访问的数据量减少或者内存缓存区增加, 都可以降低 SQL Server 从 Physical Disk 读取的 Data Page 数量. 在内存不变的情况下, 可以通过优化查询语句, 减少数据访问量, 来提高 SQL Server 数据文件的读取性能.
四, 硬盘 IO 的性能优化
硬盘 IO 的性能调优, 通常来说, 跟 Buffer Pool 的大小和数据的分布有关
1, Buffer Pool
Buffer Pool 是 SQL Server 数据库系统的缓冲池, 用于缓存从硬盘读取的数据页. 当 SQL Server 所需的数据不在内存的 Buffer Pool 中时, 就会触发硬盘 IO, 把数据从硬盘中的文件中读取到内存中的 Buffer Pool 中. 如果所需的数据存在于 Buffer Pool 中, SQL Server 直接从内存中获取数据, 不会触发任何硬盘的 IO 操作. 因此, 内存容量足够大, 硬盘 IO 将会足够小. 如果系统存在内存压力, 那么 SQL Server 将会频繁地触发硬盘 IO, 从硬盘文件中获取数据, 这将会增加查询的响应时间.
2, 多硬盘并发 IO
在存储数据时, 把数据分布在不同的物理硬盘上, 在读写数据时, 可以把工作负载分担到不同的物理硬盘上, 多个硬盘并发处理数据, 将会大大降低数据的读写时间.
因此, 在设计数据库系统时, 应该尽量把数据分布到不同的物理硬盘上, 并且每个硬盘上的数据量保持均衡, 这样, 才能最大化利用多硬盘的优势, 实现数据的读写时间最小化.
3, 日志文件
当修改数据时, 事务会被记录到日志文件中, 事务日志的写入速度, 直接影响了数据更新查询语句的执行效率. 当数据库中存在大量的修改操作时, 应该把日志文件存储到 IO 性能最优的硬盘上, 以减少日志文件写入的时间延迟.
4,tempdb 数据库文件
tempdb 是数据库实例中最繁忙的数据库了, 在查询语句执行的过程中, 查询语句创建的各种临时表, 系统创建的中间表都位于 tempdb 中, tempdb 的数据文件和日志文件的读写性能, 直接影响了查询语句的执行时间, 应该把 tempdb 数据库的数据文件部分到不同的物理硬盘中, 并且把 tempdb 的日志文件存放到 IO 性能最优的硬盘上去.
简而言之, 对于数据库系统的优化配置是:
在 OLTP 系统中, 合理的配置是把数据文件, 日志文件和 tempdb 的文件分别存放到不同的物理硬盘上, 从而分摊硬盘的 IO 争用.
在 OLAP 系统中, 事务运行时间长, 规模大, 数据相对静态, 每次返回的数据量较大, 对 IO 吞吐量的要求较高, 因此, 尽可能分摊硬盘的 IO 争用.
5, 创建合适的索引
如果一个查询需要进行表扫描, 一般是因为缺失合适的索引或索引统计信息过时, 过多的扫描操作会引起内存不足, 使得缓存中的数据或执行计划被清除(或者被转移到硬盘), 然后从硬盘加载数据到内存. 理想情况下, 常用的数据应该尽可能久地驻留在内存中, 避免不必要的内存活动.
创建合适的索引, 并保证统计信息及时更新, 能够避免不必要的表扫描, 只加载小的数据集, 能够减少 IO 操作的次数, 优化 IO 性能.
6, 数据压缩
数据压缩会使得相同的存储空间能够存储更多的数据量, 一次 IO 操作能够加载更多的数据, 这也能减少 IO 操作的次数, 优化 IO 性能.
五, IO 统计
IO 请求的等待和挂起, 数据库引擎记录对数据文件和日志文件的 IO 操作, 缓存到函数: sys.dm_io_virtual_file_stats, 对于数据文件, 数据的物理读操作更为重要; 对于日志文件, 数据的读写操作都重要:
io_stall_read_ms: 等待读操作的时间
io_stall_write_ms: 等待写操作的时间
如果硬盘繁忙, 数据库引擎发送的 IO 请求, 可能会被 IO 子系统挂起(pending), 数据库引擎把 pending 的 IO 请求缓存到视图: sys.dm_io_pending_io_requests,
io_pending: 指定是否有 IO 请求挂起或完成
1, 查看数据库文件的 IO 和等待 IO 完成的时间
- select db_name(vfs.database_id) as db_name,
- --vfs.file_id,
- mf.name as file_name,
- mf.type_desc as file_type,
- vfs.sample_ms/1000/60/60 as sample_h,
- vfs.io_stall_read_ms/vfs.num_of_reads as avg_stall_read_ms,
- vfs.io_stall_write_ms/vfs.num_of_writes as avg_stall_write_ms,
- vfs.num_of_reads as physical_reads,
- vfs.num_of_bytes_read/vfs.num_of_reads/1024 as avg_read_kb,
- vfs.num_of_writes as physical_writes,
- vfs.num_of_bytes_written/vfs.num_of_writes/1024 as avg_written_kb,
- cast(vfs.size_on_disk_bytes/1024/1024/1024.0 as decimal(10,2)) as disk_size_gb,
- --cast(mf.size/1024*8/1024.0 as decimal(10,2)) as file_size_gb,
- vfs.file_handle
- from sys.master_files mf
- cross apply sys.dm_io_virtual_file_stats(mf.database_id,mf.file_id) as vfs
- where mf.database_id=db_id() --current db
- order by avg_stall_read_ms desc ,avg_stall_write_ms desc
2, 查看 pending 的 IO 请求
- select db_name(vfs.database_id) as db_name,
- --vfs.file_id,
- mf.name as file_name,
- pr.io_type,
- sum(pr.io_pending_ms_ticks) as io_pending_ms,
- pr.io_pending
- from sys.dm_io_virtual_file_stats(null,null) vfs
- inner join sys.dm_io_pending_io_requests as pr
- on vfs.file_handle=pr.io_handle
- inner join sys.master_files mf
- on vfs.database_id=mf.database_id
- and vfs.file_id=mf.file_id
- group by vfs.database_id,
- mf.file_id,
- mf.name,
- pr.io_type,
- pr.io_pending
- order by vfs.database_id,
- mf.name
3, 计划缓存中的逻辑写排名
- select
- p.name as sp_name
- ,s.total_logical_reads
- ,s.total_logical_writes
- ,s.total_physical_reads
- ,s.total_elapsed_time
- ,s.total_worker_time
- ,s.cached_time
- ,s.execution_count
- ,s.type
- ,s.type_desc
- from sys.procedures p
- inner join sys.dm_exec_procedure_stats s
- on p.object_id=s.object_id
- where s.database_id=DB_ID()
- and s.total_logical_writes>0
- order by s.total_logical_writes
参考文档:
- Windows Performance Monitor Disk Counters Explained
- High Avg Disk Queue Length and finding the Cause
- Disk Queue Length vs. Disk Latency Times: Which is Best for Measuring Database Performance
来源: https://www.cnblogs.com/ljhdo/p/10104408.html