索引维护 联机重建或组织索引
CREATE procedure [pr_auto_indexdefrag_online]
as
begin
set nocount on
declare @Db_name nvarchar(256)
,@SchemaName nvarchar(256)
,@TableName Nvarchar(256)
,@IndexName Nvarchar(512)
,@PctFrag decimal
,@Defrag nvarchar(max)
if exists(select 1 from sys.objects where object_id =object_id(N'#tmp')) Drop table #tmp;
if exists(select 1 from sys.objects where object_id =object_id(N'#tmp_sub')) Drop table #tmp_sub;
create table #tmp_sub(database_id int,dbname nvarchar(32),tablename nvarchar(128),index_type_desc nvarchar(128))
create table #tmp(database_id int,dbname nvarchar(256),tablename nvarchar(256),indexname nvarchar(256),type_desc nvarchar(128),schemaname nvarchar(256),avgfragment decimal)
------找出 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max)、xml 或大型 CLR 类型的列
exec sp_MSforeachdb 'insert into #tmp_sub(database_id,dbname,tablename,index_type_desc)
select distinct c.database_id,''?'' dbname,b.name,''CLUSTERED''
from
sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''SAMPLED'') as a
join sys.tables as b on a.object_id=b.object_id
join sys.databases as c on a.database_id=c.database_id
join sys.all_columns d on d.object_id =a.object_id
join sys.sysobjects e on d.object_id=e.id and e.xtype=''U''
join sys.types f on d.user_type_id=f.user_type_id
where b.type_desc=''USER_TABLE'' and b.is_ms_shipped=0 and (d.max_length =-1
OR (f.name in (''image'',''text'',''ntext'',''xml'',''varbinary'',''binary'')))'
---- d.max_length =-1 ( varchar(max) 如果表中有某列是这些类型，则重建索引会报错
----找出 所有库中的索引
exec sp_MSforeachdb 'insert into #tmp(database_id,dbname,tablename,indexname,type_desc,schemaname,avgfragment)
select distinct d.database_id,''?'' dbname,c.name,b.name,b.type_desc,e.name,a.avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''SAMPLED'') as a
join sys.indexes as b on a.object_id=b.object_id and a.index_id=b.index_id
join sys.tables as c on a.object_id=c.object_id
join sys.databases as d on a.database_id=d.database_id
join sys.schemas as e on c.schema_id=e.schema_id
join sys.sysobjects f on c.object_id=f.id
join sys.all_columns g on f.id=g.object_id
join sys.types h on g.user_type_id=h.user_type_id
where a.avg_fragmentation_in_percent >20
and c.type=''U'' and f.xtype=''U''
and c.is_ms_shipped=0 '
declare frg_cur cursor for
select dbname,tablename,indexname,
schemaname,avgfragment
from #tmp
where not exists (select 1 from #tmp_sub b where database_id=b.database_id and tablename=b.tablename and type_desc=b.index_type_desc)
open frg_cur
fetch next from frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag
while @@FETCH_STATUS=0
begin
if @PctFrag between 20.0 and 40.0
begin
set @Defrag=N' ALTER INDEX '+@IndexName+' ON'+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REORGANIZE'--重新组织索引页不删除索引
EXEC SP_EXECUTESQL @Defrag
end
else if @PctFrag>40.0
begin
SET @Defrag=N' ALTER INDEX '+@IndexName+' ON'+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REBUILD WITH (ONLINE = ON )'--联机重建索引。即不锁定表重新创建索引
EXEC SP_EXECUTESQL @Defrag
end
fetch next from frg_cur into @Db_name,@TableName,@IndexName,@SchemaName,@PctFrag
end
close frg_cur
deallocate frg_cur
end
set nocount off
>> Home
