SQLSERVER索引的维护

 --取得需要重新组织的索引信息
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;