SQL SERVER日常表碎片和统计信息优化脚本

栏目：云星空知识作者：金蝶来源：金蝶云社区发布：2024-09-23浏览：1

近期发现很多因为采用不同的优化表碎片和统计信息的脚本，因为不完整或者缺失导致没有达到优化的目的，引发SQL语句执行低效甚至整体卡慢问题。

为了保证每次优化的效果，需要采用金蝶云星空企业版本要求的SQL优化脚本(简单手工运行版本)：

declare @sql as varchar(max)

set @sql='' select @sql=@sql+'dbcc dbreindex(['+name+']);'+char(13)+char(10) from sys.tables where name not like 'tmp%' and name not like 'z%'

exec(@sql)

由于索引优化过程中，会导致ldf文件快速增长问题，为了避免这个问题，下面的SQL增加了对日志文件可用空间大小，以及磁盘可用空间大小的判断，当优化的表需要的空间高于上面两个可用空间和时将终止执行，避免由于空间增长导致数据库出现问题。同时增加执行过程中的记录和每一个表执行的耗时情况。

考虑某些场景下执行时间可能过长问题，某些表如备份表，日志表等可以不做处理，增加两个参数：

@worktime 工作时间：类型为字符串，格式为24小时制，如早上8点30分，那么设置为'08:30' ，当执行时发现时间为当天的8点30将停止后面的执行

@noexectabs 排除的表：将不需要优化的表，登记到这里，格式为 't1,t2',表示排除t1和t2表，否则保留原始脚本不变

更新日志：

2024.7.18 考虑trim在2019后才支持，修改为ltrim和rtrim方式

脚本支持所有SQL Server版本

2024.7.29 修改排除表的BUG

------脚本如下（选择需要优化的数据库）：

if object_id('tempdb..#indexinfo') <>0  drop table #indexinfo
if object_id('tempdb..#noexectables') <>0  drop table #noexectables
go
set nocount on
declare @i int 
declare @icount int
declare @sql as varchar(max)
declare @tbsize as decimal(19,3)
declare @driversize as int
declare @logunusedsize int=0
declare @message varchar(300)
declare @tbname sysname
declare @begintime datetime
declare @tmpbegintime datetime
declare @worktime varchar(30)
declare @worktimeflag bit
declare @noexectabs varchar(max)
declare @checklogsize bit=0


set @worktime=''  --不允许执行计划的工作时间
if len(ltrim(rtrim(@worktime)))>0
  begin
	set @worktime= cast(CONVERT (date, GETDATE()) as varchar)+' '+ltrim(rtrim(@worktime))
	set @worktimeflag=1
  end
else
  set @worktimeflag=0

set @noexectabs=''   ----排除不需要处理的表，如备份表等
SELECT Split.tabname.value('.', 'VARCHAR(100)') AS tabname
	into #noexectables
	FROM (
		SELECT CAST('<x>' + REPLACE(@noexectabs, ',', '</x><x>') + '</x>' AS XML) AS xmldata
	) AS A
	CROSS APPLY xmlData.nodes('x') AS Split(tabname)
--判断sql server版本是否高于2008 r2
if (cast(serverproperty('ProductMajorVersion') as int)>10 or (cast(serverproperty('ProductMajorVersion') as int)=10 and cast(serverproperty('ProductMinorVersion') as int)>=50)) 
	set @checklogsize=1

if (@worktimeflag=1 and cast(@worktime as datetime)<=GETDATE()  )
begin
	set @message=N'索引优化：停止执行。因为当前系统时间为：'+convert(varchar(30),getdate(),121)+N'超过了开始工作时间范围'+@worktime+N'不允许执行优化计划'
		RAISERROR(@message, 0, 1);
		return
end;    

set @message=N'索引优化：开始获取需要优化的表'+convert(varchar(30),getdate(),121)
RAISERROR(@message, 0, 1);

WITH t
AS (
	SELECT SO.NAME AS object_name, SI.NAME AS index_name, IPS.avg_fragmentation_in_percent, (page_count * 8.0 / 1024.0) AS size_in_mB, si.type_desc
	FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL) IPS
	INNER JOIN sys.indexes SI ON SI.index_id = IPS.index_id
	INNER JOIN sys.objects SO ON SO.object_id = SI.object_id AND IPS.object_id = SO.object_id
	WHERE alloc_unit_type_desc = 'IN_ROW_DATA' AND index_level = 0 AND SI.NAME IS NOT NULL AND SO.is_ms_shipped = 0 
	AND so.NAME NOT LIKE 'tmp%' AND so.NAME NOT LIKE 'z%' AND so.NAME NOT LIKE 'gle%' 
	and  not exists(select 1 from #noexectables m where so.name=m.tabname)
	),m as(
SELECT 
		object_name, max(avg_fragmentation_in_percent) fragmentpercent,CASE 
		WHEN max(avg_fragmentation_in_percent) >= 15
			THEN 'dbcc dbreindex('+object_name+') with no_infomsgs'
		WHEN max(avg_fragmentation_in_percent) >= 5
			THEN 'dbcc indexdefrag(0,'+object_name+') with no_infomsgs'
		ELSE ''
		END execsql, sum(size_in_mB) objsize_mb
FROM t
where avg_fragmentation_in_percent>0
GROUP BY object_name)
select IDENTITY(int,1,1)id,* into #indexinfo from m where m.execsql<>'' order by 3 desc
set @icount=@@ROWCOUNT

