当前位置：首页 > news >正文

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

news 来源：原创 2024/9/20 0:01:07

近期发现很多因为采用不同的优化表碎片和统计信息的脚本，因为不完整或者缺失导致没有达到优化的目的，引发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=0set @worktime=''  --不允许执行计划的工作时间
if len(ltrim(rtrim(@worktime)))>0beginset @worktime= cast(CONVERT (date, GETDATE()) as varchar)+' '+ltrim(rtrim(@worktime))set @worktimeflag=1end
elseset @worktimeflag=0set @noexectabs=''   ----排除不需要处理的表，如备份表等
SELECT Split.tabname.value('.', 'VARCHAR(100)') AS tabnameinto #noexectablesFROM (SELECT CAST('<x>' + REPLACE(@noexectabs, ',', '</x><x>') + '</x>' AS XML) AS xmldata) AS ACROSS 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=1if (@worktimeflag=1 and cast(@worktime as datetime)<=GETDATE()  )
beginset @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_descFROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL) IPSINNER JOIN sys.indexes SI ON SI.index_id = IPS.index_idINNER JOIN sys.objects SO ON SO.object_id = SI.object_id AND IPS.object_id = SO.object_idWHERE 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) >= 15THEN 'dbcc dbreindex('+object_name+') with no_infomsgs'WHEN max(avg_fragmentation_in_percent) >= 5THEN '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=@@ROWCOUNTset @message=N'索引优化：结束获取需要优化的表'+convert(varchar(30),getdate(),121)
RAISERROR(@message, 0, 1);if @icount=0beginset @message=N'索引优化：数据库：'+db_name()+N'不需要优化'RAISERROR(@message, 0, 1);returnend;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=1while @i<=@icount
beginselect @sql=execsql,@tbsize=objsize_mb,@tbname=object_name from  #indexinfo where id=@iif(@checklogsize=1)beginSELECT@driversize=CAST(CAST(available_bytes AS DECIMAL) / (1024 * 1024 ) AS BIGINT) FROM sys.master_files AS fCROSS 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_usageif @i=1beginset @message=N'索引优化：日志可用空间为(MB)：'+cast(@logunusedsize as varchar)+N';磁盘可用空间为(MB)：'+cast(@driversize as varchar) RAISERROR(@message, 0, 1);end;endif (@worktimeflag=1 and cast(@worktime as datetime)<=GETDATE()) beginset @message=N'索引优化：停止执行。因为当前系统时间为：'+convert(varchar(30),getdate(),121)+N'超过了开始工作时间范围'+@worktime+N'不允许执行优化计划'RAISERROR(@message, 0, 1);returnend  if (@tbsize>(@driversize+@logunusedsize) and @checklogsize=1) beginset @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);returnendelsebeginset @message=N'索引优化：执行第'+cast(@i as varchar)+'/'+cast(@icount as varchar)+N'个表('+cast(@tbsize as varchar)+'MB)：'+@sqlset @tmpbegintime=getdate()exec(@sql)set @message=@message+N' ：耗时(秒)：'+cast(DATEDIFF(ss,@tmpbegintime,getdate()) as varchar)RAISERROR(@message, 0, 1);endset @i=@i+1
endset @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