MSSQL自动重建出现碎片的索引的方法分享

1.索引碎片的产生？

由于在表里大量的插入、修改、删除操作而使索引页分裂。如果索引有了高的碎片，有两种情况，一种情况是扫描索引需要花费很多的时间，另一种情况是在查询的时候索引根本不使用索引,都会导致性能降低。

2.碎片类型分为：

2.1 内部破碎

由于索引页里的数据插入或修改操作而发生，以数据作为稀疏矩阵的形式的分布而结束，这将导致数据页的增加，从而增加查询时间。

2.2外部破碎

由于索引/数据页的数据插入或修改而发生，以页码分离和在文件系统里不连贯的新的索引页的分配而结束，数据库服务器不能利用预读操作的优点，因为：下一个相关联的数据页不临近，而且这些相关连的下面的页码可能在数据文件的任何地方。

自动重建发生碎片的索引

在数据中新建碎片整理存储过程

-- ================================================
-- template generated from template explorer using:
-- create procedure (new menu).sql
--
-- use the specify values for template parameters
-- command (ctrl-shift-m) to fill in the parameter
-- values below.
--
-- this block of comments will not be included in
-- the definition of the procedure.
-- ================================================
set ansi_nulls on
go
set quoted_identifier on
go
-- =============================================
-- author:  <author,,wuxiangqian>
-- create date: <create date,2014-05-16>
-- description: <description,重建出现碎片的索引>
-- =============================================
alter procedure usp_ims_defragment_indexes
as
--声明变量
set nocount on
declare @tablename varchar (128) --表名称（已发生索引碎片）
declare @execstr varchar (255) --执行重建索引的语句
declare @indexname char(255) --索引名称
declare @dbname sysname --数据库名称
declare @dbnamechar varchar(20) --数据库名称
declare @tableidchar varchar(255) --表名称（用于遍历索引碎片）

--检查是否在用户数据库里运行
select @dbname = db_name()
if @dbname in ('master', 'msdb', 'model', 'tempdb')
begin
print 'this procedure should not be run in system databases.'
return
end else
begin
set @dbnamechar = 'dbname'
end

--第1阶段:检测碎片
--声明游标
declare tables cursor for
select convert(varchar,so.id)
from sysobjects so
join sysindexes si
on so.id = si.id
where so.type ='u'
and si.indid < 2
and si.rows > 0

-- 创建一个临时表来存储碎片信息
create table #fraglist (
tablename char (255),
indexname char (255))

--打开游标
open tables

-- 对数据库的所有表循环执行dbcc showcontig命令
fetch next
from tables
into @tableidchar

while @@fetch_status = 0
begin
--对表的所有索引进行统计
insert into #fraglist

exec ('select object_name(dt.object_id) as tablename,si.name as indexname from '+
' (select object_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent '+
' from sys.dm_db_index_physical_stats(db_id('''+@dbnamechar+'''),object_id('''+@tableidchar+''')'+
',null,null,''detailed'') where index_id<>0)as dt inner join sys.indexes si'+
' on si.object_id=dt.object_id and si.index_id=dt.index_id and '+
' dt.avg_fragmentation_in_percent>10'+
' and dt.avg_page_space_used_in_percent<75 order by dt.avg_fragmentation_in_percent desc')
fetch next
from tables
into @tableidchar
end

-- 关闭释放游标
close tables
deallocate tables

-- 为了检查，报告统计结果
select * from #fraglist

--第2阶段: (整理碎片) 为每一个要整理碎片的索引声明游标
declare indexes cursor for
select tablename, indexname
from #fraglist
-- 输出开始时间
select 'started defragmenting indexes at ' + convert(varchar,getdate())
--打开游标
open indexes
--循环所有的索引
fetch next
from indexes
into @tablename, @indexname
while @@fetch_status = 0
begin
set quoted_identifier on
select @execstr = 'alter index '+@indexname+' on '+@tablename+' rebuild with(fillfactor=90,online=on)'

select 'now executing: '
select(@execstr)
exec (@execstr)
set quoted_identifier off
fetch next
from indexes
into @tablename, @indexname
end
-- 关闭释放游标
close indexes
deallocate indexes

-- 报告结束时间
select 'finished defragmenting indexes at ' + convert(varchar,getdate())

-- 删除临时表
drop table #fraglist
go

go

设置定时执行步骤

（1）启动【sql server management studio】，在【对象资源管理器】窗口里选择【管理】——【维护计划】选项。

（2）右击【维护计划】，在弹出的快捷菜单里选择【维护计划向导】选项，弹出如图所示的【维护计划向导】对话框，单击【下一步】按钮

（3）弹出如图所示【选择目标服务器】对话框，在【名称】文本框里可以输入维护计划的名称；在【说明】文本框里可以输入维护计划的说明文字；【在服务器】文本框里可以输入要使用的服务器名；最后选择正确的身份证信息，单击【下一步】按钮。

（4）弹出如图所示【选择维护任务】对话框，在该对话框中可以选择执行sql维护任务，插入执行存储过程语句

use [dbname]

go

exec [dbo].[usp_ims_defragment_indexes]

（5）指定任务执行计划