查看SQL Server的索引碎片情况并进行整理

1，查看索引碎片占用状况

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, 
ind.name AS IndexName, indexstats.index_type_desc AS IndexType, 
indexstats.avg_fragmentation_in_percent 
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 > 5  ORDER BY 1,2 DESC 

---说明:为了获得最佳性能,avg_fragmentation_in_percent的值应尽可能接近零。

2，按照表批量重建索引

--USE TestDb
--GO
DECLARE @table_name VARCHAR(200)
--查询数据库里的所有表名
DECLARE cursor_reindex CURSOR FAST_FORWARD READ_ONLY FOR SELECT  distinct OBJECT_NAME(ind.OBJECT_ID) AS TableName 
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 > 5 
and ind.name is not null
 
--使用游标遍历表列表
OPEN cursor_reindex
FETCH NEXT FROM cursor_reindex INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
    --开始重建表索引
	DBCC DBREINDEX(@table_name)
	--DBCC DBREINDEX(@table_name, '', 90)
 
    FETCH NEXT FROM cursor_reindex INTO @table_name
END
CLOSE cursor_reindex
DEALLOCATE cursor_reindex

欢迎转载，请注明来自白菜卖故事>>本文地址：http://www.bcmgs.com/797

0 还没人赞过...