sqlserver的IO管理

SQL Server 的I/O管理是一个庞大且精密的体系，其核心目标是保证数据的事务完整性（ACID），同时追求极致的读写效率。

理论知识

造成SQL Server I/O的操作

1、对于内存中没有缓存的数据，第一次访问时需要将数据所在的页面从数据文件中读取到内存里；
2、SQL Server会将修改所产生的日志记录写入日志记录缓存区，而SQL Server又会保证缓存区内的内容会很及时地写入日志文件，哪怕有任何意外发生（包括掉电、服务异常等）。可以粗略地理解成：在任何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、数据库备份等，会带来比较大的硬盘读写。

解读：SQL Server发生的读数量，和内存是否有压力有直接关系(内存越没有压力，读的数量就会越少)，也和用户需要读取的数据量有关系。而SQL Server发生的写数量，和用户已完成的数据修改量有直接关系，与内存是否有压力没有关系。

性能计数器与IO相关的指标

以下是几个与IO相关的性能计数器，仅供参考：

性能计数器	计数器说明	注释
% Disk Time	磁盘处于做读写状态的百分比	由于算法问题,磁盘很忙会远大于100%，所以该值不准
% Idle Time	磁盘处于空闲状态的百分比	磁盘满负荷的时候值为0%，推荐用这个
Disk Bytes/sec	每秒钟磁盘作的读和写的数量	参考：<20MB 一般、>50MB 良好、>200MB 优秀
Avg. disk sec/transfer	磁盘每做一次读或者写的动作所花的平均时间	-
Avg. disk sec/read	磁盘每做一次读操作所花的平均时间	-
Avg. disk sec/write	磁盘每做一次写操作所花的平均时间	参考：<10ms很好、>10ms一般、>20ms稍慢、>50ms很慢
Avg. Disk Queue Length	平均磁盘队列长度	发出的磁盘操作正在等待被磁盘处理的请求数目

案例1：checkpoint造成的IO繁忙

案例：下图是一个硬盘读写非常慢的SQL Server服务器上收集的性能日志，当SQL Server做checkpoint（Checkpoint pages/sec<图未展示>与Avg. Disk Bytes/sec周期性地上升，像心跳一样）的时候，Avg. Disk Queue Length最高150，Disk Bytes/sec的最大值大部分时候7.5MB，而Avg. Disk sec/Write却经常超过50ms。这是一个明显的因为Checkpoint带来的disk IO不能及时完成，而影响了SQL Server性能的症状

磁盘IO繁忙的症状

如果 SQL Server 发出的读写请求磁盘不能及时完成，SQL Server 会遇到什么问题呢？主要的症状有两个

1、SQL Server 在 errorlog 里报告 833 错误。发生这种问题的时候，SQL Server 的响应速度会受到很大的影响，甚至整个服务的响应都会变得很慢。

