在一个大型数据库中，数据的更改是非常频繁的。
而建立在这些数据上的索引也是需要经常去维护的。
否则这这些数据索引就起不到起应起的作用。甚至会成为数据库本身的负担。
我们就要定期的对数据库的索引进行维护 我在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 整理完毕后会自动删除这个表。如果大家不喜欢这样的话也可以用 一个 临时表解决 .