金蝶CLOUD索引碎片超过80的表重建索引

最新推荐文章于 2022-08-17 10:39:38 发布

weixin_34050005

最新推荐文章于 2022-08-17 10:39:38 发布

阅读量233

点赞数

CC 4.0 BY-SA版权

原文链接：http://blog.51cto.com/kanshan/2346221

本文详细介绍了一种使用SQL Server的动态管理视图dm_db_index_physical_stats进行索引碎片检查的方法，并通过游标和动态SQL执行DBCC DBREINDEX语句来自动优化那些碎片率超过80%的索引，特别关注排除临时表和特定前缀的表。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DECLARE @DBCCString NVARCHAR(1000)
DECLARE @TableName VARCHAR(100)
DECLARE Cur_Index CURSOR FOR
--SELECT Name AS TblName FROM sysobjects WHERE xType='U' and Name like 'T_HS%' ORDER BY TblName
-- SELECT
-- TblName = obj.name
-- FROM sys.objects obj
-- JOIN sys.indexes idx on obj.object_id = idx.object_id
-- JOIN sys.partitions prt on obj.object_id = prt.object_id
-- JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id
-- WHERE
-- obj.type = 'U' AND idx.index_id IN (0, 1) --and obj.name like 'TBD%'
-- GROUP BY obj.name, prt.rows having prt.rows>317 and prt.rows <1000
--ORDER BY prt.rows

SELECT top 100 OBJECT_NAME(ind.OBJECT_ID) AS TblName
--, ind.name AS IndexName, indexstats.index_type_desc AS IndexType,
--indexstats.avg_fragmentation_in_percent
----into #indextmp
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats
INNER JOIN sys.indexes ind
ON ind.object_id = indexstats.object_id
AND ind.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent >80 and ind.name is not null and OBJECT_NAME(ind.OBJECT_ID) not like 'TMP%'
ORDER BY indexstats.avg_fragmentation_in_percent DESC

FOR READ ONLY
OPEN Cur_Index
FETCH NEXT FROM Cur_Index INTO @TableName
WHILE @@FETCH_STATUS=0
BEGIN
SET @DBCCString = 'DBCC DBREINDEX(@TblName,'''')WITH NO_INFOMSGS'

EXEC SP_EXECUTESQL @DBCCString,N'@TblName VARCHAR(100)',@TableName
PRINT '重建表' + @TableName +'的索引........OK!'
FETCH NEXT FROM Cur_Index INTO @TableName
END
CLOSE Cur_Index
DEALLOCATE Cur_Index

PRINT '操作完成！'

--select count(*) from TMPC494F5680CD811E9A168005056B

--exec sp_updatestats --重建计数器

转载于:https://blog.51cto.com/kanshan/2346221