SQL Server索引维护指导

核心提示： /*描述：只显示逻辑碎片率大于5%的索引信息限制：针对SqlServer2005以后版本。功能：对数据库服务器所有非系统数据库进行索引碎片检查返回碎片率>5%的索引信息*/create proc p_dba_manage_get_index_defrageasset nocount

/*
描述：只显示逻辑碎片率大于5%的索引信息
限制：针对SqlServer2005以后版本。
功能：对数据库服务器所有非系统数据库进行索引碎片检查
　返回碎片率>5%的索引信息
*/
create proc p_dba_manage_get_index_defrage
as
set nocount on
if not exists(select 1 from sys.tables where name = 'dba_manage_index_defrag')
create table dba_manage_index_defrag
([db_name] varchar(255)
,[table_name] varchar(255)
,[index_name] varchar(255)
,avg_fragmentation_in_percent real
,write_time datetime default getdate()
)
　　declare @db_name nvarchar(40)
set @db_name = ''
declare cur_db_name cursor for
select
　name
from
　sys.databases
where
　database_id > 4 and state = 0
　　open cur_db_name
fetch cur_db_name into @db_name
while (@@fetch_status=0)
begin
insert into dba_manage_index_defrag
　([db_name]
　,table_name
　,index_name
　,avg_fragmentation_in_percent)
SELECT
　db_name(a.database_id) [db_name],
　c.name [table_name],
　b.name [index_name],
　a.avg_fragmentation_in_percent
FROM
　sys.dm_db_index_physical_stats (DB_ID(@db_name), null,NULL, NULL, 'Limited') 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
where
　a.index_id>0
　and a.avg_fragmentation_in_percent>5
fetch next from cur_db_name into @db_name
end
　　CLOSE cur_db_name
DEALLOCATE cur_db_name
　　GO
select * from dba_manage_index_defrag –查看结果

b) 根据索引碎片的情况自动选择合适的处理方法