查看索引碎片

--查看索引碎片:

USE dbname
SELECT schema_name(T.schema_id) AS Schema_Name,T.Name AS Table_Name,I.name AS Index_Name,
I.type AS Index_Type,D.avg_fragmentation_in_percent AS avg_fragmentation_in_percent,page_count
into #t_index
FROM sys.dm_db_index_physical_stats(DB_id(),null, null, null, null) AS D
INNER JOIN sys.indexes AS I WITH(NOLOCK) ON D.index_id=I.index_id AND D.object_id=I.object_id
INNER JOIN sys.tables AS T WITH(NOLOCK) ON T.object_id=D.object_id
WHERE I.type>0 AND T.is_ms_shipped=0 AND D.avg_fragmentation_in_percent>=30
and page_count>=1000
order by D.avg_fragmentation_in_percent desc

--查看索引碎片，并生成重建索引代码

USE dbname
SELECT schema_name(T.schema_id) AS Schema_Name,T.Name AS Table_Name,I.name AS Index_Name,
I.type AS Index_Type,D.avg_fragmentation_in_percent AS avg_fragmentation_in_percent,page_count
into #t_index
FROM sys.dm_db_index_physical_stats(DB_id(),null, null, null, null) AS D
INNER JOIN sys.indexes AS I WITH(NOLOCK) ON D.index_id=I.index_id AND D.object_id=I.object_id
INNER JOIN sys.tables AS T WITH(NOLOCK) ON T.object_id=D.object_id
WHERE I.type>0 AND T.is_ms_shipped=0 AND D.avg_fragmentation_in_percent>=30
and page_count>=1000
order by D.avg_fragmentation_in_percent desc

SELECT 'ALTER INDEX ' + index_name + ' ON ' + schema_name + '.'
+ table_name + ' REBUILD WITH (ONLINE = ON)'
FROM #t_index

--ALTER INDEX ix_userid ON dbo.user_service REBUILD WITH (ONLINE = ON)

drop table #t_index