sqlserver2008 怎么定时清理索引碎片
sqlserver2008 怎么定时清理索引碎片
最好有详细的步骤说明！！

------解决方案--------------------
维护计划
------解决方案--------------------
维护计划
------解决方案--------------------

探讨

维护计划

------解决方案--------------------
新建维护计划,其中有一项是索引重整.

全界面操作.

------解决方案--------------------
维护计划
------解决方案--------------------
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