立竿见影的数据库优化脚本以及定时自动优化,特别适用私有云&混合云原创
金蝶云社区-深圳周少斌
深圳周少斌
315人赞赏了该文章 8376次浏览 未经作者许可,禁止转载编辑于2022年4月15日 21:05:42

哈喽,小伙伴们,大家好啊!

    昨日遇到一名资深同行提问,金蝶云星空(数据库239GB大小)系统,在执行成本计算时,在某1步卡住了1.5小时。

    经过长时间的摸排分析,以及相互交流探讨,我们最终形成了一套可行的数据库性能优化方法,并付诸实践,故障立刻被搞定,效果立竿见影,性能提升明显。

    问题摸排分析的过程,可参阅此提问与回答:执行成本计算,过程中卡死 

    接下来,本人将奉献出,经由本人萃取整理的数据库全库优化四步脚本。



/*可一次全部执行,不必分4次。4段语句均为执行全库扫描与优化,故耗时会很久。

执行期间将会拖慢金蝶系统性能,强烈要求必须放在系统空闲时执行。

SQL源代码请见文尾附件,作者:深圳周少斌*/

SET NOCOUNT ON --关闭统计以提升整体执行效率

declare @StepStartTime datetime,@GlobalStartTime datetime --时长统计公共变量:步进开始时间,全局开始时间。

select @StepStartTime=getdate(),@GlobalStartTime=getdate()

declare @TableName TABLE(name nvarchar(100) NOT NULL,UNIQUE CLUSTERED(name)) --声明表变量:待优化的数据库物理表名清单。

declare @TableNameForLoop TABLE(name nvarchar(100) NOT NULL,UNIQUE CLUSTERED(name)) --声明表变量:待优化的数据库物理表名清单_用于循环。

declare @name nvarchar(100),@SqlStr nvarchar(300) --声明变量:待优化物理表名,优化语句。

Insert into @TableName SELECT name from sysobjects where xtype='U' and (name not like'TMP%') --初始化表变量:待优化的数据库物理表名清单


--第1步.重建索引,整理碎片

Insert into @TableNameForLoop SELECT * from @TableName --初始化表变量:用于循环待优化的数据库物理表名清单_用于循环

WHILE EXISTS(SELECT top 100 percent name FROM @TableNameForLoop order by Name)

begin  

  select top 1 @name=name,@SqlStr='' FROM @TableNameForLoop order by Name --取值@TableNameForLoop的第一行

  delete from @TableNameForLoop WHERE name=@name;--删除上一行语句已取值的行。

  set @SqlStr='alter index all on ['+@name+'] rebuild;' --拼接优化语句与待优化物理表名

  BEGIN TRY exec (@SqlStr) END TRY BEGIN CATCH END CATCH --执行优化语句,如果有异常就直接吞掉,继续循环。

end

print '第1步执行时长:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@StepStartTime,getdate())))/60000,2)))+'分钟'; --统计单步执行时长

set @StepStartTime=getdate();


--第2步.更新统计信息,以利于SQL Server后台优化执行计划

Insert into @TableNameForLoop SELECT * from @TableName --重新初始化表变量:用于循环待优化的数据库物理表名清单_用于循环

WHILE EXISTS(SELECT top 100 percent name FROM @TableNameForLoop order by Name)

begin  

  select top 1 @name=name,@SqlStr='' FROM @TableNameForLoop order by Name --取值@TableNameForLoop的第一行

  delete from @TableNameForLoop WHERE name=@name;--删除上一行语句已取值的行。

  set @SqlStr='UPDATE STATISTICS ['+@name+'];' --拼接优化语句与待优化物理表名

  BEGIN TRY exec (@SqlStr) END TRY BEGIN CATCH END CATCH --执行优化语句,如果有异常就直接吞掉,继续循环。

end

print '第2步执行时长:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@StepStartTime,getdate())))/60000,2)))+'分钟'; --统计单步执行时长

set @StepStartTime=getdate();


--第3步.压缩索引以提升I/O性能(注意,这里并不是收缩数据库,而是压缩索引)

Insert into @TableNameForLoop SELECT * from @TableName --重新初始化表变量:用于循环待优化的数据库物理表名清单_用于循环

WHILE EXISTS(SELECT top 100 percent name FROM @TableNameForLoop order by Name)

begin  

  select top 1 @name=name,@SqlStr='' FROM @TableNameForLoop order by Name --取值@TableNameForLoop的第一行

  delete from @TableNameForLoop WHERE Name=@name;--删除上一行语句已取值的行。

  set @SqlStr='alter index all on ['+@name+ '] rebuild WITH (DATA_COMPRESSION=ROW);' --拼接优化语句与待优化物理表名

  BEGIN TRY exec (@SqlStr) END TRY BEGIN CATCH END CATCH --执行优化语句,如果有异常就直接吞掉,继续循环。

