首页 > 新闻系统 > 编程天地 > 文章正文

SQL Server索引维护指导

2008-04-15 10:55:02　来源:IT专家网　作者:王红波点击:

本文以笔者在实际工作中对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],
   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 -–碎片程度大于5

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

　　注：维护方式的选择，一方面要考虑是否是联机维护，另一方面就是速度上的考虑。一般碎片<=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 --查看结果