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

一、查看碎片占用情况

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 > 30 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

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

 

 

二、sql server中index的REBUILD和REORGANIZE

  

--1.准备实验数据
select * into Employee from AdventureWorks2008R2.HumanResources.Employee;

--2.查看使用空间：Employee    290            72 KB    56 KB    8 KB    8 KB
sp_spaceused Employee

--3.创建聚集索引
create clustered index IX_BusinessEntityID on Employee(BusinessEntityID);

--4.查看使用空间：Employee    290            80 KB    56 KB    16 KB    8 KB
sp_spaceused Employee

--5.索引重建，清除fragment，并设定fillfactor为60
ALTER INDEX ALL ON Employee
REBUILD WITH (FILLFACTOR = 60, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

--6.查看使用空间：Employee    290            144 KB    88 KB    16 KB    40 KB
sp_spaceused Employee

---不锁表，索引重建

ALTER INDEX ALL ON Tab_Dealer_InteractiveMessage
REORGANIZE
GO

 

 

---生成索引重建sql

SELECT distinct

 concat('ALTER INDEX ALL ON ', OBJECT_NAME(ind.OBJECT_ID),' REORGANIZE - GO-') AS ReIndex
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 > 30
   

---查询索引碎片sql
SELECT

 concat('ALTER INDEX ALL ON ', OBJECT_NAME(ind.OBJECT_ID),' REORGANIZE - GO-') AS ReIndex,

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 > 30
 
ORDER BY indexstats.avg_fragmentation_in_percent DESC

 

--DBCC SHOWCONTIG('Tab_Dealer_ProductPrice');