end

print '第3步执行时长:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@StepStartTime,getdate())))/60000,2)))+'分钟'; --统计单步执行时长

set @StepStartTime=getdate();


--第4步.压缩数据表以提升I/O性能(注意,这里并不是收缩数据库,而是压缩物理表)

Insert into @TableNameForLoop SELECT * from @TableName --重新初始化表变量:用于循环待优化的数据库物理表名清单_用于循环

WHILE EXISTS(SELECT top 100 percent name FROM @TableNameForLoop order by Name)

begin  

  select top 1 @name=name,@SqlStr='' FROM @TableNameForLoop order by Name --取值@TableNameForLoop的第一行

  delete from @TableNameForLoop WHERE Name=@name;--删除上一行语句已取值的行。

  set @SqlStr='alter table ['+@name+'] rebuild WITH (DATA_COMPRESSION=ROW);' --拼接优化语句与待优化物理表名

  BEGIN TRY exec (@SqlStr) END TRY BEGIN CATCH END CATCH --执行优化语句,如果有异常就直接吞掉,继续循环。

end

print '第4步执行时长:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@StepStartTime,getdate())))/60000,2)))+'分钟'; --统计单步执行时长


--*最后,一定记录本次执行以上四步优化的全程总耗时,做为后续设置SQL Server定时作业的重要参考。

print '全程总耗时:'+Convert(Nvarchar(50),Convert(decimal(18,2),ROUND(Convert(decimal(38,20),(datediff(ms,@GlobalStartTime,getdate())))/60000,2)))+'分钟。请记录此总耗时,做为设置定时作业的依据';

SET NOCOUNT OFF --恢复统计

/*特别说明:第1步、第2步,推荐每天、每周执行;第3步、第4步,推荐每月、每季执行,不推荐高频执行*/



SQL Server定时作业设置_每晚定时执行四步优化脚本

第1步,学会开启SQL Server 代理(别名SQL Server Agent服务,教程如下:

        http://m.coozhi.com/youxishuma/g4/62369.html?ivk_sa=1024320u

        注意,掌握上文技能后,还应进一步借鉴下文,将代理服务设置成自动:

        https://jingyan.baidu.com/article/da1091fbe69a32427849d6bc.html

        这样一来,即便是服务器重启过后,SQL Server代理服务仍然会随操作系统自动启动,以避免服务器重启过后定时执行作业失效的情况。


第2步,学会创建定时作业,每晚定时执行四步优化脚本:

        https://jingyan.baidu.com/article/0bc808fc7ea4d71bd585b968.html

        注:把文尾附件中的SQL脚本,一股脑全部丢入定时作业设置中就OK了,不用分四个定时作业的,有些同学误会了。



不重要的扩展阅读:

关于SQL Sever数据和索引的压缩:

    这特性在SQL Server2008被引入。压缩一个索引意味着将在一个页面中获得更多的关键字信息,这可以造成重大的性能改进,因为存储索引需要的页面和索引级别更少。

   但是,由于索引中的键值被压缩和解压缩,也将造成CPU和内存的开销;所以并不是所有场景都适用此方案,数据库管理员需要视具体情况而定,把握CPU、内存、硬盘I/O、硬盘空间这四者之间开销

    SQL Server提供两种数据压缩方式:行(Row)压缩和页压缩(Page),用于压缩表或索引数据。

    对于需要频繁更新的对象应该使用行压缩,对于只是执行读取操作的应该使用页压缩;在本例中,统一采用了行压缩,对于金蝶系统,也建议统一采用行压缩。

    数据压缩科普文:https://blog.csdn.net/worisaa/article/details/59121502


将SSD应用于SQL Sever数据库,从根本上提升I/O性能:

    稳妥方案一:往服务器加普通SSD(即固态硬盘),做为SQL Sever缓冲池扩展,不需要放弃原先的机械硬盘。

SQL Server2014新特性——用SSD作为缓冲池扩展:https://blog.csdn.net/yenange/article/details/79003342

    我更喜欢的方案二:给服务器加块大容量企业级SSD(比普通SSD贵2倍),将SQL Server 数据库文件迁移到企业级SSD中。同时,然后是利用SQL Sever定时作业,定期备份数据库文件到原机械硬盘,原机械硬盘变成了备份盘。

    总结:总之,SSD才是解决存储I/O性能瓶颈的终极武器。由于我本人,人菜胆又大,所以我,更倾向于推荐方案二。因为,只要做好了备份和防灾措施,就没什么好怕的。企业级SSD寿命和稳定性也是非常有保障的。



看疗效:

image.png

image.png

image.png


亲们,点赞、关注、收藏哦!

658fa305118cddcc5ed8cbb162df22a7_0100b081a0b952b34bacba3ace0c2e4b547c.png

315