SQL Server性能优化——等待——SLEEP_BPROOL_FLUSH

前言：

有一个用于历史归档的数据库（简称历史库），经过一定时间的积累，数据文件已经达到700多GB，后来决定某些数据能够不须要保留。就把这部分数据truncate了，空余出600多GB的空间。也就是说，经过收缩后，理论上数据库仅仅有100多G。为此，我经过重建各个表（表数量不多，但单表数量还是有几千万）的聚集索引后，准备进行收缩。

可是当收缩開始时，即使把每次收缩的范围缩小到500MB，速度也极其慢。常常几个小时都没反应。经过查看等待信息之后发现有一个SPID=18的会话（SPID<=50的均为系统会话）一直显示等待状态为“SLEEP_BPROOL_FLUSH”，而且堵塞了收缩操作。

为此，我觉得即使是小概率事件（由于这个等待类型尽管常见，可是并不总引人注意），既然出现了。就最好还是来研究一下。

说明：环境为SQL Server 2008R2

 本文出处：http://blog.csdn.net/dba_huangzj/article/details/50455543

简单介绍：

既然这已经成为了问题，那么有必要先了解一下SLEEP_BPOOL_FLUSH这个等待状态是什么。

在微软官方说明中：https://technet.microsoft.com/zh-cn/library/ms179984(v=sql.105).aspx ，仅有简单的描写叙述：当检查点为了避免磁盘子系统泛滥而中止新 I/O 的公布时出现。

明显这样的解释是不足的。因此我翻翻国外大牛的博客和其它书籍，总结例如以下：

这样的等待状态与checkpoint进程有直接关系。checkpoint主要用于在内存的缓冲区（BufferPool）中，自载入到内存之后发生了数据改变（称为脏页），在checkpoint触发后把脏页从内存回写到磁盘的数据文件里。

所以非常自然地想到Checkpoint。可是从行为特性来看。又意味着可能你的磁盘子系统有性能问题。

Checkpoint简单介绍：

要了解SLEEP_BPOOL_FLUSH等待类型，有必要先了解一下Checkpoint这个东西。

它是SQL Server后台触发的系统进程。也能够手动输入checkpoint来执行。

这个进程负责把缓冲区的被改动过的页写入到数据文件里。常见的地方是在备份中。这个进程的重要作用之中的一个是加快数据库在异常情况下恢复的速度。当数据库发生问题时，SQL Server必须把数据库尽可能地还原到之前的正常状态。SQL Server会使用事务日志进行重做（redo）或回滚（undo），把未写入数据文件的改动又一次附加会数据文件里。假设数据页被改动但还未写入数据文件，SQL Server必须把改动重做。假设之前已经有一次Checkpoint发生并把这些脏页写到数据文件，那么这一步就能够跳过。从而加快数据库的恢复速度。

如图所看到的：

 

当一个数据页被事务改动后，这个改动会先被记录在事务日志中（实际上不写入LDF文件而是内存中的一块叫log buffer的区域中，然后再写到磁盘的LDF文件里，这个过程由WRITELOG和LOGBUFFER等待类型表示）。然后在内存的buffer pool中的相应数据页标识为脏页。

当Checkpoint进程触发时，全部自上一次Checkpoint发生后至今的脏页都会被物理地写入磁盘的数据文件里，这个过程不会管引发脏页的事务的状态是什么（提交、未提交、回滚）。

通常来说，Checkpoint由SQL Server自己主动周期性执行（默认情况下为一分钟）。

可是不代表真的是仅仅有等待1分钟才触发。用户能够设置这个执行周期只是除非你确定问题的根源在此。否则不要随便改动。

由于Checkpoint会自己分析当前IO请求、延时等情况进行触发。

从而避免不必要的高IO开销。

 本文出处：http://blog.csdn.net/dba_huangzj/article/details/50455543

在SQL Server中，有以下几种Checkpoint类型（关于Checkpoint的具体描写叙述将在兴许文章中专门介绍）：

1. 内部Checkpoint类型：不可配置，在特定情况下自己主动触发，比方备份。

2. 自己主动Checkpoint类型：假设未改动SQLServer相关配置。会在1分钟周期时触发。这样的类型能够改动时间，可是这样的改动是实例级别的。而且仅仅能改动为小于等于1分钟。
3. 手动Checkpoint类型：通过SSMS或其它client发起checkpoint命令。这样的触发能够输入一个秒数，用于指定checkpoint必须在这个秒数内完毕。这样的操作是库级别的。比方CHECKPOINT 10。代表SQL Server会在10秒内尝试执行checkpoint。具体内容可见：https://technet.microsoft.com/zh-cn/library/ms188748(v=sql.105).aspx
4. 间接Checkpoint类型：这是SQLServer 2012引入的库级别选项。

   假设这个值大于0则会覆盖特定数据库上的默认自己主动Checkpoint配置，能够通过以下命令实现：

ALTER DATABASE[数据库名] SET TARGET_RECOVERY_TIME = [秒数或分钟数]

前面提到过。SQL Server会分析当前系统压力，当它觉得当前不是必需进行Checkpoint时，会扼杀这个进程，从而避免磁盘子系统的雪上加霜。当Checkpoint被扼杀时，就会记录在SLEEP_BPOOL_FLUSH等待类型的信息中。

在正常情况下，这样的等待状态应该尽可能接近0。

减少SLEEP_BPOOL_FLUSH等待：

既然有问题，那么就该解决，即使它可能通常没有多大性能问题。

遇到这个问题时，建议首先检查配置，还是那句话。如无必要不要改动默认配置。能够通过以下语句查询配置值：

select * from sys.configurations where name ='recovery interval (min)'

