|
SQLServer2000里自动重建索引
4 M# A- @, }% f; L5 b6 Z: xSQLServer2000里自动重建索引 $ q0 M. t6 Z+ c$ ~
--王成辉翻译整理，转贴请注明出自微软BI开拓者www.windbi.com
# k- S7 u' S8 [% W6 E% L% _--原帖地址 , ]) q9 d) n2 Q" r
在所有的OLTP环境里，实质上所有的索引都将随着时间产生碎片。几乎所有的UPDATE、INSERT、DELETE活动都将引起索引比最初创建时变得更无组织。页拆分更多，大量的页上只有很少的数据，因此满足每个SELECT需要更多的I/O。数据和索引的碎片越多，应用程序就会更慢，数据花费的空间就更多。对此你能做什么呢？你可以定期的重建索引。 8 P( i" ]. f w% V" _- y8 X* }3 |
那么什么可以立即使用呢？
9 T2 ]& J. z( f基本上你可以使用数据库维护向导来执行索引重建，创建维护计划来完成。如果你原意接受它固有的缺陷，这也可以使用。首先，用维护向导来配置和完成索引重建是不慎重的。它将重建每一个索引，不管它是否需要重建。如果你有一个有很多大表和大量索引的大数据库，这会出问题，因为不加区别的重建整个数据库的索引会花费很长的时间，会使你的维护窗口不可用。问题在于，要么全部重建，要么全部不重建，你根本不能以任何方式分批处理数据库的表。
% G8 a6 H' D; Q9 c, W4 q
) t' ]0 y5 r; h8 E9 x3 f8 w - ^& h' C$ \- m( b
那么有什么别的能做吗？你可以写一个脚本来重建选择的表的索引。这样你能对数据库分批处理以减少在重建索引时你维护窗口执行的时间。你需要将这个时间减小到最少，因为重建索引会对表执行排它锁，在重建索引期间禁止用户访问。所以你可以每周的每个工作日的晚上重建五分之一表的索引，所有的索引至少一周做一次。然而，这也是不慎重的――你将重建所有表的索引而不论数据和索引是否是有碎片。 ! L: l( h/ U+ Z0 N& x7 N S
这里推荐选择性的重建索引。你需要检查表的索引和数据的碎片，保留数据，据此操作，重建索引要用确定的且区别对待的方式。仅仅通过这样系统的方法，你可以仅重建那些实际需要重建的表的数据和索引。而且也只有这种方式能最小化索引重建的时间。在整个索引重建期间，如果你不想影响你的用户的话，减少索引重建的时间是至关重要的。
4 b# `2 L) w$ g" A那么我们怎样可以解决呢？
4 ]0 D) }2 ^9 q. `# D/ M可以使用命令 6 ]+ J2 z/ k: ?4 D
DBCC SHOWCONTIG() 2 K1 p/ g- ?! t/ H! ~
SQLServer2000比以前版本有一个大的改进就是这个简单而又至关重要的命令。DBCC SHOWCONTIG是SQLServer提供来检查索引碎片情况的工具。在以前的版本里（7.0和更早的版本），这个命令只输出文本，如果手工处理这个命令很好，然而，要实现自动化目的，它会带来严重的问题。那意味着你要循环执行每一个表并将结果输出到文本文件，然后为了读和解释原文的输出结果以便获得你寻找的信息，需要进行烦人的结构化处理。
8 L( N2 t- z' d6 X X- \. S" vSQLServer2000对DBCC SHOWCONTIG()命令引进了一个关键子句，名为WITH TABLERESULTS。这意味着你能运行这个命令然后将捕获的数据直接输出到表里，而不是还需要使用XP_CMDSHELL来操作的文本文件里。
7 c" ?& K3 G& M3 s在SQLServer2000里，这意味着你能结构化的循环处理表，通过在它们上面运行DBCC SHOWCONTIG命令以将捕获碎片信息插入表中。然后你能循环使用这个结果，根据碎片的情况，选择性的进行碎片整理。可以用下面的存储过程实现： , x9 M* r. t$ h9 i! m' u" i
CREATE PROCEDURE sp_defragment_indexes @maxfrag DECIMAL
9 l1 ]6 s. }$ ]; UAS --王成辉翻译整理，转贴请注明出处 * Z9 }& k7 V: `. j9 }2 v3 t
--声明变量
* [! v/ d% q1 V- [, o7 R5 QSET NOCOUNT ON , X1 B' I6 n' i/ \! F
DECLARE @tablename VARCHAR (128) 7 U6 z. ^3 n& c+ B( O7 w
DECLARE @execstr VARCHAR (255)
; n9 J5 T. [+ iDECLARE @objectid INT
+ F% `5 |" R3 I1 I/ z6 UDECLARE @objectowner VARCHAR(255) ' X) C; x s5 }1 f4 M' ^
DECLARE @indexid INT 9 H; z: Z+ ~) G9 b, u4 r+ L1 X
DECLARE @frag DECIMAL
+ n8 _9 G) k; [ L5 t& l7 E. WDECLARE @indexname CHAR(255)
- G' }. F, [, k1 R! Z5 R4 wDECLARE @dbname sysname
. q6 X# @1 r6 Q9 L3 H! m4 gDECLARE @tableid INT
# K2 P) ?( a3 c- `. O* a0 rDECLARE @tableidchar VARCHAR(255)
1 w6 W' }! b. y6 s' t6 ^--检查是否在用户数据库里运行 6 v( O# ]$ m( J/ s6 K
SELECT @dbname = db_name()
4 a! s, |% w0 _3 E/ nIF @dbname IN ('master', 'msdb', 'model', 'tempdb') & V5 k1 I0 L9 C0 o8 V7 r2 y- D. W9 U
BEGIN 8 Q( b. d, ~- x3 a
PRINT 'This procedure should not be run in system databases.' ' W) y9 f+ m0 o+ K% B+ a* R- o
RETURN & T; j5 g, @3 I8 c- Q
END
0 A* N+ [ |: i6 P--第1阶段:检测碎片 * |0 r! |& ~! z5 B
--声明游标
* [- r( t8 K1 h0 ^8 N, rDECLARE tables CURSOR FOR 9 H, m8 [- `. q2 ~( p: j
SELECT convert(varchar,so.id) % z9 X: g, b/ Z5 H" A' p
FROM sysobjects so , |( A4 H1 t1 {+ v
JOIN sysindexes si ( {* i! r4 A( [' K
ON so.id = si.id + }4 `% F4 D! Q& s
WHERE so.type ='U'
0 _# S) A8 M/ N ^AND si.indid < 2 - ?! T7 r: k, [" o; ^* H
AND si.rows > 0 & C+ m* `: i8 A+ ]4 f3 d
-- 创建一个临时表来存储碎片信息
3 u# V. J$ Y$ J' _; `& ACREATE TABLE #fraglist (
" g; [* {. N: b) l' Z# F8 ]ObjectName CHAR (255), 7 h6 O7 _$ i) C
ObjectId INT,
$ J' L( U5 K4 f" LIndexName CHAR (255), $ B7 ]$ [+ r2 M( p$ u, r
IndexId INT, 0 M9 k9 H5 Q. Q, l( x, Z
Lvl INT,
- J: U9 h# M0 |" q3 VCountPages INT, ) `! N7 p) f! a% b b4 s
CountRows INT,
, |9 g1 d M) L) \: M$ n* ~MinRecSize INT,
/ W u0 W& s% C0 u; r3 q7 rMaxRecSize INT, # d" z& l- B+ E$ a/ T) `
AvgRecSize INT,
0 A4 o+ @# w) T- E/ U/ RForRecCount INT,
' B# D6 i0 t; R1 ZExtents INT, * `+ ?0 _& U$ R
ExtentSwitches INT,
+ p- I8 d0 b, O9 B. XAvgFreeBytes INT, * R: y2 c; p/ Y) }) e8 W
AvgPageDensity INT, 8 I& M; l" z3 H7 w9 I7 l8 r
ScanDensity DECIMAL, ) c% b: \$ n: i4 s& j3 c
BestCount INT,
0 y) G T6 ?5 u2 gActualCount INT, v6 A3 D8 o" A
LogicalFrag DECIMAL, 1 l- I, n1 u. b
ExtentFrag DECIMAL) : r: E% T3 y+ e- W) o
--打开游标
( J& T) v; Y* t3 WOPEN tables 8 ^# `3 ^4 A ~- y, j
-- 对数据库的所有表循环执行dbcc showcontig命令
+ @( j q5 N, q2 Q( A& SFETCH NEXT
3 z! [* v2 S9 X0 a$ K! kFROM tables 5 ~5 O' ?$ g: s' \% O
INTO @tableidchar
& g, \, b# D1 J7 h7 r5 DWHILE @@FETCH_STATUS = 0
$ G+ R6 G7 o* x9 WBEGIN - M9 P$ [4 z) |; K
--对表的所有索引进行统计 + V. s8 A2 O" z5 h( v: U
INSERT INTO #fraglist
" Z$ Z2 ]; [7 c$ X; ^/ m$ y" [EXEC ('DBCC SHOWCONTIG (' + @tableidchar + ') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
/ Y7 }% b: w$ V) R* e) M' hFETCH NEXT 4 \8 j, z5 l+ }* ~8 F) c5 d
FROM tables + M# u1 B5 I5 @: `
INTO @tableidchar
. V8 n3 o6 J, I4 M% m! QEND
* m& J {) L }, G O! k-- 关闭释放游标 ! P2 ~: b2 v2 T4 p$ Q" U7 w2 C
CLOSE tables 3 m& V8 w/ l$ k/ u2 B
DEALLOCATE tables 6 |( b: g# p0 J1 ^$ Q2 H
-- 为了检查，报告统计结果 # F f1 ^: ~! D2 O! O/ f% N# o
SELECT * FROM #fraglist / c* k( a2 n+ C, S; }) V- [5 Z
--第2阶段: (整理碎片) 为每一个要整理碎片的索引声明游标 * z1 y/ A" Y; a/ b
DECLARE indexes CURSOR FOR 9 u/ }* u: s2 n' Z! s
SELECT ObjectName, ObjectOwner = user_name(so.uid), ObjectId, IndexName, ScanDensity 6 Q' E- K8 @; _/ i% ]
FROM #fraglist f
* ]3 \" ~9 O& q; m' [2 l+ }4 M* ~JOIN sysobjects so ON f.ObjectId=so.id
" W; {. X$ o$ t* k, k5 VWHERE ScanDensity <= @maxfrag 7 r- G3 a+ `, j, i# M% e- e' M4 x2 F
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
* f, x* `5 c8 p-- 输出开始时间
' q* q& i* F5 r$ F4 gSELECT 'Started defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE())
/ x$ T, A3 E& D--打开游标
$ `/ y+ g3 f( WOPEN indexes
; s! v4 x6 X4 x, \' A4 Q7 ~0 m--循环所有的索引
' g. K5 h5 b8 OFETCH NEXT
f7 |4 B6 F4 e6 qFROM indexes 5 S. B& K/ a/ @2 Y
INTO @tablename, @objectowner, @objectid, @indexname, @frag
% g* h; K: J8 w1 M/ yWHILE @@FETCH_STATUS = 0
. m. v! Q& g, o0 J5 p* }BEGIN 7 K" h& o" U4 P9 o
SET QUOTED_IDENTIFIER ON
& F" p5 H6 w9 k; CSELECT @execstr = 'DBCC DBREINDEX (' + '''' +RTRIM(@objectowner) + '.' + RTRIM(@tablename) + '''' +
% o$ `; }7 X8 ?# D& l; G$ h', ' + RTRIM(@indexname) + ') WITH NO_INFOMSGS' ' ^' O) H' F8 X# a
SELECT 'Now executing: ' . u" V! h w2 r% k" S
SELECT(@execstr)
# j( C7 j. U+ O; pEXEC (@execstr)
# _" v+ b. `0 u) i6 E. D- {6 k. USET QUOTED_IDENTIFIER OFF - Y4 a( h9 x# C" x
FETCH NEXT
' N$ I p9 N" Q# fFROM indexes
) _5 ~& s2 i* f3 [8 A2 C: J( {3 v; e0 Y2 NINTO @tablename, @objectowner, @objectid, @indexname, @frag . q1 G& z" H# A2 t+ Z8 x
END
+ T5 n0 a! k- I5 r0 G-- 关闭释放游标
+ U+ |% z* w2 QCLOSE indexes
5 _3 t# M5 v$ e/ n0 |DEALLOCATE indexes
/ i5 Q; m' M3 g$ B2 D6 L-- 报告结束时间 E3 z* c# q3 l$ N- {, z9 ]
SELECT 'Finished defragmenting indexes at ' + CONVERT(VARCHAR,GETDATE()) 5 Q. L7 b. V4 z2 l9 a' I C7 @
-- 删除临时表
- M1 g, P5 E- c" D2 LDROP TABLE #fraglist 9 M5 D7 P9 S+ d- [, d- m
GO 5 { X4 J& V6 x8 u- S
使用
2 ~$ Q% U- n) f; p这个存储过程应该创建在master数据库里，以便你能在服务器上的任何用户数据库里使用。
% F" S' Y* C$ X1 `: j' o* p在用户数据库里通过传递一个参数（MAXFRAG）来运行。该参数是一个百分比值。意思是任何索引的碎片扫描密度小于这个值。例如，如果你想要整理那些扫描密度小于95％的索引的碎片： 1 O) i: e6 K6 W9 W }# K1 R0 w+ I+ E
USE pubs
+ x( J( g! W# JGO 1 M, d6 j; N. c
EXEC sp_deframent_indexes 95.00 ; w/ z! N) g2 e2 \: D
局限
% V" Q. n1 G7 Y" ^" u# }% l/ @这个过程依赖于的标准是扫描密度，但扫描密度对于那些跨越多个文件的索引来说不是一个有效的标准。如果你的索引确实跨越多个文件，你需要用另一个标准（如Logical Frag）来更改这个存储过程。然而，这类更改超过本文的范围；如果你的索引跨越多个文件，你需要做更多的工作。 , ?" w) ^( p% ^
怎样做，做什么？
' I+ l" H4 U$ P0 Y& g这个存储过程有两个独特的部分。
. |5 K2 d: ?, {5 C- `8 r; u第1阶段
$ I5 ~/ L8 L$ w, D- g/ L7 X2 ?在这部分里，存储过程通过在数据库里的每个表上运行下面的命令来检查索引碎片：
S8 e0 W/ y7 |# Z; R2 x3 ]+ eDBCC SHOWCONTIG (‘tablename') WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS * ^8 j0 Z! J0 t6 y
命令的结果存储在预先创建的临时表#fraglist里。这里就会用到DBCC SHOWCONTIG 语句的WITH TABLERESULTS的好处，仅这一点，真正的节省了太多的以前版本得到同样结果所花费的麻烦和精力。 - w1 o5 a5 K2 L3 Y& k# ]+ d( r% r
你应该注意该存储过程工作的数据库的表的拥有者是不是dbo，通常是。我发现我最初的版本不起作用，当时一个软件经销商给我们提供的新系统的数据库里就出现了拥有者不是dbo的表。当我在这个新系统上第一次运行我的碎片整理过程时，这个程序的缺点就暴露无遗了，最后彻底失败。这个问题实际上出现在碎片整理阶段（阶段2），因为表在这里要引用表名，而在阶段1，DBCC SHOWCONTIG命令引用的时表的ID即object_id。
( G) @6 ^* z" O' ^第2阶段
3 j9 K& \! k4 O* t* M. b, O! H0 s这儿使用了另一个游标来循环处理表#fraglist里的记录，这些记录是那些扫描密度低于传给存储过程参数的那个阈值的表： ( n! E( K( X% [) x6 |% q
DBCC DBREINDEX() ' |0 R. p$ a" J3 c; U, X, i
执行的结果以输出文件的形式显示在表#fraglist的内容之后，以便你能查看表和索引的碎片，正如屏幕上所显示的那样，也可以通过查看DBCC DBREINDEX()执行的结果列表来查看采取的动作。利用这些你也能推导出每个索引重建的时间。 7 H. `" ~ `4 m2 }3 b
输出结果是什么意思？ E1 S# F/ f1 z, S3 m' W$ w n+ `
输出示例：
- u" N% x% }1 i# ~ 
" A, b. j; J8 }上面是在Excel里打开的存储过程输出文本文件的一个截屏。为了简洁一些列已经删掉了。你需要用文本文件向导来打开它，选择固定列宽，打开导入从第三行起。 ' U5 O1 U* }/ J4 P5 b2 h; @4 `
这里，你能够检查你选择检查的数据库里的表的扫描密度。
2 }0 m" P, W; t& T% g在接下来的输出文件里（DBCC SHOWCONTIG输出结果的后面），你会发现正被重建索引的每个表或索引的细节，这部分的开始和结束部分都有重建索引的开始和结束时间。如下面例子显示的那样： 1 F/ b$ R8 ~9 u1 y4 Y
, x0 Y) _. k! q: b, ^- s$ ~为什么不使用DBCC INDEXDEFRAG()去减少阻塞？ . N2 V6 W. m# u& A$ X- u# i
答案是如果你想要或者需要的话就使用它。如果你需要7×24小时的在线操作，那么DBCC DBREINDEX()的排他表锁不适合你的业务，你可以使用它来代替DBCC DBREINDEX()。然而，你需要适当改变一下语法，因为它们是不相同的（谢谢，微软！）。如果你不知道它们的区别，这里有一个简单的摘要：当运行DBCC DBREINDEX()的时候，必须对表有排他锁，因为它是一个完全的，彻头彻尾的索引重建操作。而DBCC INDEXDEFRAG()就不那么完全了，在线的操作试图改善你索引的环境而不至于引起阻塞和中断OLTP（希望如此）。我必须承认我从来不用DBCC INDEXDEFRAG()，因为很幸运的是我的系统不需要严格的7×24在线且要求不阻塞，所以我不敢担保是否有效率。我已经理解它不是和DBCC DBREINDEX一样有效率。然而它的确比什么都没有强，所以如果你的数据库运行一个全球的WEB站点并且从来不能停止，这在今天这也很普遍，那么你需要使用它来代替以改变这个存储过程。
& N& w! h" f' j+ _) a添加到调度任务里 6 f0 \2 R) L) ?
对于有相当经验的DBA来说这是一个相关的微不足道的任务，所以我在这里不会提供它的代码。作为一个独立的任务或在你存在的维护作业里的一个步骤都行。你要确保作业的步骤或作业里的输出结果是一个文本文件以便你能保存和查看所有重要的输出文件。 : j; ~2 j6 v8 B
结论 & n( T+ }! x6 H+ A F1 C, [
希望这篇文章和代码能帮助你完成一个对数据库服务器维护来说更好的更精确的方法。现在的维护窗口开销很大，所以在影响用户和执行时间上保持最小的同时也提供了有效率和良好的数据库服务器维护。用调度作业实现这个过程，小心的监控它的输出结果。 | 
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
抢沙发
千斤顶
显身卡
详细信息 
300x180 AD
