SQL Server索引重建和重新组织脚本
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
ALTER procedure [dbo].[IndexMaintain]
as
SET NOCOUNT on
BEGIN TRY
declare @EXCEPTION VARCHAR(MAX)
declare @MailSubject NVARCHAR(255)
--获取index被整理后的碎片比例
set @Sql=N'USE '+@DBName+';select @avg_fragmentation_in_percent_new_temp=s.avg_fragmentation_in_percent,@avg_page_space_used_in_percent_new_temp= s.avg_page_space_used_in_percent
order by TableName,IndexName '
--定义CURSOR遍历临时表#Frag,根据Fragment大小采取不同的方案维护index.
declare cList CURSOR for
DBname NVARCHAR(255),
TableName NVARCHAR(255),
SchemaName NVARCHAR(255),
IndexName NVARCHAR(255),
declare @avg_page_space_used_in_percent_old DECIMAL(18,3)
declare @avg_fragmentation_in_percent_new DECIMAL(18,3)
declare @avg_page_space_used_in_percent_new DECIMAL(18,3)
exec sp_executesql @Defrag
--获取index被整理后的碎片比例
set @Sql=N'USE '+@DBName+'; select @avg_fragmentation_in_percent_new_temp=s.avg_fragmentation_in_percent,@avg_page_space_used_in_percent_new_temp= s.avg_page_space_used_in_percent
end
--Fragment大于40.0 ,使用 Alter INDEX rebuild整理碎片
else if @avg_fragmentation_in_percent_old >40.0 AND @DBName <>'[TOPCOA]'
begin
--整理碎片
set @Defrag=N'Alter INDEX '+''+@IndexName+' on '+@DBName+'.'+@SchemaName+'.'+@TableName+' rebuild'
exec sp_executesql @Defrag
--write log
insert [dbo].IndexDefrag values(@DBName,@TableName,@SchemaName,@IndexName,getdate(),@avg_fragmentation_in_percent_old,@avg_page_space_used_in_percent_old,@avg_fragmentation_in_percent_new,@avg_page_space_used_in_percent_new,'0')
GO
-- =============================================
-- Author
-- Create date: 2012/01/17
-- Description: IndexMaintain
-- ==============================================
declare @Defrag NVARCHAR(max)
declare @Sql NVARCHAR(max)
declare @ParmDefinition nvarchar(500)
set @EXCEPTION=''
--删除#Frag
where i.object_id=object_id('''+@TableName+''''+')and ='''+@IndexName+''''
set @ParmDefinition=N'@avg_fragmentation_in_percent_new_temp DECIMAL(18,3) output,@avg_page_space_used_in_percent_new_temp DECIMAL(18,3) output'
while @@FETCH_STATUS=0
begin
set @TableName ='['+ @TableName +']'
--Fragment between 20.0 and 40.0 ,使用 Alter INDEX reorganize整理碎片
USE [msdbStoredProcedure [dbo].[IndexMaintain] Script Date: 09/14/2012 17:59:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
from '+@DBName+'.sys.indexes i
inner join '+@DBName+'.sys.dm_db_index_physical_stats(db_id(replace(replace('''+@DBName+''',''['',''''),'']'','''')), object_id('''+@TableName+''''+'),null,null,''sampled'') as s on i.index_id=s.index_id
join [?].sys.tables t on i.Object_id=t.Object_ID
join [?].sys.schemas sc on t.schema_id=sc.SCHEMA_ID
if exists(select * from sys.objects where object_id=object_id(N'#Frag'))
drop table #Frag
--定义临时表#Frag保存index Fragment
create table #Frag(
AvgFragment DECIMAL(18,3),
avg_page_space_used DECIMAL(18,3)
)
--遍历DB中所有table上的index,并将Fragment保存到临时表#Frag中.
if @avg_fragmentation_in_percent_old between 20.0 and 40.0 AND @DBName <>'[TOPCOA]'
begin
--整理碎片
set @Defrag=N'Alter INDEX '+''+@IndexName+' on '+@DBName+'.'+@SchemaName+'.'+@TableName+' reorganize'
exec sp_MSforeachdb @command1= 'insert into #Frag(DBname, TableName,SchemaName,IndexName,AvgFragment,avg_page_space_used)
select ''[?]'' AS DBName, AS TableName, AS SchemaName, AS IndexName, s.avg_fragmentation_in_percent, s.avg_page_space_used_in_percent
where s.avg_fragmentation_in_percent >20 and t.type=''U'' and s.page_count>8 and i.allow_page_locks=1 and i.allow_row_locks=1
exec sp_executesql @Sql,@ParmDefinition ,@avg_fragmentation_in_percent_new_temp=@avg_fragmentation_in_percent_new output, @avg_page_space_used_in_percent_new_temp=@avg_page_space_used_in_percent_new output
from [?].sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''Sampled'') AS s
join [?].sys.indexes i on s.Object_Id=i.Object_id and s.Index_id=i.Index_id
select * from #Frag
open cList
fetch next from cList into @DBName,@TableName,@SchemaName,@IndexName,@avg_fragmentation_in_percent_old,@avg_page_space_used_in_percent_old
declare @DBName NVARCHAR(255)
declare @TableName NVARCHAR(255)
declare @SchemaName NVARCHAR(255)
declare @IndexName NVARCHAR(255)
declare @avg_fragmentation_in_percent_old DECIMAL(18,3)
as
SET NOCOUNT on
BEGIN TRY
declare @EXCEPTION VARCHAR(MAX)
declare @MailSubject NVARCHAR(255)
--获取index被整理后的碎片比例
set @Sql=N'USE '+@DBName+';select @avg_fragmentation_in_percent_new_temp=s.avg_fragmentation_in_percent,@avg_page_space_used_in_percent_new_temp= s.avg_page_space_used_in_percent
order by TableName,IndexName '
--定义CURSOR遍历临时表#Frag,根据Fragment大小采取不同的方案维护index.
declare cList CURSOR for
DBname NVARCHAR(255),
TableName NVARCHAR(255),
SchemaName NVARCHAR(255),
IndexName NVARCHAR(255),
declare @avg_page_space_used_in_percent_old DECIMAL(18,3)
declare @avg_fragmentation_in_percent_new DECIMAL(18,3)
declare @avg_page_space_used_in_percent_new DECIMAL(18,3)
exec sp_executesql @Defrag
--获取index被整理后的碎片比例
set @Sql=N'USE '+@DBName+'; select @avg_fragmentation_in_percent_new_temp=s.avg_fragmentation_in_percent,@avg_page_space_used_in_percent_new_temp= s.avg_page_space_used_in_percent
end
--Fragment大于40.0 ,使用 Alter INDEX rebuild整理碎片
else if @avg_fragmentation_in_percent_old >40.0 AND @DBName <>'[TOPCOA]'
begin
--整理碎片
set @Defrag=N'Alter INDEX '+''+@IndexName+' on '+@DBName+'.'+@SchemaName+'.'+@TableName+' rebuild'
exec sp_executesql @Defrag
--write log
insert [dbo].IndexDefrag values(@DBName,@TableName,@SchemaName,@IndexName,getdate(),@avg_fragmentation_in_percent_old,@avg_page_space_used_in_percent_old,@avg_fragmentation_in_percent_new,@avg_page_space_used_in_percent_new,'0')
GO
-- =============================================
-- Author
-- Create date: 2012/01/17
-- Description: IndexMaintain
-- ==============================================
declare @Defrag NVARCHAR(max)
declare @Sql NVARCHAR(max)
declare @ParmDefinition nvarchar(500)
set @EXCEPTION=''
--删除#Frag
where i.object_id=object_id('''+@TableName+''''+')and ='''+@IndexName+''''
set @ParmDefinition=N'@avg_fragmentation_in_percent_new_temp DECIMAL(18,3) output,@avg_page_space_used_in_percent_new_temp DECIMAL(18,3) output'
while @@FETCH_STATUS=0
begin
set @TableName ='['+ @TableName +']'
--Fragment between 20.0 and 40.0 ,使用 Alter INDEX reorganize整理碎片
USE [msdbStoredProcedure [dbo].[IndexMaintain] Script Date: 09/14/2012 17:59:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
from '+@DBName+'.sys.indexes i
inner join '+@DBName+'.sys.dm_db_index_physical_stats(db_id(replace(replace('''+@DBName+''',''['',''''),'']'','''')), object_id('''+@TableName+''''+'),null,null,''sampled'') as s on i.index_id=s.index_id
join [?].sys.tables t on i.Object_id=t.Object_ID
join [?].sys.schemas sc on t.schema_id=sc.SCHEMA_ID
if exists(select * from sys.objects where object_id=object_id(N'#Frag'))
drop table #Frag
--定义临时表#Frag保存index Fragment
create table #Frag(
AvgFragment DECIMAL(18,3),
avg_page_space_used DECIMAL(18,3)
)
--遍历DB中所有table上的index,并将Fragment保存到临时表#Frag中.
if @avg_fragmentation_in_percent_old between 20.0 and 40.0 AND @DBName <>'[TOPCOA]'
begin
--整理碎片
set @Defrag=N'Alter INDEX '+''+@IndexName+' on '+@DBName+'.'+@SchemaName+'.'+@TableName+' reorganize'
exec sp_MSforeachdb @command1= 'insert into #Frag(DBname, TableName,SchemaName,IndexName,AvgFragment,avg_page_space_used)
select ''[?]'' AS DBName, AS TableName, AS SchemaName, AS IndexName, s.avg_fragmentation_in_percent, s.avg_page_space_used_in_percent
where s.avg_fragmentation_in_percent >20 and t.type=''U'' and s.page_count>8 and i.allow_page_locks=1 and i.allow_row_locks=1
exec sp_executesql @Sql,@ParmDefinition ,@avg_fragmentation_in_percent_new_temp=@avg_fragmentation_in_percent_new output, @avg_page_space_used_in_percent_new_temp=@avg_page_space_used_in_percent_new output
from [?].sys.dm_db_index_physical_stats(DB_ID(''?''),NULL,NULL,NULL,''Sampled'') AS s
join [?].sys.indexes i on s.Object_Id=i.Object_id and s.Index_id=i.Index_id
select * from #Frag
open cList
fetch next from cList into @DBName,@TableName,@SchemaName,@IndexName,@avg_fragmentation_in_percent_old,@avg_page_space_used_in_percent_old
declare @DBName NVARCHAR(255)
declare @TableName NVARCHAR(255)
declare @SchemaName NVARCHAR(255)
declare @IndexName NVARCHAR(255)
declare @avg_fragmentation_in_percent_old DECIMAL(18,3)