原文：Microsoft SQL Server 2000 Index Defragmentation Best Practices
来源：Microsoft TechNet
作者：Mike Ruthruff
时间：February 2003
Summary As Microsoft SQL Server 2000 maintains indexes to reflect updates to their underlying tables, these indexes can become fragmented. Depending on workload characteristics, this fragmentation can ……
——————————————————————————–
摘要 既然SQL Server 2000为了反应数据的更新，需要维护表上的索引，因而这些索引会形成碎片。根据工作量的特征，这些碎片会影响对应的工作性能。该白皮书提供能帮助你决定是否需要整理碎片以改善性能的信息。SQL Server 2000提供了一些命令来实现索引的碎片整理。这里比较其中两个命令：DBCC DBREINDEX 和 DBCC INDEXDEFRAG。
目录
概述
了解碎片
整理碎片前需要考虑的因素
小规模环境 vs. 大规模环境
决定何时进行索引碎片整理
DBCC DBREINDEX vs. DBCC INDEXDEFRAG
结论
更多信息
附录 A: 测试环境
概述
本白皮书提供在生产环境中，决定是否进行索引的碎片整理工作以改善工作性能的信息。另外，本文比较了Microsoft SQL Server 2000中用于索引碎片整理的两个命令：DBCC DBREINDEX 和 DBCC INDEXDEFRAG。这个比较包括不同的数据库和硬件环境的测试结果。关于测试环境，请见章节”小规模环境 vs. 大规模环境”和附录A。
注意: 并不是在任何情况下，碎片整理都会改善性能。每个场景是不同的。也因为如此，所以是否要进行碎片整理工作要根据分析结果而定。
白皮书叙述索引碎片整理的重要性以及常规处理流程。下面列举本文的关键观点：
在索引碎片整理前，请确保系统资源的一些问题，比如物理磁盘碎片，不合理的基础结构等因素会给性能带来负面影响。
DBCC SHOWCONTIG可以显示索引碎片数量。当运行该命令时，要特别注意逻辑碎片(Logical Fragmentation)和页密度(Page Density)两个指标。
决定是否要碎片整理，考察工作类型很重要。不是所有情况下，都能从碎片整理中受益。对读取比较多的工作类型来说，磁盘I/O是最重要的性能指标。测试显示决策支持系统(DSS: Decision Support System)比很多在线事务处理系统(OLTP: Online Transaction Processing)，从碎片整理中获益更多。
碎片将影响磁盘性能和SQL Server预读管理(read-ahead manager)的效果。Windows性能监视器有几个关键指标可以用来支持这一观点。
决定是否用 DBCC DBREINDEX 还是 DBCC INDEXDEFRAG 取决于你的需求以及硬件环境。
DBCC DBREINDEX会带来更新统计(updating statistics)的副作用，而DBCC INDEXDEFRAG不会。可以通过在执行DBCC INDEXDEFRAG后执行UPDATE STATISTICS来增加其影响。
了解碎片
当索引所在页面的基于主关键字的逻辑顺序，和数据文件中的物理顺序不匹配时，碎片就产生了。所有的叶级页包含了指向前一个和后一个页的指针。这样就形成一个双链表。理想情况下，数据文件中页的物理顺序会和逻辑顺序匹配。整个磁盘的工作性能在物理顺序匹配逻辑顺序时将显著提升。对某些特定的查询而言，这将带来极佳的性能。当物理排序和逻辑排序不匹配时，磁盘的工作性能会变得低效，这是因为磁头必须向前和向后移动来查找索引，而不是只象某个单一方向来搜索。碎片会影响I/O性能，不过对于位于SQL Server数据缓冲内的数据页而言，碎片并不会带来任何影响。
当索引第一次创建时，没有或者只有极少碎片。随着时间推移，插入，更新和删除数据，和这些数据相关的索引上的碎片就增加了。为了整理碎片，SQL Server提供如下命令：
CREATE INDEX后的DROP INDEX命令
不带DROP_EXISTING选项的CREATE INDEX命令
DBCC INDEXDEFRAG
DBCC DBREINDEX
本文用 DBCC INDEXDEFRAG 和 DBCC DBREINDEX 命令来进行测试。这些命令都可以在在线和离线场景下执行。DBCC DBREINDEX按照CREATE INDEX的方式创建索引；因此DBCC DBREINDEX的执行结果和用CREATE INDEX命令的结果很相似。上面所有这些命令的测试结果和功能描述会在本文后面提到。
整理碎片前需要考虑的因素
系统资源问题
在索引碎片整理之前，要确认系统任何性能问题和系统资源限制无关。关于这方面的详细讨论已经超出了本文的范围，不过有些更常见的资源问题和I/O子系统性能，内存使用以及CPU使用率相关。关于分析这些类型资源问题的更深入讨论，请见本文最后的“更多的信息”章节。
物理磁盘碎片
在某些系统上，磁盘碎片会带来很糟的性能。要确定是否存在磁盘碎片，可以使用Microsoft Windows自带的系统工具，或者第三方提供的工具来分析SQL Server所在的分区。对于常规的I/O子系统上的规模较小的数据库，建议在运行索引碎片整理工具前，先进行磁盘碎片整理。而对于更智能的磁盘子系统上的规模较大的数据库，例如SAN(存储区域网络 storage area networks)环境，磁盘碎片整理就不是必要的。
执行情况较差的查询
当考察任何性能相关问题时，你必须能识别出那些查询执行效率较差。这里讨论的一些信息在后面也会用到，这些信息用于决定那些索引碎片将被整理。
可以使用SQL Profiler(事件探查器)来识别执行效率差的查询(关于这方面更多的信息，请参考SQL Server联机帮助的”SQL Profiler”主题)。运行SQL Profiler会带来开销；不过，只监控下面介绍的一些事件可以收集到必要的信息，而且对性能的影响尽可能的小(一般来说，小于10%的CPU使用率，当然有根据情况有些差异)。
SQL Profiler提供了一个名叫SQLProfilerTSQL_Duration的跟踪模板，可以捕获相关的事件。可以很快捷地利用它来识别执行效率较差的查询。也可以手工创建SQL Profiler跟踪来捕获下述事件：
TSQL: SQLBatchCompleted
Stored Procedures: RPC:Completed
运行SQL Profiler的时间长度要根据服务器工作量而定。为了让跟踪更有效，需要选择代表性的任务类型，至少应该选择那些能显示性能低下的工作类型。当跟踪被捕获后，检场跟踪日志中持续时间那列数据。该列数据以毫秒为单位，表示每个批处理或者查询运行需要的时间。
标识出引起性能最差的查询
这里列举能够标识出造成最糟糕性能的查询的一些建议：
按查询持续时间对跟踪进行分组。将注意力首先放在前10个最差的查询上。
如果在应用中大量使用了存储过程，考虑使用SQLProfilerSP_Counts模板来标识被调用最多的那些存储过程。将注意力放在被调用最频繁，同时也是引起较差性能的存储过程。
将收集的数据放到SQL Server表中。这样，就可以通过查询表来对工作性能进行更为详细的分析(例如，平均运行时间，最大运行时间，等等)。
基础结构
当找出运行时间最长，性能最差得查询后，必须确保数据库基础架构对于那个查询来说是最优的。例如确保存在适当的索引并且被那个查询正确地使用了。可以使用查询分析器来显示和检查查询计划，以发现在查询任务中那些索引被用到了。当使用查询分析器图形化显示查询的执行计划时，以前的数据会以警告的方式标识(例如表名会以红色字体显示)。在整理碎片之前要解决这些问题。
检查查询计划时，要牢记以下建议：
找到执行计划中开销较大的步骤。这些步骤是查询中最耗时的部分。解决这些步骤带来的问题将会使性能大幅提高。
找出执行索引扫描的步骤。索引扫描是从碎片整理中获利最大的部分。注意那些性能较差的查询索引扫描中用到的索引，在碎片整理的时候可以集中在这些索引上进行。
利用SQL Profiler中捕获的跟踪信息，以及手工从查询计划中获取的信息，就可以使用索引向导(Index Tuning Wizard)来分析工作量。利用索引想到生成的报表来决定是否要对基础结构做改动。在碎片整理前做完这些改动。
小规模环境 vs. 大规模环境
这里做的测试基于两台服务器，两台服务器之间的I/O子系统相差很大。一台服务器代表小规模环境，而另一台代表大规模环境。用来解释测试结果，每台环境的规格如下。
小规模环境
在小规模环境中，数据库大小在10GB－20GB之间。数据分布再两个物理磁盘上，tempdb和数据库日志分别在两个使用RAID 0的额外磁盘上。DSS数据库包含两个文件组，每个文件组内有一个文件。OLTP数据库只包含一个文件组，文件组内有一个数据文件。
大规模环境
Micorosoft和Hitachi Data System系统配合，可以用Hitachi Freedom Storage Lightning 9900 Series Lightning 9960 system来构建SAN环境，用于存储数据。用于测试的那个数据库大小大约为1TB。数据分散在64个物理磁盘上，使用RAID1+0结构。存储数据的磁盘由8个LUNs(Logical Unit Numbers)连接，数据库包含一个文件组，该文件组中包含8个数据文件。tempdb和数据库日志单独放在一组磁盘上，与数据文件隔离开，48个磁盘用于存放tempdb，而日志分布在8个磁盘上。为了快速备份和恢复有碎片的数据库镜像，在SAN中维护中有两个Hitachi ShadowImage拷贝数据/日志备份，Lightning 9960系统用于同步在线数据和ShadowImage备份数据。在该环境中，重复在三个碎片级别上运行两次，因为大容量的存储需要维护每个级别(大约1.4TB)的备份。
索引碎片整理对性能的影响
测试结果在后面会详细讨论。但是，虽然碎片整理对两个环境(小规模和大规模)环境都带来负面影响，但是无疑对大规模环境的影响要小得多。因为大规模环境从SAN中获取了极高的I/O性能，因此这个结论应该是对的：数据不光分散在多个磁盘上，而且SAN还提供16GB的数据缓冲区。I/O benchmark测试显示创建1TB数据量，最大的读取速度为354 MB/sec, 而小规模环境下只有71 MB/sec。
注意: 这些数值会根据各人的实现步骤和存储配置而变。
显然，高性能的I/O子系统对SQL Server性能十分有利，不过，索引碎片整理的确会对所有系统带来性能的提升。当创建数据库时，要谨慎考虑I/O子系统，并确保尽可能将日志文件和数据库数据文件隔离开。
决定何时进行索引碎片整理
决定何时进行索引碎片整理时，请考虑以下重要的建议：
标识有碎片的索引。
了解何种任务会从碎片整理中获利。
确定查询的I/O性能。
理解碎片整理带来的影响和SQL Server预读管理器。
下一节中，测试的结果可以用来帮助理解这些建议。
使用 DBCC SHOWCONTIG 来标识有碎片的索引
在决定何时进行碎片整理前，必须先确定那些索引有碎片。DBCC SHOWCONTIG可以用于衡量索引上的碎片程度和页密度级别(Page Density level)。
下面是运行 DBCC SHOWCONTIG 后的得到的示例信息:
DBCC SHOWCONTIG scanning ‘table_1’ table…
Table: ‘table_1’ (453576654); index ID: 1, database ID: 8
TABLE level scan performed.
– Pages Scanned…………………………..: 48584
– Extents Scanned…………………………: 6090
– Extent Switches…………………………: 12325
– Avg. Pages per Extent……………………: 8.0
– Scan Density [Best Count:Actual Count]…….: 49.27% [6073:12326]
– Logical Scan Fragmentation ………………: 10.14%
– Extent Scan Fragmentation ……………….: 32.74%
– Avg. Bytes Free per Page…………………: 1125.2
– Avg. Page Density (full)…………………: 86.10%
DBCC SHOWCONTIG scanning ‘table_1’ table…
Table: ‘table_1’ (453576654); index ID: 2, database ID: 8
LEAF level scan performed.
– Pages Scanned…………………………..: 41705
– Extents Scanned…………………………: 5221
– Extent Switches…………………………: 6094
– Avg. Pages per Extent……………………: 8.0
– Scan Density [Best Count:Actual Count]…….: 85.55% [5214:6095]
– Logical Scan Fragmentation ………………: 7.80%
– Extent Scan Fragmentation ……………….: 6.63%
– Avg. Bytes Free per Page…………………: 877.7
– Avg. Page Density (full)…………………: 83.20%
检查DBCC SHOWCONTIG运行后的结果时，需要特别留意Logical Scan Fragmentation和Average Page Density。Logic scan fragmentattion表示索引上乱序的百分比(注意: 该数值和堆和文本索引不相关。所谓堆表示一个没有聚集索引的表。)。Page density是索引叶级页填充程度的度量。请查找SQL Server联机帮助的“DBCC SHOWCONTIG”主题以获取更多信息。
分析DBCC SHOWCONTIG的输出结果
在分析DBCC SHOWCONTIG的输出结果时，请考虑下面问题：
碎片会影响I/O。因此，要集中关注较大的索引，因为这些索引被SQL Server放入缓存的可能性比较小。通过DBCC SHOWCONTIG得到的页数，可以估算出索引的大小(每页大小为8KB)。一般来说，没有必要关注那些碎片级别小于1,000页的索引。在测试中，包含超过10,000页的索引才会影响性能，特别是包含更多的页(超过50,000页)的索引，会引起最大的性能提升。
逻辑扫描碎片(logical scan fragmentation)值太高，会大大降低索引扫描的性能。在测试中，那些逻辑碎片大于10%的聚集索引，在碎片整理后性能得到了提升；对那些大于20%的聚集索引，性能提升尤其明显。因此关注那些逻辑碎片大于等于20%的索引。注意，对于堆(Index ID=0)来说，该标准是无意义的。
平均页密度(average page density)太低，将导致查询中需要读取更多的页。重新组织这些页，可以提高平均页密度，从而完成相同的查询只要读取较少的页。一般来说，在第一次载入数据后，表拥有较高的页密度。随着数据的插入，页密度会降低，从而带来叶级页拆分。检查平均页密度时，记住该值依赖于创建表时设置的填充因子取值。
虽然扫描密度(scan density)可以作为碎片级别的参考，不过当索引跨越多个文件时，该参考无效。因此，当检查跨越多个文件的索引时，扫描密度不应该被考虑。
监视碎片级别
有规律地监控索引的碎片级别是良好的实践习惯。SQL Server联机帮助的”DBCC SHOWCONTIG”主题中，有一个示例脚本，用于自动捕获和重建碎片程度较大的索引。建议每隔一段固定时间就使用带TABLERESULTS选项的DBCC SHOWCONTIG命令，将得到的结果保存在表内。这样做可以使你不受时间限制地监控碎片级别。另外，还建议在繁忙的服务器上使用带有WITH FAST选项的DBCC SHOWCONTIG命令。带有WITH FAST选项的DBCC SHOWCONTIG命令可以避免扫描索引的叶级页，从而是的执行比较快。不过，因为它不扫描叶级页，所以就无法报告页密度指标。
表1 显示在小规模和大规模DSS环境下，运行DBCC SHOWCONTIG命令所需的时间。每次测试中，都带 ALL_INDEXES 和 TABLERESULTS 选项。 表1 DBCC SHOWCONTIG 执行结果 DBCC SHOWCONTIG options Total number of index pages (all indexes) Run time (minutes)
Small-Scale Environment
Not using the WITH FAST option 1,702,889 5.02
Using the WITH FAST option 1,702,889 0.90
Large-Scale Environment
Not using the WITH FAST option 111,626,354 382.35
Using the WITH FAST option 111,626,354 48.73
理解哪些工作类型从索引的碎片整理中获益最多
当你决定进行索引的碎片整理时，理解有些工作类型将比另一些工作类型从碎片整理中获益更多这个结论，是十分重要的。碎片会对I/O带来负面影响。在大范围内用到索引页的查询受碎片的影响最大，从而也最能从碎片整理中获益。
测试利用两种数据库工作类型，比较了碎片整理前和整理后的性能。测试对象包括了具有代表性的OLTP和DSS数据库。OLTP数据库上的工作主要集中在特定范围的数据的更新(insert，update和delete)和有选择性的读取。而对于DSS系统，由于主要是读取工作类型，因此测试集中在多表联接的查询任务。特别是这些查询必须需要扫描到一个或更多的索引。测试结果表明碎片对OLTP工作影响极小，而对DSS系统则从碎片整理获益多多。
DBCC INDEXDEFRAG 和 DBCC DBREINDEX 命令常用来整理索引碎片。对这两个命令特定功能的更详细讨论后面会涉及到。
OLTP型工作量
测试中，OLTP数据库模拟数据仓库环境下的订单处理流程。工作量包括5个存储过程，涉及流程包括新建订单，订单状态查询，分发，库存状态，以及交付流程。存储过程中用到了插入，更新和删除语句以及有目的选择的SELECT查询。
图1 显示了使用DBCC INDEXDEFRAG 和 DBCC DBREINDEX 进行索引碎片整理前后，每个存储过程完成查询时性能的差异。
图1: 碎片整理前后，OLTP型工作环境下，每个存储过程的平均执行时间。值越小表示性能越好。点击看原图
——————————————————————————–
如图1所示，碎片整理前后，存储过程性能差别不是很大。因为存储过程中的基础查询有选择性地选取部分数据，因此索引碎片给性能带来的影响不大。图1还显示运行碎片整理工具的确降低存储过程的性能；不过在存储过程运行期间本身就会给性能带来10%－20%之间的波动。图1显示的差异也在这个范围之内。更重要的是，结果显示当碎片级别上升时，并未带来存储过程性能的下降。
DSS型工作量
测试中，DSS工作量包含22个由复杂SELECT语句构成的报表类型的查询。这些查询严格地以批处理方式在服务器端运行。所有查询包含一个或多个多表联接，大多数查询需要扫描很大范围的索引。
表2 记录测试中用到的索引的平均碎片和页密度级别。碎片级别通过下属行为组合得到：
以Bulk INSERT方式插入新数据到数据库，并模拟周期性地刷新数据。
删除某个范围内的数据。
按关键值执行一些更新操作，虽然这至少会影响碎片级别，不过和插入和删除操作相比，更新涉及到的记录相对还是比较少。
表2 小规模和大规模环境中平均逻辑碎片和页密度测试 Fragmentation level Average logical fragmentation (%) Average page density (%)
Small-Scale Environment
Low (1) 7.8 80.1
Medium (2) 16.6 68.1
High (3) 29.5 69.2
Large-Scale Environment
Low (1) 5.9 84.4
Medium (2) 13.8 70.3
DSS工作类型的测试结果和OLTP工作类型相差很大。在整理索引碎片后，性能提高很明显。因为此时工作量的性能强烈依赖于磁盘吞吐量(大多数查询会包含索引扫描)，因此该结果是可以预料到的。下面的图2和图3显示DSS型工作量在整理索引碎片前后的性能收益情况。如图中所示，性能从碎片整理中显著提升。
在小规模环境中，在碎片较低级别，性能提升了60％，而在碎片较高级别提升了460％。在大规模环境中，在碎片较低级别性能提升13%，在中等级别提升了40%。结果显示大规模环境中，碎片对性能影响影响相对较小，这是因为该环境从磁盘子系统的表现获益更多一些。更详细的讨论见本文后面的“碎片对磁盘吞吐量的影响和SQL Server预读管理器”章节。
图2: 小规模环境下，整个DSS型工作量在不同碎片级别下的运行时间。取值越低表示性能越好。 点击看原图
——————————————————————————–
图3: 大规模环境下，整个DSS型工作量在不同碎片级别下的运行时间。取值越低表示性能越好。点击看原图
——————————————————————————–
图2从数值上显示，在小规模环境下，DBCC INDEXDEFRAG的结果比DBCC DBREINDEX要好一些。不过，在大多数情况下，完全重建索引应该具有更好的性能。
在解释这些测试结果时，需要牢记以下几点：
图2和图3显示的结果意味着这是应用DBCC INDEXDEFRAG的“最佳”场合。测试中，DBCC INDEXDEFRAG运行在一个禁止的系统上；因此DBCC INDEXDEFRAG可以完全消除碎片。当DBCC INDEXDEFRAG运行在一个动态的系统上，即数据保持在线更新，DBCC INDEXDEFRAG会跳过那些被锁住的页。因此DBCC INDEXDEFRAG也许就无法完全消除碎片。要衡量DBCC INDEXDEFRAG发挥了多大作用，可以在DBCC INDEXDEFRAG后立刻运行DBCC SHOWCONTIG。
数据的分布情况会影响磁盘性能。小规模环境中，数据只分布在两个物理磁盘(磁盘容量加起来一共33.5GB)上，在数据库创建前，这两个磁盘是空的。数据库创建后，数据文件大小在22GB到30GB之间。当数据库创建时，数据分布在磁盘外围部分。DBCC INDEXDEFRAG整理碎片也是从最邻近原始数据的位置开始。因为DBCC DBREINDEX完全重建索引，在释放旧的索引页前，它必须首先给新索引页分配空间。分配新空间使得数据离原始数据位置较远，并且分布在磁盘内侧，因此造成I/O吞吐量有轻微的下降。在小规模环境下的benchmark测试中，这种下降表现为读取数据吞吐量下降15%。
剩余空间容量同样会影响DBCC DBREINDEX。如果没有大量连续的空闲空间，DBREINDEX会强迫使用数据文件中的空闲空间，从而导致索引重建时带有一小部分数量的逻辑碎片。关于DBCC DBREINDEX对剩余空间的需求的信息，见本文后面的”DBCC DBREINDEX”章节。
确定查询的I/O流量
因为具有大量I/O读写的查询从碎片整理中获益最多，所以讨论如何确定某个特定查询的I/O流量是必要的。SET STATISTICS IO命令可以报告完成一个特定查询时，服务器实例上的读取量和读取类型。可以在查询管理器中选择该命令开关为ON和OFF，使用方法如下：
SET STATISTIC IO ON
GO
SELECT * FROM table_1
GO
SET STATISTIC IO OFF
GO
输出示例
Table ‘table_1’.
Scan count 1,
logical reads 12025,
physical reads 0,
read-ahead reads 11421.
表3 关于SET STATISTIC IO 输出结果的描述 Value Description
Scan count Number of scans performed
logical reads Number of pages read from the data cache
physical reads Number of pages read from disk
read-ahead reads Number of pages placed into the cache for the query
通过physical reads和read-ahead reads值，可以对某个特定查询涉及的I/O量有一个估计。physical reads和read-ahead reads都表示从磁盘读取的页数。多数情况下，read-ahead reads比physical reads数值要大。
注意 在通过SQL Profiler 获取信息时，reads列表示的是逻辑读取量(logical reads)，而不是物理读取量(physical reads)。
除了重新对页进行排序，通过增加索引叶级页的页密度，索引的碎片整理还降低执行某个查询的I/O数量。页密度的提高导致完成相同的查询需要读取更少的页，从而提高性能。
理解碎片整理带来的影响和SQL Server预读管理器
碎片对大量读取磁盘边缘的操作带来负面影响。可以使用Windows性能监视器来获取这种影响的衡量。通过性能监视器，可以观测磁盘活动情况，并且有助于决定何时进行碎片整理。
为了理解为什么碎片对DSS型工作具有如此的影响，首先很重要的是要理解碎片是如何影响SQL Server预读管理器的。为了完成需要扫描一个或多个索引的查询，SQL Server预读管理器负责提前对索引页进行扫描，并且将额外的数据页放到SQL Server数据缓存中。根据基础页的物理顺序，预读管理器动态地调整读取量。当碎片较少时，预读管理器即时可以读取较大的数据块，更高效地利用I/O子系统。当数据产生碎片时，预读管理器只能读取较小的数据块。预读的数量虽然和数据的物理顺序无关，不过，因为较小的读取请求消耗更多的CPU/时钟，所以最终就会降低整个磁盘的吞吐量。
在所有情况下，预读管理器能提高性能；但是，当存在碎片，且预读管理器无法读取较大的数据块，整个磁盘的吞吐量就下降。通过检查性能监视器中的Physical Disk相关的计数器可以发现该现象。下表列举并描述了这些计数器。 表4 性能监视器中物理磁盘计数器 Physical Disk counter Description
Avg Disk sec/ Read 该计数器用于衡量磁盘延迟。测试显示当碎片出于很高水平(大于等于30%)时，会增加磁盘延迟。
Disk Read Bytes/ sec 该计数器能很好地衡量全面的磁盘吞吐量。一段时间内工作量的下降趋势可以用来表示碎片正在影响性能。
Avg Disk Bytes/ Read 该计数器用于衡量每个读取请求带来的数据读取量。当索引页连续，SQL Server预读管理器可以一次读取较大的数据块，对I/O子系统的利用效率较高。测试显示该计数器值和碎片数量间有着直接联系。当碎片级别上升，该值就下降，从而影响全面的磁盘吞吐量。
Avg Disk Read Queue Length 一般而言，该计数器为每两个物理磁盘上持续的平均数值。测试中，很可能由于较高的延迟和较低的全面磁盘吞吐量，使得该计数器随着碎片的增加而增加。
图4到图7显示在DSS型工作中，性能监视器报告的磁盘吞吐量和平均读取大小。
图4: 小规模环境下，DSS工作流的磁盘吞吐量。数值越高表示磁盘吞吐能力越好。点击看原图
——————————————————————————–
图5: 小规模环境下，DSS工作流的磁盘吞吐量。数值越高表示磁盘吞吐能力越好。点击看原图
——————————————————————————–
图6: 小规模环境下，DSS工作中每次磁盘读取的平均大小。数值越高表示每次读取字节数越多。点击看原图
——————————————————————————–
图7: 小规模环境下，DSS工作中每次磁盘读取的平均大小。数值越高表示每次读取字节数越多。点击看原图
——————————————————————————–
上面的图显示碎片对磁盘性能的影响趋势。虽然使用DBCC DBREINDEX和DBCC INDEXDEFRAG得到的结果不同，但是注意在所有系统上，都得到了一致的结果，即每次读取的平均大小和整体磁盘吞吐量随着碎片的增加而降低。正如你所见的，整理所有碎片对磁盘吞吐量提高极大。
每次读取的平均大小还可以用来展示，碎片是如何影响预读管理器读取较大数据块的能力的。这点需牢记在心，不过，平均读取数量较大并不总是意味着整体吞吐量较高。平均读取量大表示数据传输中，CPU负荷较小。当索引没有碎片时，读取64KB大小数据的速度和读取256KB大小数据的速度几乎一样。这个结论在那些数据分布在多个磁盘上的大型系统而言，尤其如此。这些普遍和特殊的结论，会根据不同的系统，因为各种各样的原因(例如，不同的I/O子系统，工作类型差异，数据在磁盘的分布特征等等)而不同。当监视系统时，寻找那些持续时间较长的磁盘吞吐量和读取数量的下降阶段。这些阶段以及DBCC SHOWCONTIG命令提供的信息，可以用于帮助来确定什么时候该进行索引的碎片整理了。
测试结果显示碎片也会带来磁盘延迟，不过，至于那些最高级别的碎片才会对磁盘延迟带来巨大的负面影响，并且也只在小规模环境下才有此现象。在大规模环境下，由于SAN提供了很高的I/O性能，因此磁盘延迟值十分小而从来不会带来问题。
DBCC DBREINDEX vs. DBCC INDEXDEFRAG
除了使用CREATE INDEX命令来删除或者重新创建索引，还可以使用DBCC DBREINDEX和DBCC INDEXDEFRAG命令来帮助维护索引。
DBCC DBREINDEX
DBCC DBREINDEX用于在指定的表上重建一个或多个索引。DBCC DBREINDEX是离线操作方式。当该操作运行时，涉及到的表就无法被用户访问。DBCC DBREINDEX动态地重建索引。没有必要知道参与重建的表结构到底如何，是否用主键或者唯一性约束等信息；重建的时候会自动管理的。DBCC DBREINDEX完全重建索引，也就是说，将页密度级别恢复到最初(默认)的填充因子水平；当然你也可以选择页密度的新值。从内部运行看，DBCC DBREINDEX和手工用T-SQL语句来运行删除然后重新创建索引十分相似。
下面两点是DBCC DBREINDEX比DBCC INDEXDEFRAG优越的地方：
DBCC DBREINDEX在重建索引过程中，自动重建统计；这将显著提高工作性能。
DBCC DBREINDEX可以运行在多处理器环境下，利用多处理器的优势，当重建较大和碎片厉害的索引时，速度可以十分快。
DBCC DBREINDEX的所有工作是一个单一的，原子事务。必须完成创建新的索引并替换旧索引，然后旧索引页被释放。完成重建需要数据文件中有足够的空余空间。如果空余空间不够，DBCC DBREINDEX要么无法重建索引，要么会产生大于0的逻辑碎片。所需空余空间视情况而定，取决于事务中要创建的索引数目。对于聚集索引而言，一个不错的指导公式为：所需空余空间 = 1.2 * (平均行大小) * (行数量)。
对于非聚集索引，可以通过计算非聚集索引包含的每行平均大小(非聚集索引包含关键字长度 + 聚集中的索引关键字或者row ID长度)乘以行数量得到所需空间。如果对整个表进行索引重建，需要为聚集索引和非聚集索引留出足够的空间。同样，如果重建不唯一非聚集索引，也需要为聚集和非聚集索引留出空余空间。因为SQL Server必须为每行创建唯一标识，因此非聚集索引是隐式重建的。使用DBCC DBREINDEX时，较佳的做法是指定那些索引需要整理。这样做可以使得对操作有更多的控制力，以及避免不必要的麻烦。
DBCC INDEXDEFRAG
DBCC INDEXDEFRAG用于对指定的索引进行重建。和DBCC DBREINDEX类似，也不需顾及表的基础结构；不过，DBCC INDEXDEFRAG无法用一个语句对所有的索引进行重建。对于每个希望进行碎片整理的索引，都必须运行一次DBCC INDEXDEFRAG。
和DBCC DEREINDEX不同的是，DBCC INDEXDEFRAG是在线操作的；因此在整理索引碎片时，仍然可以访问被整理到的表和索引。另外一个和DBCC INDEXDEFRAG很不同的地方是，DBCC INDEXDEFRAG可以中止和重新开始而不丢失任何工作信息。整个DBCC DBREINDEX操作作为一个原子事务运行。这意味着如果中止DBCC DBREINDEX操作，整个操作会回滚，继续的话必须重新开始。但是，如果中止DBCC INDEXDEFRAG，任务会立刻中止并不会丢失已经完成的任务，因为DBCC INDEXDEFRAG每个工作单位是独立的事务。
DBCC INDEXDEFRAG包括两个阶段：
压缩页并试图根据索引创建时指定的填充因子来调整页密度。DBCC INDEXDEFRAG会根据最初的填充因子，尽可能提高页密度级别。而DBCC INDEXDEFRAG不会，但是它会将那些高于最初填充因子的页密度降低。
通过移动页来使得物理顺序和索引叶级页的逻辑顺序一致，从而整理索引碎片。这个工作由一系列独立的很小的事务来完成；因此DBCC INDEXDEFRAG对整体系统性能，影响很小。图8显示DBCC INDEXDEFRAG的碎片整理阶段中页移动情况。
图8: DBCC INDEXDEFRAG的数据文件页移动情况
——————————————————————————–
DBCC INDEXDEFRAG并不会帮助整理分散插入到数据文件中的索引，这种分散插入称为Interleave。同样，DBCC INDEXDEFRAG也不对扩展页碎片进行整理。当索引扩展页(扩展页=8页)中的数据并不连续的时候，出现Interleave，此时多个扩展页的数据在文件中是交叉状态。因为即使逻辑顺序和物理顺序一致情况下，所有的索引页也不见得一定是连续的，因此即使没有逻辑碎片情况下，Interleave也会存在。
虽然上面提到了DBCC INDEXDEFRAG的限制，但是测试显示，DBCC INDEXDEFRAG对性能的改善和DBCC DBREINDEX一样有用。实际上，从测试结果可以看出，即使重建了索引，使得Interleave最小，这部分优化并不会对性能带来显著提升。减少逻辑碎片级别这部分优化才对性能提升最多。这就是为什么检查索引碎片时，建议将重点放在逻辑碎片整理和页密度碎片上的原因。表5总结了DBCC DBREINDEX和DBCC INDEXDEFRAG之间的差别。 表5 DBCC DBREINDEX 和 DBCC INDEXDEFRAG的比较 Functionality DBCC DBREINDEX DBCC INDEXDEFRAG
Online/Offline Offline Online
Faster when logical fragmentation is: High Low
Parallel processing Yes No
Compacts pages Yes Yes
Can be stopped and restarted without losing work completed to that point No Yes
Able to untangle interleaved indexes May reduce interleaving No
Additional free space is required in the data file for defragmenting Yes No
Faster on larger indexes Yes No
Rebuilds statistics Yes No
Log space usage High in full recovery mode (logs entire contents of the index), low in bulk logged or simple recovery mode (only logs allocation of space) Varies based on the amount of work performed
May skip pages on busy systems No Yes
性能: DBCC DBREINDEX vs. DBCC INDEXDEFRAG
测试显示，无论是DBCC DBREINDEX还是DBCC INDEXDEFRAG，都可以有效地整理索引碎片，并将页密度恢复到初始填充因子规定的页密度附近。基于这些结果，下面需要决定什么时候应用哪种整理方式。
如果允许有一段时间进行离线索引重建，DBCC DBREINDEX一般来说比DBCC INDEXDEFRAG要快。DBCC DBREINDEX可以充分利用多处理器系统的平行性能。DBCC INDEXDEFRAG用于对生产环境干扰不大，对工作性能影响不大的场合。测试显示，即使同时几个DBCC INDEXDEFRAG并行工作，对性能下降的影响也从来不会超出10%。但是，这也同样使得DBCC INDEXDEFRAG针对较大的索引整理时，需要很长的时间才能完成。而且，工作时间的长短还依赖于当时在服务器上运行的访问工作。
图9为DBCC INDEXDEFRAG和DBCC DBREINDEX的性能比较。图中的数据为小规模环境下，对所有索引进行整理的时间(大规模环境下结果类似，DBCC INDEXDEFRAGY运行时间为DBCC INDEXREINDEX的8倍)。当碎片级别增加，索引大小增加时，DBCC DBREINDEX可以比DBCC INDEXDEFRAG执行得更快。
图9: 小规模环境下，整理所有索引碎片所需时间点击看原图
——————————————————————————–
日志考虑: DBCC DBREINDEX vs. DBCC INDEXDEFRAG
最后要考察的问题是使用DBCC INDEXDEFRAG和DBCC DBREINDEX时，写入事务日志的数据量差别。DBCC INDEXDEFRAG中写入事务日志的数据量依赖于碎片的级别和完成的工作量。测试显示，当数据库完全恢复模式下，DBCC INDEXDEFRAG写入事务日志的数据量远远小于DBCC DBREINDEX。DBCC INDEXDEFRAG的日志数据量，可以变化很大。这是因为DBCC INDEXDEFRAG完成的碎片整理工作量由页移动数量和必要的页压缩数量决定。因为DBCC INDEXDEFRAG工作由一系列小事务组成，因此可以通过备份来回收DBCC INDEXDEFRAG使用的那部分日志空间。
从日志使用的角度看，DBCC DBREINDEX和DBCC INDEXDEFRAG稍有不同；在大批量(bulk)日志恢复模式下，日志量具有最大的差异。在完全恢复模式下，DBCC DBREINDEX对每个索引页有日志镜像，在日志恢复模式下，就没有。因此，在完全恢复模式下，DBCC DBREINDEX所需的日志空间大约等于索引页数量乘以8KB。可以通过DBCC SHOWCONTIG来确定给定索引的页数量。在大规模环境下，运行DBCC DBREINDEX时，建议将恢复模式改为日志恢复模式。而在运行结束后，再改为完全恢复模式。
注意：由于大规模环境下，回滚事务需要付出巨大的时间代价，因此理解日志的需求很重要。
图10 显示在小规模和中度碎片级别环境下，DBCC INDEXDEFRAG和DBCC DBREINDEX日志空间使用的差别。虽然DBCC INDEXDEFRAG日志空间波动很大，不过测试结果可以体现DBCC DBREINDEX和DBCC INDEXDEFRAG的一般性差别。
图10: 对DSS型数据库所有索引碎片整理时，DBCC INDEXDEFRAG和DBCC DBREINDEX所用的整个日志空间点击看原图
——————————————————————————–
结论
对于不同的工作类型，索引碎片整理具有十分不同的影响。某些应用可以从碎片整理中获取很大的性能提升。理解应用特征，系统性能和SQL Server提供的碎片统计信息，是正确决定何时进行碎片整理的关键。SQL Server提供一些命令来完成索引碎片整理。本文可以帮助我们来决定何时以及如何整理索引碎片，从而使性能得到最大的改善。
更多信息
要得到关于监视，分析和改善工作性能的更多信息，请访问如下资源：
Microsoft Knowledge Base article 243589: “HOW TO: Troubleshoot Slow-Running Queries on SQL Server 7.0 or Later” at http://support.microsoft.com/default.aspx?scid=KB;en-us;243589&sd=tech
Microsoft SQL Server 2000 Performance Tuning Technical Reference, Microsoft Press, ISBN: 0-7356-1270-6
“Windows 2000 IO Performance” from Microsoft Research at http://research.microsoft.com/BARC/Sequential_IO/Win2K_IO.pdf
附录 A : 测试环境
本文的测试使用下面的硬件和软件环境：
Microsoft Software
Microsoft Windows 2000 Data Center (Service Pack 3)
Microsoft SQL Server 2000 Enterprise Edition (Service Pack 2)
Hardware Platform
Small-Scale Environment:
Dell PowerEdge 6450
4 Intel Pentium III Xeon 550 MHz processors
4 GB RAM
Large-Scale Environment:
Dell PowerEdge 8450
8 Intel Pentium III Xeon 550 MHz processors
16 GB RAM
Storage
Small-Scale Environment:
1 Dell PowerVault 660f, with 2, 18 GB 10,000 RPM disks
Total Disk Space = 36 GB (Raid 0)
Large-Scale Environment:
1 Hitachi Freedom Storage Lightning 9960 system, with 192, 73 GB, 10,000 RPM disks
Total Disk Space = 13 TB (~6 TB after RAID 1+0 and further striping/slicing)
0 条评论。