2018-04-22 16:30:02.140 spid6s  SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [F:\...

2、SQL Server 的 errorlog 里没有错误，但是 sys.sysprocesses 有许多连接的等待状态是 PAGEIOLATCH_*（针对数据文件）或者 WRITELOG（针对日志文件）。发生这种问题的时候，SQL Server 的响应速度会受到很大的影响。

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

解读：PAGEIOLATCH_* 等待类型是 SQL Server 中最常见的 I/O 相关等待。它们都表示一个会话正在等待从磁盘将某个数据页读取到内存（缓冲池）中。而后缀（_EX, _SH, _KP, _DT）则指明了该会话在等待期间，准备对内存中的这个数据页施加的锁模式（Latch Mode）。

等待类型	锁模式	典型产生场景	通俗理解
PAGEIOLATCH_SH	共享 (Shared)	只是读取页面内容，不修改	我要读这页，并和他人共享
PAGEIOLATCH_EX	独占 (Exclusive)	计划修改页面内容(脏页)	我要改写这页，谁也别碰
PAGEIOLATCH_UP	更新 (Update)	先读取，然后可能修改	我可能改，先不锁死，留个活口
PAGEIOLATCH_DT	销毁 (Destroy)	页面即将从缓冲池中被移除	这页没用了，我要毁掉它
PAGEIOLATCH_KP	保持 (Keep)	防止页面在某个操作期间被销毁或移出	我要用这页，别把它从我手里拿走

当 SQL Server 要去读或者写一个页面的时候，它首先会在 Buffer Pool 里寻找。如果在 Buffer Pool 里找到了，那么读/写操作会继续进行，没有任何等待。如果没找到，就会设置连接的等待状态是 PAGEIOLATCH_EX（写）或者 PAGEIOLATCH_SH（读），然后发起一个异步I/O操作，将页面读入 Buffer Pool 中。在I/O没做完之前，连接都会保持在 PAGEIOLATCH_EX 或者 PAGEIOLATCH_SH 的等待状态。I/O消耗的时间越长，等待的时间也会越长。

案例2：磁盘存在瓶颈造成的833错误

案例：客户抱怨每天晚上 SQL Server 会突然很慢，几乎没响应。根据客户提供的时间点，在 SQL Server Errorlog 里可以找到这样的信息：

2018-05-20 00:06:27.33 spid4s     SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\DATA\AA_1.ndf] in database [AA] (6).  The OS file handle is 0x0000000000000A00.  The offset of the latest long I/O is: 0x00000bb44f0000
2018-05-20 00:06:57.41 spid4s     SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\DATA\BB_log.ldf] in database [BB] (5).  The OS file handle is 0x0000000000000958.  The offset of the latest long I/O is: 0x00000000c5bc00
2018-05-20 00:08:18.06 Server     Process 4:0:0 (0x9e4) Worker 0x00000000038661C0 appears to be non-yielding on Scheduler 15. Thread creation time: 12855596328382. Approx Thread CPU Used: kernel 0 ms, user 0 ms. Process Utilization 0%. System Idle 99%. Interval: 74998 ms.
2018-05-20 00:11:45.27 spid13s    A time-out occurred while waiting for buffer latch -- type 2, bp 0000000696FE5B00, page 1:1937, stat 0x9c0040d, database id: 6, allocation unit Id: 72223169859026944, task 0x0000000007812478 : 0, waittime 300, flags 0x1a, owning task 0x0000000007812478. Not continuing to wait.

第1步，这两个833错误发生在两个数据库的文件上，一个是AA库的数据文件，一个是BB库的日志文件。这说明读写问题是系统级的，而不是单个数据库级的，也就排除了某个特别的应用导致问题的可能性。
第2步，SQL Server对日志文件的操作一般都是顺序写、小量多次，不会有超大量的I/O请求。现在居然对日志文件的某个操作都花了15秒钟没有返回，可见磁盘的响应速度一定出了大问题。有意思的是，这两个文件都在E盘上。
第3步，后面SQL Server在几分钟里报了一个non-yielding的警告和Buffer Latch timeout的错误。这两个错误通常都会影响SQL Server的顺畅运行
第4步，看看E盘当时到底出了什么问题，在0:06AM到0:14AM之间，E盘的 % Idle Time突然降到了0，说明磁盘当时在忙着做什么事情。Current Disk Queue Length却一直不为0，说明一直有请求处于等待的状态。可是不正常的是，Disk Bytes/sec居然在那段时间里等于0，很明显，磁盘当时没有响应。所以SQL Server当时的I/O请求都不能得到执行，由此影响到了整个SQL Server的顺畅运转
第5步，当磁盘恢复正常后，SQL Server也恢复正常。所以这个问题要从硬件的层面着手解决

如何查看IO相关的信息

查看最近60天、存在833错误的日志

--.查看最近60天、存在833错误的日志
DECLARE @start_time datetime 
SET @start_time = GETDATE()-60 
EXEC sys.xp_readerrorlog 0,1,N'I/O',Null,@start_time,NULL,'DESC'

查看每个磁盘分区的使用率

--.查看每个磁盘分区的使用率
DECLARE @version SYSNAME 
DECLARE @exec NVARCHAR(1024) 
SET @version = CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(40)) 
SET @version = SUBSTRING(@version,1,CHARINDEX('.',@version)-1) 

