你的位置:首页 > 数据库

[数据库]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>8

open 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 @Defrag

end
else if @PctFrag>40.0
begin
SET @Defrag=N' ALTER INDEX '+@IndexName+' ON '+@Db_name+'.'+@SchemaName+'.'+ @TableName +' REBUILD ONLINE=ON'--联机重建索引。即不锁定表重新创建索引
EXEC SP_EXECUTESQL @Defrag

end
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 on

begin

 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_curend

end

set nocount off

------解决方案--------------------
mark一下
尽量争取在生产环境空闲的情况 下优化
重整索引很耗资源的 
------解决方案--------------------

探讨
----将其放到作业中定时执行即可(最好在生产服务器空闲的时候执行)。仅供参考。



create procedure pr_auto_indexdefrag
as
set nocount on

begin

declare @Db_name nvarchar(256)
,@SchemaName nvarchar(256)
,@TableName Nvarc……


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

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

 




山西旅游路线推荐山西自驾游攻略山西旅游线路大全什么时候去山西旅游最好山西跟团旅游报价多少清远飞霞山乘车方便吗?飞霞山坐车要多久? 外国人最爱的10大中国菜 糖醋里脊排第一(全文) 深圳东部华侨城图片?东部华侨城大峡谷有哪些特色? 澳门环岛游坐几路车?珠海澳门环岛游有直达车吗? 惠州蓝田瑶族风情园怎么样?蓝田瑶族风情园有什么好玩的? 蓝田瑶族风情园门票包含什么?龙门蓝田瑶族风情园有表演吗? 蓝田瑶族风情园团购价格?龙门蓝田瑶族风情园门票优惠政策? 广宁竹海自驾游团购价格?广宁竹海大观门票网上预订? 柞水峡谷第一漂停车收费吗?柞水峡谷第一漂流有停车场吗? 柞水峡谷第一漂流好不好?商洛柞水峡谷第一漂流有什么好玩的? 金沙湾景区怎么坐车去?秦皇岛金沙湾景区有公交车吗? 金三角漂流小孩可以玩吗?天堂寨金三角漂流适合多大的儿童玩? 丽江有什么好玩的娱乐活动吗? 春节去昆明、丽江怎么样? 三亚有哪些机场?机场有几个国际航班? 丽江适合什么时候旅游?最佳的旅游季节是? 554-0001 Datasheet 554-0001 Datasheet 554-0001-100 Datasheet 554-0001-100 Datasheet 554-0001-111 Datasheet 554-0001-111 Datasheet 黑龙江参团去港澳 黑龙江参团去港澳 黑龙江参团去港澳 东营去香港旅游 东营去香港旅游 东营去香港旅游 苏州去香港旅游 苏州去香港旅游 苏州去香港旅游