SQL索引碎片整理脚本

复制代码 代码如下:DECLARE cur CURSOR FOR
SELECT [object_name]=s.name+’.’+OBJECT_NAME(A.object_id), B.name
FROM
sys.dm_db_index_physical_stats(DB_ID(‘AdventureWorks’),NULL,null,null,null)
AS A JOIN sys.indexes AS B ON A.[object_id]=B.[object_id] AND
A.[index_id]=B.[index_id] JOIN sys.objects AS o ON
A.[object_id]=o.[object_id] JOIN sys.schemas AS s ON
o.[schema_id]=s.[schema_id] WHERE A.[index_id]0 AND NOT EXISTS(
SELECT * FROM sys.xml_indexes WHERE A.[object_id]=[object_id]
AND A.[index_id]=[index_id] ); OPEN cur; DECLARE @objname
varchar(128),@indname varchar(128); DECLARE @sql nvarchar(4000); FETCH
NEXT FROM cur INTO @objname,@indname;
–重整所有索引,在这里先不管索引的碎片程度 WHILE @@FETCH_STATUS=0 BEGIN
SET @sql=’ALTER INDEX ‘+@indname+’ ON ‘+@objname+’ REBUILD’; EXEC(@sql);
FETCH NEXT FROM cur INTO @objname,@indname; END CLOSE cur; DEALLOCATE
cur;

在一个大型数据库中,数据的更改是非常频繁的。
而建立在这些数据上的索引也是需要经常去维护的。
否则这这些数据索引就起不到起应起的作用。甚至会成为数据库本身的负担。
我们就要定期的对数据库的索引进行维护
我在MSDN上发现了这个脚本不过其中有些小问题我已经修正
大家可以使用这个脚本对数据库的索引进行日常维护 复制代码 代码如下:SET NOCOUNT ON; DECLARE
@objectid int; DECLARE @indexid int; DECLARE @partitioncount bigint;
DECLARE @schemaname sysname; DECLARE @objectname sysname; DECLARE
@indexname sysname; DECLARE @partitionnum bigint; DECLARE @partitions
bigint; DECLARE @frag float; DECLARE @command varchar(8000); DECLARE
@dbId int; — ensure the temporary table does not exist IF EXISTS
(SELECT name FROM sys.objects WHERE name = ‘work_to_do’) DROP TABLE
work_to_do; — conditionally select from the function, converting
object and index IDs to names. set @dbId=DB_ID(); SELECT object_id AS
objectid, index_id AS indexid, partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag INTO work_to_do FROM
sys.dm_db_index_physical_stats (@dbId, 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 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); EXEC (@command); END; IF @frag = 30.0 BEGIN; SELECT
@command = ‘ALTER INDEX [‘ + @indexname +’] ON ‘ + @schemaname + ‘.[‘

原文发布时间为:2011-02-23 —— 来源于本人的百度文章 [由搬家工具导入]

  • @objectname + ‘] REBUILD’; IF @partitioncount 1 SELECT @command =
    @command + ‘ PARTITION=’ + CONVERT (CHAR, @partitionnum); EXEC
    (@command); END; PRINT ‘Executed ‘ + @command; FETCH NEXT FROM
    partitions INTO @objectid, @indexid, @partitionnum, @frag; END; — Close
    and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions; —
    drop the temporary table IF EXISTS (SELECT name FROM sys.objects WHERE
    name = ‘work_to_do’) DROP TABLE work_to_do; GO 这个脚本在运行时
    会建立一个表 work_to_do
    整理完毕后会自动删除这个表。如果大家不喜欢这样的话也可以用 一个
    临时表解决 .

reindex是比较好的选择,速度快,但是他不能在线操作
INDEXDEFRAG 比较慢,但是可以在线操作
rebuild建议在碎片较少时采用。

附上微软的重建索引脚本,从里面也可以看出微软根据碎片大小推荐的方式,不过这个要随每个不同的数据库而定。
图片 1— ensure a USE <databasename> statement has been executed first.
图片 2SET NOCOUNT ON;
图片 3DECLARE @objectid int;
图片 4DECLARE @indexid int;
图片 5DECLARE @partitioncount bigint;
图片 6DECLARE @schemaname sysname;
图片 7DECLARE @objectname sysname;
图片 8DECLARE @indexname sysname;
图片 9DECLARE @partitionnum bigint;
图片 10DECLARE @partitions bigint;
图片 11DECLARE @frag float;
图片 12DECLARE @command varchar(8000);
图片 13— ensure the temporary table does not exist
图片 14IF EXISTS (SELECT name FROM sys.objects WHERE name = ‘work_to_do’)
图片 15    DROP TABLE work_to_do;
图片 16— conditionally select from the function, converting object and index IDs to names.
图片 17SELECT
图片 18    object_id AS objectid,
图片 19    index_id AS indexid,
图片 20    partition_number AS partitionnum,
图片 21    avg_fragmentation_in_percent AS frag
图片 22INTO work_to_do
图片 23FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ‘LIMITED’)
图片 24WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
图片 25— Declare the cursor for the list of partitions to be processed.
图片 26DECLARE partitions CURSOR FOR SELECT * FROM work_to_do;
图片 27
图片 28— Open the cursor.
图片 29OPEN partitions;
图片 30
图片 31— Loop through the partitions.
图片 32FETCH NEXT
图片 33   FROM partitions
图片 34   INTO @objectid, @indexid, @partitionnum, @frag;
图片 35
图片 36WHILE @@FETCH_STATUS = 0
图片 37    BEGIN;
图片 38        SELECT @objectname = o.name, @schemaname = s.name
图片 39        FROM sys.objects AS o
图片 40        JOIN sys.schemas as s ON s.schema_id = o.schema_id
图片 41        WHERE o.object_id = @objectid;
图片 42
图片 43        SELECT @indexname = name 
图片 44        FROM sys.indexes
图片 45        WHERE  object_id = @objectid AND index_id = @indexid;
图片 46
图片 47        SELECT @partitioncount = count (*) 
图片 48        FROM sys.partitions
图片 49        WHERE object_id = @objectid AND index_id = @indexid;
图片 50
图片 51— 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding
图片 52IF @frag>5 AND
@frag <= 30.0
图片 53    BEGIN;
图片 54    SELECT @command = ‘ALTER INDEX ‘ + @indexname + ‘ ON ‘ + @schemaname + ‘.’ + @objectname + ‘ REORGANIZE’;
图片 55    IF @partitioncount > 1
图片 56        SELECT @command = @command + ‘ PARTITION=’ + CONVERT (CHAR, @partitionnum);
图片 57    EXEC (@command);
图片 58    END;
图片 59
图片 60IF @frag > 30.0
图片 61    BEGIN;
图片 62    SELECT @command = ‘ALTER INDEX ‘ + @indexname +’ ON ‘ + @schemaname + ‘.’ + @objectname + ‘ REBUILD’;
图片 63    IF @partitioncount > 1
图片 64        SELECT @command = @command + ‘ PARTITION=’ + CONVERT (CHAR, @partitionnum);
图片 65    EXEC (@command);
图片 66    END;
图片 67PRINT ‘Executed ‘ + @command;
图片 68
图片 69FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag;
图片 70END;
图片 71— Close and deallocate the cursor.
图片 72CLOSE partitions;
图片 73DEALLOCATE partitions;
图片 74
图片 75— drop the temporary table
图片 76IF EXISTS (SELECT name FROM sys.objects WHERE name = ‘work_to_do’)
图片 77    DROP TABLE work_to_do;
图片 78GO