IF @version >= 11 
BEGIN 
  SET @exec = 'SELECT DISTINCT UPPER(vs.volume_mount_point) AS disk_partition, vs.file_system_type,CONVERT(DECIMAL(18,2),vs.total_bytes/1073741824.0)' + CHAR(10) 
    + 'AS [total_size_gb],CONVERT(DECIMAL(18,2),vs.available_bytes/1073741824.0) AS [available_size_gb],' + CHAR(10) 
    + 'CAST(1-CAST(vs.available_bytes AS FLOAT) / CAST(vs.total_bytes AS FLOAT) AS DECIMAL(18,2)) * 100 AS [space_used_pct]' + CHAR(10) 
    + 'FROM sys.master_files AS mf WITH(NOLOCK)' + CHAR(10) 
    + 'CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.[file_id]) AS vs ORDER BY disk_partition ASC' 
  EXECUTE (@exec) 
END

实战配置

服务器层面

1.内存建议越多越好

增加内存，将更多数据存放在高速缓存区，从而减少IO读写
如果内存存在瓶颈，系统会做大量的 paging 动作，磁盘就会很忙。这个时候就要先解决内存瓶颈，才能降低 I/O 瓶颈

2.磁盘设备建议选择 SSD

数据库服务器的磁盘，优先选择SSD（NVMe SSD比SATA SSD更好），而 NVMe SSD 单盘的风险，可通过 Alwayson 多副本来解决

解读：因为在高性能场景下，机械硬盘（HDD）的随机读写能力是数据库最主要的性能瓶颈。建议mdf和tempdb使用SSD硬盘，以提高随机读写性能，而一般SAS硬盘亦可满足ldf的顺序写性能。

以下是一份不同设备利用sqlio及diskspd压测的64K随机写（64K随机读更好）的性能数据：

设备类型	IOPS	吞吐量(64K随机写)	设备型号
SATA 机械盘 x 1	70-90	10 MB/s	SATA 7400 rpm
SAS 机械盘 x 1	150-180	13 MB/s	SAS 15000 rpm
SATA SSD 800GB x 8 + RAID 10	27000	1700 MB/s	Intel SATA SSD 800GB
NVMe SSD 3.2 TB x 1	65773	4110 MB/s	Memblaze 3.2TB

3.磁盘阵列建议选择 RAID 10

如果是多块磁盘，磁盘阵列则建议 RAID 10，不建议 RAID 5

解读：RAID 5 的读取很快，写入较慢，它的的读写速度约为单块硬盘的 N-1 倍，但写入速度一般，甚至达不到良好，适合读多写少的场景，比如文件服务器、冷存储等。

实例层面

4.配置恢复间隔和压缩备份

Recovery Interval（恢复间隔）控制着 SQL Server 多长时间进行一次 checkpoint。在绝大多数SQL Server上，缺省的设置是比较好的，没必要修改它

解读：同样的修改量，若经常做checkpoint则每次checkpoint所产生的硬盘写就不会太多，对硬盘的冲击不会太大

启用备份压缩虽然会略微增加CPU的负载，但换来的收益非常显著：备份文件显著减小(压缩率通常可达3:1)、备份速度更快、节省存储成本

5.推荐启用即时文件初始化

授予 SQL Server 服务账户“执行卷维护任务”，开启即时文件初始化，这能显著缩短数据文件自动增长或创建数据库时的等待时间

6.优化备份作业

针对数据库备份、索引重建等有大量IO操作的作业，请安排在CPU和IO空闲时段执行，比如凌晨2-5点

7.定期重建页面碎片

定期执行索引重建，减少页面碎片，避免将更多的零碎的页面读入内存
重建作业不宜频繁，以免影响IO效能

数据库层面

8.合理设置数据/日志文件的自动增长

建议1年的数据量，合理设置数据文件、日志文件的初始大小
参考当前文件大小及业务规模，设置按固定大小自动增长（比如数据文件每次增长100MB），而不是按比例，以避免一次增长太多或太少所带来的不必要的麻烦，切勿启用自动收缩
定期检测各个数据文件的使用情况，尽量保证每个文件剩余的空间一样大

解读：针对多个数据文件的场景，若要达到分散IO负载的目的，则必须保证同一个文件组里的所有数据文件都有同样大小的空闲空间（不是文件一样大就可以）。如果某个硬盘上的数据文件写满了，SQL Server就不会再往这个硬盘上写了。若所有数据文件全都写满了，SQL Server会选取其中一个文件（不是每一个）做自动增长，新数据全都写入这个文件，直到这个文件再次写满。

对于日志文件，SQL Server在同一个时间只写一个文件，所以加入多个日志文件对性能基本不会有什么帮助

9.合理使用文件组及表分区

