立竿见影的数据库优化脚本以及定时自动优化，特别适用私有云&混合云
哈喽，小伙伴们，大家好啊！
昨日遇到一名资深同行提问，金蝶云星空（数据库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寿命和稳定性也是非常有保障的。
看疗效：
亲们，点赞、关注、收藏哦!
周少太棒啦！
果断收藏
非常棒！管用！
学习了，支持云星空企业版吗？
云星空标准版可以用这个语句吗？
大佬，MRP运算越来越慢可以用这个方法吗
数据库500G，昨天试下了，4步优化完10个小时。但是日志干到了900G，崩溃了
alter index all on [' + @name + '] rebuild WITH (DATA_COMPRESSION=ROW); alter index all on [' + @name + '] rebuild; 第一步跟第三步存在重复
相当于重建两次. 直接重建的时候压缩索引
ALTER INDEX PK_MyTable ON dbo.MyTable REBUILD WITH (DATA_COMPRESSION = ROW, ONLINE = OFF);
可以
赞
立竿见影的数据库优化脚本以及定时自动优化，特别适用私有云&混合云
本文2024-09-16 18:38:01发表“云星空知识”栏目。
本文链接：https://wenku.my7c.com/article/kingdee-k3cloud-23401.html
