你的位置:首页 > 数据库

[数据库]sqlserver2008 如何定时清理索引碎片

sqlserver2008 如何定时清理索引碎片

查询索引引起的表垃圾碎片sql脚本:

 SELECT object_name(a.object_id) [TableName] ,a.index_id ,name [IndexName] ,avg_fragmentation_in_percent 
From sys.dm_db_index_physical_stats ( DB_ID() , NULL , NULL, NULL, NULL ) 
As a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
Where avg_fragmentation_in_percent > 0
Order By avg_fragmentation_in_percent Desc 


------解决方案--------------------

维护计划,其中有一项是索引重整.全界面操作.


------解决方案--------------------
declare @Db_name nvarchar(256)
,@SchemaName nvarchar(256)
,@TableName Nvarchar(256)
,@IndexName Nvarchar(512)
,@PctFrag decimal
,@Defrag nvarchar(max)declare frg_cur cursor for
select d.name,e.name,c.name ,b.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
where a.avg_fragmentation_in_percent >20--索引碎片的大小百分比
and c.type='U' and a.page_count>8open frg_cur
fetch next from frg_cur into @Db_name,@SchemaName,@TableName,@IndexName,@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 @Defragend
else if @PctFrag>40.0
begin
SET @Defrag=N' ALTER INDEX '+@IndexName+' ON '+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REBUILD ONLINE=ON'--联机重建索引。即不锁定表重新创建索引
EXEC SP_EXECUTESQL @Defragend
fetch next from frg_cur into @Db_name,@SchemaName,@TableName,@IndexName,@PctFrag
end
close frg_cur
deallocate frg_cur
---记得把系统数据库过滤掉就可以了写个作业 调用 
------解决方案--------------------
----将其放到作业中定时执行即可(最好在生产服务器空闲的时候执行)。仅供参考。 create procedure pr_auto_indexdefrag
as
set nocount onbegin 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;
create table #tmp(dbname nvarchar(256),tablename nvarchar(256),indexname nvarchar(256),schemaname nvarchar(256),avgfragment decimal)exec sp_MSforeachdb 'insert into #tmp(dbname,tablename,indexname,schemaname,avgfragment)
select ''?'' dbname,c.name,b.name,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
where a.avg_fragmentation_in_percent >20
and c.type=''U'' and a.page_count>8
and d.name like ''caihong_%''' declare frg_cur cursor for
select * from #tmp
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_curendendset nocount off------解决方案--------------------
mark一下
尽量争取在生产环境空闲的情况 下优化
重整索引很耗资源的 
------解决方案--------------------

探讨
----将其放到作业中定时执行即可(最好在生产服务器空闲的时候执行)。仅供参考。create procedure pr_auto_indexdefrag
as
set nocount onbegindeclare @Db_name nvarchar(256)
,@SchemaName nvarchar(256)
,@TableName Nvarc……


------解决方案--------------------
作业或者维护计划都可以实现的。 

转载:http://www.myexception.cn/sql-server/319573_2.html