当中“value”为0代表默认配置，这个值以分钟为单位。值越小，Checkpoint的频率就越高，越easy引发SLEEP_BPOOL_FLUSH等待。另外在事务中频繁使用CHECKPOINT命令也非常easy触发这样的等待。

除了这样的情况之外，另一个可能就是数据文件所在的磁盘子系统的性能问题。前面提到过，Checkpoint触发的结果是把缓冲区的脏页写入磁盘，假设当前磁盘负载非常大，那么Checkpoint操作就会被频繁扼杀。从而引起SLEEP_BPOOL_FLUSH等待。

回归主题：

前面介绍了这样的等待状态的含义、原因，那么如今来看看我的问题，由于问题还是要解决。

经过检查，默认配置没问题，而我在执行的操作是数据文件收缩。所以问题应该是在收缩上面。

收缩数据文件有三个潜在问题：

1. 收缩的逻辑就是把数据移动到数据文件较前的区中，由于收缩是从数据文件的最后的区開始回收，这个操作会消耗大量的时间和系统资源用于移动全部的数据。在这个过程中，SQL Server使用大量的CPU资源去决定数据能够移动到哪里。有多少空间能够用于移动，同一时候要求大量的IO资源用于从数据文件里读取数据和把数据写入到新的物理地址中。另外，假设表没有聚集索引。那么非聚集索引由于叶子节点记录了RID信息，所以移动会导致非聚集索引的信息更新开销。注意是“每一个非聚集索引的每一行”都受影响。不用多说都能够想象到。这是非常高开销的操作。
2. 日志文件的增长：无论当前使用何种恢复模式。SQL Server都会记录每一个数据移动操作。每一个数据页和区的分配或回收。还有每一个索引的变更。这样的记录会加重前面第一个问题的系统资源开销，同一时候会导致日志文件的高速增大。有一位MVP的博客上介绍了数据文件收缩所需的日志文件数量：http://www.karaszi.com/SQLServer/info_dont_shrink.asp
3. 添加表和索引的碎片：须要先说明，碎片不总是坏事，由于存在就有存在的理由。有非常多操作并不受碎片影响。这部分能够看微软的白皮书：https://technet.microsoft.com/en-us/library/cc966523.aspx 。里面介绍了碎片的不通类型和须要关注的碎片情景。

通过前面的分析。在查看server那个历史库所在的磁盘（普通SAS盘）。能够初步确定是磁盘IO性能问题。由于在之前已经对全部表的聚集索引进行了重建（没有堆表），应该是数据紧密度足够高。

这就是最头痛的问题，不可能由于收缩慢就说换磁盘，即使能换，財务流程也不是一般的繁琐。那么我们还是来想想怎么使得每次读写操作尽可能地小吧。

 本文出处：http://blog.csdn.net/dba_huangzj/article/details/50455543

这个是一个历史库，历史库在月底（写本文的时候）会有比較多的月结类、年度结算类查询，在频繁使用的过程中收缩文件显然不合理。所以把这个操作放在闲时执行（闲时并不一定就是晚上，主要看系统类型和操作时间段）。另外。收缩的规模也要尽可能小。为了避免一大片的语句，能够用以下语句进行自己主动化收缩：

 

declare @sql nvarchar(1024)
declare @size int=758000--当前大小，MB为单位
declare @end int =1024  --停止范围
while @size>=@end  --直到达到停止范围前一直循环
begin
set @sql='DBCC SHRINKFILE (N''数据文件名称'','+cast(@size as nvarchar(20))+')'
--print @sql
exec (@sql)
set @size=@size-500
end

当中凝视掉的print语句用来检查将要执行的命令是否正确。

这里仅仅是抛砖引玉，读者能够依据实际情况改动或加入其它功能。另外代码倒数第二行set @size=@size-500意思是每次收缩500MB。读者也能够依据具体情况測试，可能100MB/次反而是最好最快的，那最好还是设为set @size=@size-100。

通过调整每次收缩的规模、安排闲时执行。不定期手动执行Checkpoint。尽管等待状态依然（毕竟磁盘性能是硬伤），可是收缩进度还如意。

最重要的手段还是在server闲时进行。在重复測试之后，晚上11点之后，server维护作业还未执行。而用户已经下班。此时即使每次收缩100G，也仅仅须要1个多小时。

尽管结果有点不如意，读者可能希望看到怎样彻底解决。

可是毕竟是正式环境，不能轻易尝试和改动。可是除了前面的方式之外，还是有其它方式能够按需选择：

1. 拆分数据文件，把文件移动到负荷较低或性能较高的磁盘。只是这个操作要考虑数据后期合并。

2. 某些库是能够短暂脱机的，能够把数据库移动到性能较好的盘然后附加再进行收缩。
3. 事实上。

   。。不收缩未尝不是件好事。

总结：

SLEEP_BPOOL_FLUSH等待跟SQL Server的Checkpoint进程有密切关系。而Checkpoint主要负责的是把脏页写入磁盘。在Checkpoint触发前，SQL Server会分析server当前负载。假设磁盘子系统压力过大导致Checkpoint被觉得必须扼杀时，SQL Server会把这样的状态记录到SLEEP_BPOOL_FLUSH等待状态中。

在一个正常的系统中。这样的等待状态的等待时间不应该非常长，可是它还是有可能影响系统性能。

过于频繁地执行CHECKPOINT命令或把“recovery interval”的值设的过低，都可能引发SLEEP_BPOOL_FLUSH等待。数据文件的磁盘子系统性能过低也相同会引发这样的等待信息。

因此，在发现这样的等待状态频繁出现或等待时间非常长时。须要检查SQL Server配置、语句及磁盘子系统。

 本文出处：http://blog.csdn.net/dba_huangzj/article/details/50455543

下一篇我们来聊聊本文重复出现的Checkpoint。