sqlserver索引碎片整理

SELECT
object_name(dt.object_id) as 表名,
si.name as 索引名称 ,
dt.avg_fragmentation_in_percent as 外部碎片,
dt.avg_page_space_used_in_percent as 内部碎片
FROM
(
SELECT object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(db_id('efcoretest'),null,null,null,'DETAILED')
WHERE index_id <> 0
) AS dt
INNER JOIN sys.indexes si ON si.object_id=dt.object_id AND si.index_id=dt.index_id
where dt.avg_fragmentation_in_percent > 10 AND dt.avg_page_space_used_in_percent < 75
ORDER BY avg_fragmentation_in_percent DESC
1、当ExternalFragmentation > 10 时说明出现了外部索引碎片。
2、当InternalFragmentation < 75 时说明出现了内部索引碎片。
上面我们分析索引碎片以及判定索引碎片的规则，那么我们又该如何进行磁盘索引碎片整理呢？
如下有两种方式
1、重组磁盘索引碎片：
ALTER INDEX ALL ON TableName REORGANIZE
2、重建磁盘索引碎片：
ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON)
显示碎片
declare @table_id int
set @table_id=object_id('TB_GW_ProductPackInToGW')
--执行
dbcc showcontig(@table_id)
--以下是碎片整理
ALTER INDEX ALL ON TB_GW_CheckMaterialH REORGANIZE
ALTER INDEX ALL ON TB_GW_CheckMaterialH REBUILD WITH (FILLFACTOR=90,ONLINE=ON)
来源：https://blog.csdn.net/CXJ0062008/article/details/123572875
碎片产生原因与处理：https://blog.csdn.net/u011334954/article/details/104795285
https://blog.csdn.net/qq_29024215/article/details/90405369
DBCC DBREINDEX( table_name,index_name,fillfactor)弃用，使用alter index all on ....