SQL Server索引维护指导

核心提示： 注：维护方式的选择，一方面要考虑是否是联机维护，SQL Server索引维护指导(2)，另一方面就是速度上的考虑，一般碎片<=30%时，锁定资源周期短，可联机进行，使用重新组织的方法速度比索引重建快;碎片>30%时，索引重建的速度比重新组织要快

注：维护方式的选择，一方面要考虑是否是联机维护，另一方面就是速度上的考虑。一般碎片<=30%时，使用重新组织的方法速度比索引重建快;碎片>30%时，索引重建的速度比重新组织要快。

1. 联机维护

SQL Server2000:

DBCC INDEXDEFRAG 重新组织索引，占用资源少，锁定资源周期短，可联机进行。

SQL Server 2005:

1. 联机重新组织：

ALTER INDEX [index_name] ON [table_name]

REORGANIZE;

2. 联机重建：

ALTER INDEX [index_name] ON [table_name]

REBUILD WITH (FILLFACTOR = 85, SORT_IN_TEMPDB = OFF,

STATISTICS_NORECOMPUTE = ON,ONLINE = ON);

2. 脱机维护

SQL Server2000：DBCC DBREINDEX

SQL Server 2005：ALTER INDEX [indexname] ON [table_name] REBUILD;

CREATE INDEX WITH DROP_EXISTING

3. 能否方便地整理出比较通用的维护过程，实现自动化维护?

a) 获取及查看所有索引的碎片情况

SQL Server2000:

/*
描述：获取服务器上所有数据库的逻辑碎片率>5的索引信息
适用：SqlServer2000以后版本
*/
SET NOCOUNT ON
DECLARE @db_name varchar(128)
DECLARE @tablename varchar(128)
DECLARE @table_schema varchar(128)
DECLARE @execstr　 varchar(255)
DECLARE @objectid　int
DECLARE @indexid　 int
DECLARE @frag　　　decimal
DECLARE @maxfrag　 decimal
DECLARE @sql　　varchar(8000)
-- Decide on the maximum fragmentation to allow for.
SELECT @maxfrag = 5
　　-- Create the table.
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()
)
if not exists(select 1 from dbo.sysobjects where name = 'dba_manage_index_defrag_temp')
CREATE TABLE dba_manage_index_defrag_temp (
　 [db_name] char(255) default '',
　 ObjectName char(255),
　 ObjectId int,
　 IndexName char(255),
　 IndexId int,
　 Lvl int,
　 CountPages int,
　 CountRows int,
　 MinRecSize int,
　 MaxRecSize int,
　 AvgRecSize int,
　 ForRecCount int,
　 Extents int,
　 ExtentSwitches int,
　 AvgFreeBytes int,
　 AvgPageDensity int,
　 ScanDensity decimal,
　 BestCount int,
　 ActualCount int,
　 LogicalFrag decimal,
　 ExtentFrag decimal)
　　-- Declare a cursor.
DECLARE databases CURSOR FOR
　 select
　name
from
　master.dbo.sysdatabases
where
　dbid>4
　　-- Open the cursor.
open databases
fetch databases into @db_name
while (@@fetch_status=0)
begin
insert into dba_manage_index_defrag_temp
(ObjectName ,
　 ObjectId ,
　 IndexName,
　 IndexId ,
　 Lvl ,
　 CountPages ,
　 CountRows ,
　 MinRecSize ,
　 MaxRecSize ,
　 AvgRecSize ,
　 ForRecCount ,
　 Extents ,
　 ExtentSwitches ,
　 AvgFreeBytes ,
　 AvgPageDensity ,
　 ScanDensity ,
　 BestCount ,
　 ActualCount ,
　 LogicalFrag ,
　 ExtentFrag )
exec('use ['+@db_name+'];
　　dbcc showcontig
　 with
　FAST,
　TABLERESULTS,
　ALL_INDEXES,
　NO_INFOMSGS')
update
　dba_manage_index_defrag_temp
set
　[db_name] = @db_name
where　
　[db_name] = ''
fetch next from databases into @db_name
end
　　close databases
deallocate databases
insert into dba_manage_index_defrag
([db_name]
,[table_name]
,[index_name]
,avg_fragmentation_in_percent
)
select
[db_name],
ObjectName [table_name],
indexname [index_name],
LogicalFrag [avg_fragmentation_in_percent]
from
dba_manage_index_defrag_temp
where
logicalfrag>5
-- Delete the temporary table.
DROP TABLE dba_manage_index_defrag_temp
　　GO
SELECT * FROM dba_manage_index_defrag　--查看结果

SQL Server 2005: