一般使用 dbcc dbreindex(@TABLENAME,”,90) 来重建表索引，但数据库中表较多需要批量全部重建，有的碎片程度不高或数据行数不多，不需要重建时，可以参考使用如下批处理。
DBA可以将#Tables_Reindex建成实体表，记录重建时间，重建前后碎片比率等，并建立每周执行计划。实体表方式可以按控制索引重建频率，查看重建索引执行情况等。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SELECT db_name() as db,a.object_id,a.name,0 as reindexdays,getdate() reindexdate,b.rows as lastrows ,8*b.reserved/1024 reserved,rtrim(8*b.dpages/1024) used,8*(b.reserved-b.dpages)/1024 unused,8*b.dpages/1024-b.rows/1024*b.minlen/1024 free,'Mb'unit ,(select max(avg_fragmentation_in_percent) from sys.dm_db_index_physical_stats(DB_ID(),a.[object_id] ,NULL,NULL,NULL) where index_type_desc='CLUSTERED INDEX')as lastfragmentation --按簇集索引随便比率判断 into #Tables_Reindex FROM SYS.objects a inner join SYS.sysindexes b on a.object_id=b.id and b.indid<=1 WHERE a.TYPE = 'U' and a.name not like 'temp%' and len(a.name)<50 ORDER BY b.rows DESC IF(CURSOR_STATUS('global','TABLE_CURSOR')<>'-3') BEGIN CLOSE TABLE_CURSOR DEALLOCATE TABLE_CURSOR END DECLARE @TABLENAME NVARCHAR(50) DECLARE TABLE_CURSOR CURSOR FOR select name,case when lastrows>10000 and used>30 then 0 else 1 end as issmall from #Tables_Reindex where db=DB_NAME() and lastfragmentation>10 --索引碎片比率大于10% and ((lastrows<10000 or used<30) and datediff(day,reindexdate,getdate())>30 ) --行数小于1万或空间小于30M的小表每30天以上才重建一次 order by lastfragmentation desc OPEN TABLE_CURSOR FETCH NEXT FROM TABLE_CURSOR INTO @TABLENAME,@ISSMALL WHILE (@@FETCH_STATUS = 0 --and datepart(hour,GetDate())<7 --定时任务时启用此行，避免工作时间执行影响使用 ) BEGIN IF @ISSMALL=1 BEGIN dbcc dbreindex(@TABLENAME,'',100)--小表填充因子100% END ELSE BEGIN dbcc dbreindex(@TABLENAME,'',90)--填充因子90% END select @TABLENAME,'索引已重建' FETCH NEXT FROM TABLE_CURSOR INTO @TABLENAME,@ISSMALL END CLOSE TABLE_CURSOR DEALLOCATE TABLE_CURSOR drop table #Tables_Reindex |
© 2019, ITJOY.NET. 版权所有. 如未注明，均为原创，转载请注明出处。