MsSql教程网-Sql Server 数据库索引整理语句,自动整理数据库索引

在一个大型数据库中,数据的更改是非常频繁的。

而建立在这些数据上的索引也是需要经常去维护的。

否则这这些数据索引就起不到起应起的作用。甚至会成为数据库本身的负担。

我们就要定期的对数据库的索引进行维护 我在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 + ‘.[‘ + @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 整理完毕后会自动删除这个表。如果大家不喜欢这样的话也可以用 一个 临时表解决 .

共享资源网提供最优质的资源集合。
共享资源网 » MsSql教程网-Sql Server 数据库索引整理语句,自动整理数据库索引

共享资源网提供最优质的资源集合

充值中心 开启会员