Microsoft SQL Server 2000 索引碎片整理最佳实践(上)

Microsoft SQL Server 2000 索引碎片整理最佳实践<!-- DATA[原文：Microsoft SQL Server 2000 Index Defragmentation Best Practices<br-->

来源：Microsoft TechNet
作者：Mike Ruthruff
时间：February 2003

Summary As Microsoft SQL Server 2000 maintains indexes to reflect up&#100;ates 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。这个比较包括不同的数据库和硬件环境的测试结果。关于测试环境，请见章节&#34;小规模环境 vs. 大规模环境&#34;和附录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后执行Up&#100;ate STATISTICS来增加其影响。

了解碎片

当索引所在页面的基于主关键字的逻辑顺序，和数据文件中的物理顺序不匹配时，碎片就产生了。所有的叶级页包含了指向前一个和后一个页的指针。这样就 形成一个双链表。理想情况下，数据文件中页的物理顺序会和逻辑顺序匹配。整个磁盘的工作性能在物理顺序匹配逻辑顺序时将显著提升。对某些特定的查询而言， 这将带来极佳的性能。当物理排序和逻辑排序不匹配时，磁盘的工作性能会变得低效，这是因为磁头必须向前和向后移动来查找索引，而不是只象某个单一方向来搜 索。碎片会影响I/O性能，不过对于位于SQL Server数据缓冲内的数据页而言，碎片并不会带来任何影响。

当索引第一次创建时，没有或者只有极少碎片。随着时间推移，插入，更新和删除数据，和这些数据相关的索引上的碎片就增加了。为了整理碎片，SQL Server提供如下命令：

Cr&#101;ate INDEX后的Dro&#112; INDEX命令
不带Dro&#112;_EXISTING选项的Cr&#101;ate INDEX命令
DBCC INDEXDEFRAG
DBCC DBREINDEX

本文用 DBCC INDEXDEFRAG 和 DBCC DBREINDEX 命令来进行测试。这些命令都可以在在线和离线场景下执行。DBCC DBREINDEX按照Cr&#101;ate INDEX的方式创建索引；因此DBCC DBREINDEX的执行结果和用Cr&#101;ate INDEX命令的结果很相似。上面所有这些命令的测试结果和功能描述会在本文后面提到。

整理碎片前需要考虑的因素

系统资源问题

在索引碎片整理之前，要确认系统任何性能问题和系统资源限制无关。关于这方面的详细讨论已经超出了本文的范围，不过有些更常见的资源问题和I/O子 系统性能，内存使用以及CPU使用率相关。关于分析这些类型资源问题的更深入讨论，请见本文最后的“更多的信息”章节。

物理磁盘碎片

在某些系统上，磁盘碎片会带来很糟的性能。要确定是否存在磁盘碎片，可以使用Microsoft Windows自带的系统工具，或者第三方提供的工具来分析SQL Server所在的分区。对于常规的I/O子系统上的规模较小的数据库，建议在运行索引碎片整理工具前，先进行磁盘碎片整理。而对于更智能的磁盘子系统上 的规模较大的数据库，例如SAN(存储区域网络 storage area networks)环境，磁盘碎片整理就不是必要的。

执行情况较差的查询

当考察任何性能相关问题时，你必须能识别出那些查询执行效率较差。这里讨论的一些信息在后面也会用到，这些信息用于决定那些索引碎片将被整理。

可以使用SQL Profiler(事件探查器)来识别执行效率差的查询(关于这方面更多的信息，请参考SQL Server联机帮助的&#34;SQL Profiler&#34;主题)。运行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 &#39;table_1&#39; table…
Table: &#39;table_1&#39; (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 &#39;table_1&#39; table…
Table: &#39;table_1&#39; (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联机帮助的&#34;DBCC SHOWCONTIG&#34;主题中，有一个示例脚本，用于自动捕获和重建碎片程度较大的索引。建议每隔一段]
]>