摘要由AI智能服务提供本文介绍了作者团队在解决金蝶云星空系统执行成本计算时卡住的性能问题后，总结出的一套数据库性能优化方法。方法包括四步全库优化脚本，旨在通过重建索引、更新统计信息、压缩索引和压缩数据表来提升系统性能。同时，提供了SQL Server定时作业的设置教程，并讨论了SQL Server数据压缩的适用场景和SSD在提升I/O性能方面的应用。
哈喽，小伙伴们，大家好啊！
昨日遇到一名资深同行提问，金蝶云星空（数据库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 代理服务，教程如下：
1.SQL Server 代理服务的别名又叫SQL Server Agent或SQLSERVERAGENT，三者同义。
2.将代理服务设置为启动，并设置为成随系统自动启动教程链接：
注：找到【SQL Server 代理】服务，设置成“自动”。这样一来，即便是服务器重启过后，SQL Server代理服务仍然会随操作系统自动启动，以避免服务器重启过后定时执行作业失效的情况。
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寿命和稳定性也是非常有保障的。
看疗效：
亲们，点赞、关注、收藏哦!
金蝶数据库全库优化四步脚本_作者周少斌.zip(1.91KB)
赞 375
打赏推荐阅读