34,576
社区成员
发帖
与我相关
我的任务
分享
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
--1、查找碎片
SELECT object_name(IPS.object_id) AS [表名],
SI.name AS [索引名称],
IPS.Index_type_desc AS [类型],
IPS.avg_fragmentation_in_percent,
IPS.avg_fragment_size_in_pages,
IPS.avg_page_space_used_in_percent,
IPS.record_count AS [行数],
IPS.ghost_record_count AS [幻影记录行数],
IPS.fragment_count,
IPS.avg_fragment_size_in_pages
FROM sys.dm_db_index_physical_stats(db_id(DB_NAME()), NULL, NULL, NULL , 'DETAILED') IPS
JOIN sys.tables T WITH (nolock) ON IPS.object_id = T.object_id
JOIN sys.indexes SI WITH (nolock) ON IPS.object_id = SI.object_id AND IPS.index_id = SI.index_id
WHERE T.is_ms_shipped = 0
order by IPS.avg_fragment_size_in_pages desc
--生成处理语句,仅针对选定的碎片量
DECLARE @页满度 TINYINT = 80 --可修改
DECLARE @索引页总数 TINYINT = 80 --可修改
SELECT 'alter index ' + i.name + ' on ' + OBJECT_NAME(i.id) + ' rebuild'
FROM Manufacture.sys.sysindexes i ,
Manufacture.sys.dm_db_index_physical_stats(DB_ID('Manufacture'), NULL,
NULL, NULL, 'SAMPLED') o
WHERE i.id = o.object_id
AND i.indid = o.index_id
AND o.page_count > @索引页总数
AND avg_page_space_used_in_percent < @页满度
AND i.name NOT LIKE '_WA%'