DSS型工作量
测试中,DSS工作量包含22个由复杂Select语句构成的报表类型的查询。这些查询严格地以批处理方式在服务器端运行。所有查询包含一个或多个 多表联接,大多数查询需要扫描很大范围的索引。
表2 记录测试中用到的索引的平均碎片和页密度级别。碎片级别通过下属行为组合得到:
- 以Bulk Insert方式插入新数据到数据库,并模拟周期性地刷新数据。
- 删除某个范围内的数据。
- 按关键值执行一些更新操作,虽然这至少会影响碎片级别,不过和插入和删除操作相比,更新涉及到的记录相对还是比较少。
DSS工作类型的测试结果和OLTP工作类型相差很大。在整理索引碎片后,性能提高很明显。因为此时工作量的性能强烈依赖于磁盘吞吐量(大多数查询 会包含索引扫描),因此该结果是可以预料到的。下面的图2和图3显示DSS型工作量在整理索引碎片前后的性能收益情况。如图中所示,性能从碎片整理中显著 提升。
在小规模环境中,在碎片较低级别,性能提升了60%,而在碎片较高级别提升了460%。在大规模环境中,在碎片较低级别性能提升13%,在中等级别 提升了40%。结果显示大规模环境中,碎片对性能影响影响相对较小,这是因为该环境从磁盘子系统的表现获益更多一些。更详细的讨论见本文后面 的“碎片对磁盘吞吐量的影响和SQL Server预读管理器”章节。
图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 |
Table ‘table_1′. |
通过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到图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的碎片整理阶段中页移动情况。
DBCC INDEXDEFRAG并不会帮助整理分散插入到数据文件中的索引,这种分散插入称为Interleave。同样,DBCC INDEXDEFRAG也不对扩展页碎片进行整理。当索引扩展页(扩展页=8页)中的数据并不连续的时候,出现Interleave,此时多个扩展页的数 据在文件中是交叉状态。因为即使逻辑顺序和物理顺序一致情况下,所有的索引页也不见得一定是连续的,因此即使没有逻辑碎片情况下,Interleave也 会存在。
虽然上面提到了DBCC INDEXDEFRAG的限制,但是测试显示,DBCC INDEXDEFRAG对性能的改善和DBCC DBREINDEX一样有用。实际上,从测试结果可以看出,即使重建了索引,使得Interleave最小,这部分优化并不会对性能带来显著提升。减少逻 辑碎片级别这部分优化才对性能提升最多。这就是为什么检查索引碎片时,建议将重点放在逻辑碎片整理和页密度碎片上的原因。表5总结了DBCC DBREINDEX和DBCC INDEXDEFRAG之间的差别。
性能: 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执行得更快。
日志考虑: 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的一般性差别。
结论
对于不同的工作类型,索引碎片整理具有十分不同的影响。某些应用可以从碎片整理中获取很大的性能提升。理解应用特征,系统性能和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)
Host bus adapters (HBA)
8 Emulex LP9002L PCI Host Bus Adapters
Firmware 3.82A1
Port Driver v5-2.11a2
Fabric switch
1 McData Switch, 1 GB
Storage management software
Hitachi Command Control Interface (CCI)
Hitachi ShadowImage
Databases
Representative OLTP and DSS databases