--查询基本的信息
if object_id('tempdb..#a') is not null
drop table #a
select db_name(a.database_id) as database_name,object_name(a.object_id) as table_name,b.name as index_name,a.partition_number
,a.index_type_desc,a.index_depth,a.index_level
,alloc_unit_type_desc
,avg_fragmentation_in_percent,avg_fragment_size_in_pages,avg_page_space_used_in_percent
,page_count,record_count
,min_record_size_in_bytes,max_record_size_in_bytes,avg_record_size_in_bytes
,forwarded_record_count
into #a
from
sys.dm_db_index_physical_stats(db_id('usercenter'),null,null,null,'detailed')
a inner join sys.indexes b on a.object_id = b.object_id and a.index_id
= b.index_id
order by a.database_id,object_name(a.object_id),b.name,a.partition_number
--统计索引碎片情况
if object_id('tempdb..#b') is not null
drop table #b
select
database_name,table_name,index_name,index_type_desc,index_depth,index_level,count(partition_number)
as count_partition_number,max(avg_fragmentation_in_percent) as
max_avg_fragmentation_in_percent
into #b
from #a
where page_count > 0
group by database_name,table_name,index_name,index_type_desc,index_depth,index_level
order by database_name,table_name,index_name,index_type_desc,index_depth,index_level
--统计索引碎片情况-最大max_avg_fragmentation_in_percent
if object_id('tempdb..#c') is not null
drop table #c
select distinct table_name,index_name,max(max_avg_fragmentation_in_percent) as max_avg_fragmentation_in_percent
into #c
from #b
group by table_name,index_name
order by table_name,index_name
--给出索引优化建议
/*
功能 ALTER INDEX REORGANIZE ALTER INDEX REBUILD CREATE INDEX WITH DROP_EXISTING
可以更改索引定义。 否 否 是
可以设置或修改索引选项。 否 是 是
可以在单个事务中重新生成多个索引。 是 是 否
可以联机重新生成大部分索引类型，而不会阻止运行查询或更新。 总是 是 是
已分区索引可以重新分区。 否 否 是
可以将索引移动到另一个文件组中。 否 否 是
需要额外的临时磁盘空间。 是 是 是
重新生成聚集索引的操作将重新生成相关的非聚集索引。 否(除非指定关键字 ALL) 否(除非指定关键字 ALL) 否(除非更改索引定义)
可以重新生成强制 PRIMARY KEY 和 UNIQUE 约束的索引，而不用删除并重新创建这些约束。 是 是 是
可以重新生成单个索引分区。 是 是 否
具有DBCC等效语句 是 是（但总是脱机操作) 否
*/
select
case
when max_avg_fragmentation_in_percent <30 then 'ALTER INDEX ' + index_name + ' ON ' + table_name + ' REORGANIZE;'
else 'ALTER INDEX ' + index_name + ' ON ' + table_name + ' REBUILD WITH (ONLINE = ON);'
end
from #c
where index_name is not null and max_avg_fragmentation_in_percent > 10
order by table_name,index_name