条件允许的话，将 TempDB 拆分为多个大小相等的文件，数量尽量与CPU核心数匹配，能有效降低写入竞争
针对超大型数据库，使用文件和文件组，并将其存放在不同的物理磁盘上
针对超大型数据表，使用表分区，以提高磁盘I/O读写

10.谨慎开启延迟持久性

谨慎开启“延迟持久性”（Delayed durability），它以丢数据的风险提高数据导入性能，能极大提升写入吞吐量，特别是报表服务器（效果明显）

解读：开启延迟持久性之后，SQL Server 会“攒批”写入日志。默认情况下，数据可能最多会丢失一个缓冲区的大小或数千条提交的事务。虽然时间通常很短（毫秒到秒级），但你必须意识到这个风险

警告：开启“延迟持久性”后，无论计划内重启还是意外崩溃，均可能导致数据丢失。其原理是：事务提交时，日志先写入内存缓冲区即返回成功（异步），而非同步写入磁盘。服务关闭过程不会自动触发内存中未持久化日志的刷新操作，因此，已提交但尚未落盘的事务将随服务停止而永久丢失。建议重启之前执行 EXECUTE sys.sp_flush_log 将当前数据库的事务日志刷新至磁盘

SQL Server 2014及以后版本，可以在数据库级别启用延迟事务持久性

--.语法
ALTER DATABASE { database_name } SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }
--.示例
ALTER DATABASE db001 SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT

11.谨慎开启快照读

谨慎开启“快照读”，数据库中的读操作（SELECT）将不再被写操作（INSERT/UPDATE/DELETE）阻塞，写操作也不会被读操作阻塞，可显著提升并发性能，并避免脏读，风险就是大幅增加 tempdb 的负载和tempdb的空间占用

--.示例
ALTER DATABASE db001 SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE

架构设计层面

12.优化索引及事务设计

合理的索引設計要建立在对各种查询的分析和预测上
调整事务的隔离级别，降低交易长度和复杂度。尽量返回或修改小的记录集，缩短事务的逻辑处理过程，及早提交或回滚事务可加快查询

13.配置读写分离及多级缓存

使用 Replication、Alwayson 之类的同步技术，建立同步数据库，将频繁操作的表同步多份，将读操作分散到这些同步的表中
利用 Redis、Memcached 等技术，为多个web服务器提供可共享的高性能缓存服务，可缓解数据库的读取压力
利用负载均衡或Cache设备（如Nginx、F5、NetScaler等）缓解WEB访问DB的压力

sqlserver的IO管理

sqlserver的IO管理

理论知识

造成SQL Server I/O的操作

性能计数器与IO相关的指标

案例1：checkpoint造成的IO繁忙

磁盘IO繁忙的症状

案例2：磁盘存在瓶颈造成的833错误

如何查看IO相关的信息

实战配置

服务器层面

1.内存建议越多越好

2.磁盘设备建议选择 SSD

3.磁盘阵列建议选择 RAID 10

实例层面

4.配置恢复间隔和压缩备份

5.推荐启用即时文件初始化

6.优化备份作业

7.定期重建页面碎片

数据库层面

8.合理设置数据/日志文件的自动增长

9.合理使用文件组及表分区

10.谨慎开启延迟持久性

11.谨慎开启快照读

架构设计层面

12.优化索引及事务设计

13.配置读写分离及多级缓存

results matching ""

No results matching ""

sqlserver的IO管理

理论知识

造成SQL Server I/O的操作

性能计数器 与IO相关的指标

案例1：checkpoint造成的IO繁忙

磁盘IO繁忙的症状

案例2：磁盘存在瓶颈造成的833错误

如何查看IO相关的信息

实战配置

服务器层面

1.内存建议越多越好

2.磁盘设备建议选择 SSD

3.磁盘阵列建议选择 RAID 10

实例层面

4.配置恢复间隔和压缩备份

5.推荐启用即时文件初始化

6.优化备份作业

7.定期重建页面碎片

数据库层面

8.合理设置数据/日志文件的自动增长

9.合理使用文件组及表分区

10.谨慎开启延迟持久性

11.谨慎开启快照读

架构设计层面

12.优化索引及事务设计

13.配置读写分离及多级缓存

results matching ""

No results matching ""

性能计数器与IO相关的指标