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。 爱情是两个人的”饰“！欢迎光临我的时尚饰品店：http://shop36465575.taobao.com 也许有意想不到的收获！！！转载于:https://www.cnblogs.com/lfzwenzhu/archive/2009/07/03/1516022.html