set @message=N'索引优化：结束获取需要优化的表'+convert(varchar(30),getdate(),121)
RAISERROR(@message, 0, 1);

if @icount=0
	begin
		set @message=N'索引优化：数据库：'+db_name()+N'不需要优化'
		RAISERROR(@message, 0, 1);
		return
	end;


set @begintime=getdate()
set @message=N'索引优化：数据库：'+db_name()+N'，开始时间： '+convert(varchar(30),@begintime,121)+N' 总共需要执行:'+cast(@icount as varchar)+N' 条语句'
RAISERROR(@message, 0, 1);

set @i=1

while @i<=@icount
begin

   select @sql=execsql,@tbsize=objsize_mb,@tbname=object_name from  #indexinfo where id=@i
   
   if(@checklogsize=1)
   begin
	   SELECT
		@driversize=CAST(CAST(available_bytes AS DECIMAL) / (1024 * 1024 ) AS BIGINT) 
		FROM sys.master_files AS f
		CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
		WHERE f.database_id = DB_ID()
		AND f.type_desc = 'LOG';

		select @logunusedsize=(total_log_size_in_bytes-used_log_space_in_bytes)/1024/1024 from sys.dm_db_log_space_usage

		if @i=1
		begin
			set @message=N'索引优化：日志可用空间为(MB)：'+cast(@logunusedsize as varchar)+N';磁盘可用空间为(MB)：'+cast(@driversize as varchar) 
			RAISERROR(@message, 0, 1);
		end;
	end

	if (@worktimeflag=1 and cast(@worktime as datetime)<=GETDATE()) 
		begin
			set @message=N'索引优化：停止执行。因为当前系统时间为：'+convert(varchar(30),getdate(),121)+N'超过了开始工作时间范围'+@worktime+N'不允许执行优化计划'
			RAISERROR(@message, 0, 1);
			return
		end  

	if (@tbsize>(@driversize+@logunusedsize) and @checklogsize=1) 
	begin
		set @message=N'索引优化：磁盘可用空间和日志可用空间不足，执行终止。原因：表:'+@tbname+ N'总需空间大小(MB)：'+cast(@tbsize as varchar)+N';日志可用空间为(MB)：'+cast(@logunusedsize as varchar)+N';磁盘可用空间为(MB)：'+cast(@driversize as varchar) 
		RAISERROR(@message, 16, 1);
		return
	end
	else
	begin
		set @message=N'索引优化：执行第'+cast(@i as varchar)+'/'+cast(@icount as varchar)+N'个表('+cast(@tbsize as varchar)+'MB)：'+@sql
		set @tmpbegintime=getdate()
		exec(@sql)
		set @message=@message+N' ：耗时(秒)：'+cast(DATEDIFF(ss,@tmpbegintime,getdate()) as varchar)
		RAISERROR(@message, 0, 1);
	end

   set @i=@i+1
end
	set @message=N'索引优化：数据库：'+db_name()+N'，结束时间： '+convert(varchar(30),getdate(),121)+N' 总耗时(秒)：'+cast(DATEDIFF(ss,@begintime,getdate()) as varchar)
	RAISERROR(@message, 0, 1);

----执行结果如下为：

索引优化：开始获取需要优化的表2024-07-18 15:03:15.540

索引优化：结束获取需要优化的表2024-07-18 15:03:21.857

索引优化：数据库：AIS_K3CLOUD_*****，开始时间： 2024-07-18 15:03:21.857 总共需要执行:9298 条语句

索引优化：日志可用空间为(MB)：24247;磁盘可用空间为(MB)：3379198

索引优化：执行第1/9298个表(0.516)：dbcc dbreindex(T_IV_SALESIC_O) with no_infomsgs ：耗时(秒)：1

索引优化：执行第2/9298个表(1.930)：dbcc dbreindex(T_ENG_BOMEXPANDRESULT) with no_infomsgs ：耗时(秒)：0

索引优化：执行第3/9298个表(0.016)：dbcc dbreindex(T_ENG_BOMCHILD_L) with no_infomsgs ：耗时(秒)：0