SQL SERVER 查询与整理索引碎片

重建索引

 1 use database_name;
 2 
 3 declare @tablename varchar(255)
 4 declare @sql nvarchar(500)
 5 declare @fillfactor int
 6 set @fillfactor = 80
 7 declare tablecursor cursor for
 8 select object_schema_name([object_id])+'.['+name+']' as tablename
 9 from sys.tables w
10 open tablecursor
11 fetch next from tablecursor into @tablename
12 while @@fetch_status = 0
13 begin
14 set @sql = 'alter index all on ' + @tablename + ' rebuild with (online=off,fillfactor=80);' --' rebuild with (fillfactor = ' + convert(varchar(3),@fillfactor) + ')'
15 exec (@sql)
16 fetch next from tablecursor into @tablename
17 end
18 close tablecursor
19 deallocate tablecursor
20 go

 查询索引碎片

use database_name;
select dbschemas.[name] as 'schema', 
dbtables.[name] as 'table', 
dbindexes.[name] as 'index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
from sys.dm_db_index_physical_stats (db_id(), null, null, null, null) as indexstats
inner join sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
inner join sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
inner join sys.indexes as dbindexes on dbindexes.[object_id] = indexstats.[object_id]
and indexstats.index_id = dbindexes.index_id
where indexstats.database_id = db_id() and dbindexes.[name] not like 'pk_%'
order by indexstats.avg_fragmentation_in_percent * page_count desc