SQL Server索引维护指导

SQL Server索引维护指导

索引在数据库相关工作者的日常工作中占据了很重要的位置，索引需要牵涉到索引创建、优化和维护多方面的工作，本文以实例结合相关原理来介绍索引维护相关的知识。文中的相关代码，也可以满足多数情况下索引的维护需求。

实现步骤

1、以什么标准判断索引是否需要维护?

2、索引维护的方法有哪些?

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

一、以什么标准判断索引是否需要维护?

由于本文集中讨论索引维护相关，所以我们暂且抛开创建的不合理的那些索引，仅从维护的角度来讨论。从索引维护的角度来讲，最主要的参考标准就是索引碎片的大小。通常情况下，索引碎片在10%以内，是可以接受的。下面介绍获取索引碎片的方法：

SQL Server 2000: DBCC SHOWCONTIG

SQL Server 2005: sys.dm_db_index_physical_stats

实例(取db_test数据库所有索引碎片相关信息)：

SQL Server 2000:

USE [db_test];

GO

DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

GO

SQL Server 2005:

DECLARE @db_name VARCHAR(256)

SET @db_name='db_test'

SELECT

db_name(a.database_id) [db_name],

[table_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 -–碎片程度大于5

二、索引维护的方法有哪些?

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

1、联机维护

SQL Server2000:

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

SQL Server 2005:

联机重新组织：

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);

3、脱机维护

SQL Server2000：DBCC DBREINDEX

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

CREATE INDEX WITH DROP_EXISTING

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

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,