SQLSERVER索引的维护

sqlserver提升效率的有效方法是建立索引，但索引在增删改的过程中会产生碎片，碎片会影响性能，所以需要清理已达到提升性能的目的。

可以使用dm_db_index_physical_stats查询碎片状态，如果超过30%需要重新建立索引，如果在5%~30%之间，需要重新组织。

具体可以参考以下SQL：

 --取得需要重新组织的索引信息
SELECT
s.schema_id,
base.object_id,
base.index_id, 
base.avg_fragmentation_in_percent,
s.name AS schemaname,
o.name AS tablename,
i.name AS indexname
FROM sys.dm_db_index_physical_stats (DB_ID(N'MvcBlog'), NULL, NULL , NULL, NULL) AS base
INNER JOIN sys.objects AS o ON o.object_id = base.object_id
INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
INNER JOIN sys.indexes AS i ON i.object_id = base.object_id
 AND  i.index_id = base.index_id
WHERE 
 base.avg_fragmentation_in_percent between 5 and 30 --5%～30%
 AND 
 base.index_id > 0;

 --循环上面取得结果，执行重新组织索引语句
 ALTER INDEX [IndexName] ON [SchemaName].[TableName] REORGANIZE;
 

 --取得需要重新组织的索引信息
SELECT
s.schema_id,
base.object_id,
base.index_id,
base.avg_fragmentation_in_percent,
s.name AS schemaname,
o.name AS tablename,
i.name AS indexname
FROM sys.dm_db_index_physical_stats (DB_ID(N'MvcBlog'), NULL, NULL , NULL, NULL) AS base
INNER JOIN sys.objects AS o ON o.object_id = base.object_id
INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id
INNER JOIN sys.indexes AS i ON i.object_id = base.object_id
 AND  i.index_id = base.index_id
WHERE 
 base.avg_fragmentation_in_percent > 30 --30%以上
 AND 
 base.index_id > 0;

 --循环上面取得结果，执行重新建立索引语句
 ALTER INDEX [IndexName] ON [SchemaName].[TableName] REBUILD;

 

--重新建立表JNJA0001REC的所有索引	
ALTER INDEX ALL ON JNJA0001REC REBUILD;