SQL Server 索引碎片百分比高

reference

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15

• 查询数据库索引碎片超过30%的表，然后重建索引：

use DB_Name --改成当前库
SELECT dbschemas.[name] as 'Schema', 
dbtables.[name] as 'Table', 
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and dbindexes.[name] not like 'pk_%' and avg_fragmentation_in_percent > 30
ORDER BY indexstats.avg_fragmentation_in_percent * page_count desc

• 找到索引碎片多的表格之后重建索引：

ALTER INDEX Index_Name on Table_Name Rebuild;  --实际使用时将Index_Name替换成索引名称，Table_Name替换成索引所属表的名称

---

• 实际例子
  

-- 重建表Production.Produc索引
use AdventureWorks2012
GO
ALTER INDEX AK_Product_Name on Production.Product Rebuild
GO