ITKeyword,专注技术干货聚合推荐

注册 | 登录

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

DBA_Huangzj 分享于

2021腾讯云限时秒杀,爆款1核2G云服务器298元/3年!(领取2860元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1062

2021阿里云最低价产品入口+领取代金券(老用户3折起),
入口地址https://www.aliyun.com/minisite/goods

推荐:SQL SERVER性能优化综述

一个系统的性能的提高,不单单是试运行或者维护阶段的性能调优的任务,也不单单是开发阶段的事情,而是在整个软件生命周期都需要注意,进行有效工作才能达到的。

前言:


有一个用于历史归档的数据库(简称历史库),经过一定时间的积累,数据文件已经达到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或其他客户端发起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等待:


既然有问题,那么就该解决,即使它可能通常没有多大性能问题。遇到这个问题时,建议首先检查配置,还是那句话,如无必要不要修改默认配置。可以通过下面语句查询配置值:

推荐:SQL Server性能优化的一些技巧

数据库性能优化涉及到很多方面,在数据库开发时可以通过一些基本的优化技巧提高数据库的性能: 1.原则上为创建的每个表都建立一个主键,主键唯一标识某一行记录

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

其中“value”为0代表默认配置,这个值以分钟为单位,值越小,Checkpoint的频率就越高,越容易引发SLEEP_BPOOL_FLUSH等待。另外在事务中频繁使用CHECKPOINT命令也很容易触发这种等待。

除了这种情况之外,还有一个可能就是数据文件所在的磁盘子系统的性能问题。前面提到过,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 。里面介绍了碎片的不通类型和需要关注的碎片情景。

通过前面的分析,在查看服务器那个历史库所在的磁盘(普通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,虽然等待状态依旧(毕竟磁盘性能是硬伤),但是收缩进度还如意。

最重要的手段还是在服务器闲时进行,在反复测试之后,晚上11点之后,服务器维护作业还未运行,而用户已经下班,此时即使每次收缩100G,也只需要1个多小时。

虽然结果有点不如意,读者可能希望看到如何彻底解决。但是毕竟是正式环境,不能轻易尝试和修改。但是除了前面的方式之外,还是有其他方式可以按需选择:

  1. 拆分数据文件,把文件移动到负荷较低或性能较高的磁盘。不过这个操作要考虑数据后期合并。
  2. 某些库是可以短暂脱机的,可以把数据库移动到性能较好的盘然后附加再进行收缩。
  3. 其实。。。不收缩未尝不是件好事。

总结:


SLEEP_BPOOL_FLUSH等待跟SQL Server的Checkpoint进程有密切关系,而Checkpoint主要负责的是把脏页写入磁盘。在Checkpoint触发前,SQL 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。

 

 

推荐:优化 SQL Server 查询性能

导读:   优化数据库服务器时,您需要优化个人查询性能。这与优化服务器安装的其他影响硬件和软件配置等性能的方面一样重要 — 甚至更为重要。   即使数据库

前言: 有一个用于历史归档的数据库(简称历史库),经过一定时间的积累,数据文件已经达到700多GB,后来决定某些数据可以不需要保留,就把这部分数据truncate了,空余出600多GB的空间,也就是

相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。