SQL Server 2005：索引碎片整理脚本

SQL Server 2005：索引碎片整理脚本。
首先说明：该 SQL 索引碎片整理脚本，是从 SQL Server 2005 联机帮助上摘录下来，并且稍加整理而成的。

该 SQL 索引碎片整理脚本，首先从 SQL Server 2005 系统管理视图 sys.dm_db_index_physical_stats 中，找出索引碎片程度大于 10% 的索引，然后根据索引碎片程度，分别来采取不同的方法来整理索引碎片。小于 30% 的使用 alter index reorganize；大于等于 30% 的使用 alter index rebuild。其中 reorganize 相当于 dbcc indexdefrag()；rebuild 相当于 dbcc dbreindex()。

SQL 碎片整理后，索引数据页在数据库文件中排列的更紧凑，可以大幅提高一些 SQL 查询的效率。DBA 可以每周进行一次碎片整理。另外要注意的是，不要在收缩数据库（dbcc shrinkfile, dbcc shrinkdatabase）前整理索引碎片。

Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes
The following example automatically reorganizes or rebuilds all partitions in a database that have an average fragmentation over 10 percent. Executing this query requires the [view database state] permission.

--------------------------------------------------------------------------------
-- ensure a USE  statement has been executed first.
--------------------------------------------------------------------------------

set nocount on

declare @objectid         int
       ,@indexid          int
       ,@partitioncount   bigint
       ,@schemaname       sysname
       ,@objectname       sysname
       ,@indexname        sysname
       ,@partitionnum     bigint
       ,@partitions       bigint
       ,@frag             float
       ,@command          varchar(1000)

select objectid     = object_id
      ,indexid      = index_id
      ,partitionnum = partition_number
      ,frag         = avg_fragmentation_in_percent
  into #work_to_do
  from sys.dm_db_index_physical_stats(db_id(), null, null , null, 'LIMITED')
where avg_fragmentation_in_percent > 10.0
   and index_id > 0

-- declare the cursor for the list of partitions to be processed.
declare partitions cursor for
   select * from #work_to_do

-- Open the cursor.
open partitions

-- Loop through the partitions.
fetch next from partitions into @objectid, @indexid, @partitionnum, @frag

while @@fetch_status = 0 begin
   select @objectname = o.name, @schemaname = s.name
     from sys.objects as o
            inner join sys.schemas as s
      on s.schema_id = o.schema_id
   where o.object_id = @objectid

   select @indexname = name
     from sys.indexes
    where object_id = @objectid
      and index_id = @indexid

   select @partitioncount = count (*)
    from sys.partitions
   where object_id = @objectid
     and index_id = @indexid

   -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
   if @frag < 30.0 begin
       select @command = 'alter index ' + @indexname + ' on '
                       + @schemaname + '.' + @objectname + ' reorganize'

       if @partitioncount > 1
           select @command = @command + ' partition=' + convert(char, @partitionnum)
   end

   if @frag >= 30.0 begin
       select @command = 'alter index ' + @indexname +' on '
                       + @schemaname + '.' + @objectname + ' rebuild'

       if @partitioncount > 1
           select @command = @command + ' partition=' + convert(char, @partitionnum)
   end

   -- exec (@command)
   print 'Executed: ' + @command

   fetch next from partitions into @objectid, @indexid, @partitionnum, @frag
end


-- free resource
close partitions
deallocate partitions
drop table #work_to_do
《SQL Server 2005：索引碎片整理脚本》代码摘自 SQL Server 2005 Books Online。

 

查询数据库中所有表的索引密度和碎片信息，以便为索引的重建和整理提供依据，也可以参考DBCC SHOWCONTIG，通常FRAGMENTATIOIN在30%以上建议重建，否则建议整理

SELECT i.name                           AS indexname,
         o.name                           AS tablename,
         s.name                           AS schemaname,
         f.index_type_desc                AS indextype,
         f.avg_page_space_used_in_percent AS indexdensity,
         f.avg_fragmentation_in_percent   AS indexfragmentation,
         f.page_count                     AS pages
    FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') f
         INNER JOIN sys.objects o ON o.object_id = f.object_id
         INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
         INNER JOIN sys.indexes i ON f.object_id = i.object_id AND f.index_id = i.index_id
   WHERE page_count > 50
     AND f.index_id > 0
ORDER BY o.name, i.index_id

SELECT i.name AS indexname, o.name AS tablename, s.name AS schemaname, f.index_type_desc AS indextype, f.avg_page_space_used_in_percent AS indexdensity, f.avg_fragmentation_in_percent AS indexfragmentation, f.page_count AS pages FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') f INNER JOIN sys.objects o ON o.object_id = f.object_id INNER JOIN sys.schemas s ON o.schema_id = s.schema_id INNER JOIN sys.indexes i ON f.object_id = i.object_id AND f.index_id = i.index_id WHERE page_count > 50 AND f.index_id > 0 ORDER BY o.name, i.index_id

 

LIMITED | SAMPLED | DETAILED | NULL | DEFAULT
     这些模式影响了如何收集碎片数据。LIMITED模式扫描堆所有的页，但对于索引，则只扫描叶级上面的父级别页。SAMPLED收集在堆或索引中1%采样率的数据

。DETAILED模式扫描所有页（堆或索引）。DETAILED是执行最慢的，但也是最精确的选项。指定NULL或DEFAULT的效果与LIMITED模式的相同。