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