2018年01月聚合文章--sql server 索引碎片整理

1. 首先需要用sql 查询出数据库索引碎片情况：

SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName,

ind.name AS IndexName, indexstats.index_type_desc AS IndexType,

indexstats.avg_fragmentation_in_percent

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) indexstats

INNER JOIN sys.indexes ind

ON ind.object_id = indexstats.object_id

AND ind.index_id = indexstats.index_id

WHERE indexstats.avg_fragmentation_in_percent > 50

ORDER BY indexstats.avg_fragmentation_in_percent DESC

2. 碎片整理

常见是

1）删除索引并重建

2）使用 DROP_EXISTING 语句重建索引

3）使用 ALTER INDEX REBUILD 语句重建索引

4）使用 ALTER INDEX REORGANIZE 重新组织索引

我们使用的是方法三 Rebuild 与方法四 Reorganize 。

对有些数据记录比较多、即使访问低峰也被经常访问的表进行索引 Rebuild 会失败，尝试删除索引也失败，出现 "Lock request time out period exceeded." 的错误，后来改用 Reorganize 操作成功。在对有些表的索引进行 Reorganize 操作时出现了 SQL Server Management Stuido 窗口一直卡死的问题，后来通过 Windows 任务管理器强制结束了这个窗口，结束后发现索引碎片已成功整理。

对于 IndexType 为 HEAP 的索引碎片，只需为对应的表创建聚集索引，索引碎片就会自动消失。

对于记录数超过600万的表，无论是聚集索引还是非聚集索引，只要进行 Rebuild 操作，就会阻塞所有对该表查询操作，只能使用 Reorganize ，对该表的聚集索引进行 Reorganize 操作耗时 26 分钟。

详见： SQL Server索引的维护 - 索引碎片、填充因子 <第三篇>

如果用sql 的话可以这样：

USE [msdb]

/****** Object: StoredProcedure [dbo].[IndexMaintain] Script Date: 09/14/2012 17:59:33 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

-- =============================================

-- Author

-- Create date: 2012/01/17

-- Description: IndexMaintain

-- ==============================================

ALTER procedure [dbo].[IndexMaintain]

SET NOCOUNT on

BEGIN TRY

declare @EXCEPTION VARCHAR(MAX)

declare @MailSubject NVARCHAR(255)

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)

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)

declare @Defrag NVARCHAR(max)

declare @Sql NVARCHAR(max)

declare @ParmDefinition nvarchar(500)

set @EXCEPTION=''

--删除#Frag

if exists(select * from sys.objects where object_id=object_id(N'#Frag'))

drop table #Frag

--定义临时表#Frag保存index Fragment

create table #Frag(

DBname NVARCHAR(255),

TableName NVARCHAR(255),

SchemaName NVARCHAR(255),

IndexName NVARCHAR(255),

AvgFragment DECIMAL(18,3),

avg_page_space_used DECIMAL(18,3)

)

--遍历DB中所有table上的index,并将Fragment保存到临时表#Frag中.

exec sp_MSforeachdb @command1= 'insert into #Frag(DBname, TableName,SchemaName,IndexName,AvgFragment,avg_page_space_used)

select ''[?]'' AS DBName, t.Name AS TableName, sc.Name AS SchemaName, i.name AS IndexName, s.avg_fragmentation_in_percent, s.avg_page_space_used_in_percent

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

join [?].sys.tables t on i.Object_id=t.Object_ID

join [?].sys.schemas sc on t.schema_id=sc.SCHEMA_ID

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

order by TableName,IndexName '

--定义CURSOR遍历临时表#Frag,根据Fragment大小采取不同的方案维护index.

declare cList CURSOR for

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

while @@FETCH_STATUS=0

begin

set @TableName ='['+ @TableName +']'

--Fragment between 20.0 and 40.0 ,使用 Alter INDEX reorganize整理碎片

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_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

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

where i.object_id=object_id('''+@TableName+''''+')and i.name='''+@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'

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

--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')

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

--获取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

from '+@DBName+'.sys.indexes i

where i.object_id=object_id('''+@TableName+''''+')and i.name='''+@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'

--write log

end

fetch next from cList into @DBName,@TableName,@SchemaName,@IndexName,@avg_fragmentation_in_percent_old,@avg_page_space_used_in_percent_old

end

close cList

deallocate cList

END TRY

BEGIN CATCH

SET @EXCEPTION = ERROR_MESSAGE()