SQLServer2000里自动重建索引

SQLServer2000里自动重建索引

在所有的OLTP环境里，实质上所有的索引都将随着时间产生碎片。几乎所有的UPDA TE、INSERT、DELETE活动都将引起索引比最初创建时变得更无组织。页拆分更多，大量的页上只有很少的数据，因此满足每个SELECT需要更多的I/O。数据和索引的碎片越多，应用程序就会更慢，数据花费的空间就更多。对此你能做什么呢？你可以定期的重建索引。

那么什么可以立即使用呢？

基本上你可以使用数据库维护向导来执行索引重建，创建维护计划来完成。如果你原意接受它固有的缺陷，这也可以使用。首先，用维护向导来配置和完成索引重建是不慎重的。它将重建每一个索引，不管它是否需要重建。如果你有一个有很多大表和大量索引的大数据库，这会出问题，因为不加区别的重建整个数据库的索引会花费很长的时间，会使你的维护窗口不可用。问题在于，要么全部重建，要么全部不重建，你根本不能以任何方式分批处理数据库的表。

那么有什么别的能做吗？你可以写一个脚本来重建选择的表的索引。这样你能对数据库分批处理以减少在重建索引时你维护窗口执行的时间。你需要将这个时间减小到最少，因为重建索引会对表执行排它锁，在重建索引期间禁止用户访问。所以你可以每周的每个工作日的晚上重建五分之一表的索引，所有的索引至少一周做一次。然而，这也是不慎重的――你将重建所有表的索引而不论数据和索引是否是有碎片。

这里推荐选择性的重建索引。你需要检查表的索引和数据的碎片，保留数据，据此操作，重建索引要用确定的且区别对待的方式。仅仅通过这样系统的方法，你可以仅重建那些实际需要重建的表的数据和索引。而且也只有这种方式能最小化索引重建的时间。在整个索引重建期间，如果你不想影响你的用户的话，减少索引重建的时间是至关重要的。

那么我们怎样可以解决呢？

可以使用命令

DBCC SHOWCONTIG()

SQLServer2000比以前版本有一个大的改进就是这个简单而又至关重要的命令。DBCC SHOWCONTIG是SQLServer提供来检查索引碎片情况的工具。在以前的版本里（7.0和更早的版本），这个命令只输出文本，如果手工处理这个命令很好，然而，要实现自动化目的，它会带来严重的问题。那意味着你要循环执行每一个表并将结果输出到文本文件，然后为了读和解释原文的输出结果以便获得你寻找的信息，需要进行烦人的结构化处理。

SQLServer2000对DBCC SHOWCONTIG()命令引进了一个关键子句，名为WITH TABLERESULTS。这意味着你能运行这个命令然后将捕获的数据直接输出到表里，而不是还需要使用XP_CMDSHELL来操作的文本文件里。

在SQLServer2000里，这意味着你能结构化的循环处理表，通过在它们上面运行DBCC SHOWCONTIG命令以将捕获碎片信息插入表中。然后你能循环使用这个结果，根据碎片的情况，选择性的进行碎片整理。可以用下面的存储过程实现：

CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL

AS --王成辉翻译整理，转贴请注明出处

--声明变量

SET NOCOUNT ON

DECLARE @tablename VARCHAR (128)

DECLARE @execstr VARCHAR (255)

DECLARE @objectid INT

DECLARE @objectowner VARCHAR(255)

DECLARE @indexid INT

DECLARE @frag DECIMAL

DECLARE @indexname CHAR(255)

DECLARE @dbname sysname

DECLARE @tableid INT

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

--第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 (

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)

--打开游标

OPEN tables

-- 对数据库的所有表循环执行dbcc showcontig命令FETCH NEXT

FROM tables

INTO @tableidchar

WHILE @@FETCH_STATUS = 0

BEGIN

--对表的所有索引进行统计