SAPR3性能优化指南(1)

1、MS SQL Server 7.0 的 SAP R/3 性能优化指南 1998 年 11 月发行 读者 本文档所提供的信息可以帮助 SAP R/3 数据库管理员理解 Microsoft® SQL Server 7.0 的各个方面。这些方面可以经过优化，从而在与 SAP R/3 环境相关的特有数据库工作负荷条件下提供最佳性能。 虽然本文档是为 SAP R/3 站点量身定制的，但是一定要注意本文档中描述的 SQL Server 功能和优化技巧不仅仅适用于 SAP R/3。大型到超大型数据库 (VLDB) 需要支持大量用户连接和大的工作负荷，工作于这一环境中的数据库管理员将从本文的信息中获

2、益匪浅。 引言 本篇有关性能优化的文档将讨论 SAP R/3 环境中的 SQL Server 7.0 最佳配置。本指南分为四个逻辑部分。第一部分，讨论与 Microsoft Windows NT® Server 相关的配置选项。第二部分，描述 SAP R/3 环境中 SQL Server 的重要配置选项。前面这两个部分直截了当，其中还包含了在 SQL Server 的初始配置过程中几分钟就可以完成的一些步骤。第三部分，讨论 SQL Server 的索引设计，因为它与 SAP R/3 相关。索引分析往往是一个极其棘手的过程，为使数据库性能最佳需要持续不断地执行索引分析。 Microso

3、ft TechNet 中的“Microsoft SQL Server 7.0 性能优化指南”应该作为本文第三部分的补充读物。该指南综合讨论硬件 I/O 性能、索引设计以及 SQL Server 性能优化工具。第四部分，讨论 R/3 数据库中的 SQL Server 文件和文件组的最佳应用。 Windows NT 配置 Windows NT 页面文件的大小应该至少比服务器上安装的 RAM 大三倍，而且要至少有 10 亿字节 (1 GB)。 设置页面文件大小 1. 在开始菜单上，指向设置，然后单击控制面板。 2. 双击系统，然后双击性能选项卡。 3. 单击更改，然后在起始大小 (MB) 框中，输入

4、页面文件的大小（按兆字节 (MB) 计）。 4. 单击确定。 通常，VMM（虚拟内存管理器）已经根据 SQL Server 安装的默认设置进行了适当的配置。 检查和/或配置 VMM 设置 1. 在开始菜单上，指向设置，然后单击控制面板。 2. 双击网络，然后单击服务选项卡。 3. 双击服务器，选择最大化网络应用程序吞吐量，然后单击确定。 配置最小影响的屏幕保护程序和墙纸 1. 在开始菜单上，指向设置，然后单击控制面板。 2. 双击显示，然后单击背景选项卡。 3. 选定图案为（无），并选定墙纸为（无）。 4. 单击应用，然后单击屏幕保护程序选项卡。 5. 在屏幕保护程序下，选定 Blank Sc

5、reen，然后选定密码保护。 6. 单击应用。 一些多处理器服务器可以将联网 I/O 请求动态分发到最不繁忙的处理器。该硬件功能有助于防止在服务于许多联网请求的系统中出现处理器瓶颈和不良网络性能。该功能通常称为对称中断分发，使用该功能可以改进可扩展性能并防止出现在其它处理器还有剩余容量时某一个处理器却成为瓶颈的情况。它在 Pentium 处理器系列的 Windows NT 4.0 HAL（硬件抽象层）中可以找到。Windows® 2000 也支持该功能。 不同处理器操作平台使用不同方法分发中断。网卡的中断分发是由每个处理器操作平台的 HAL 来控制的。HAL 实施的中断方案取决于处理

6、器的性能。某些处理器包含了中断控制硬件，如高级可编程中断控制器 (APIC)。APIC 允许处理器将中断路由到计算机上的其它处理器。有关特定处理器操作平台所使用的分发方法的详细信息，请咨询操作平台供应商。 在默认情况下，Windows NT 4.0 不使用对称中断分发，而是将与网卡 (NIC) 相关的延迟进程调用 (DPC) 活动分配给系统中编号最大的处理器。在安装有多个 NIC 且 NIC 活动的系统中，每个额外的 NIC 的活动都指派到下一个编号最大的处理器。 如果处理器频繁以百分之百的容量（性能监视器所显示的 Processor: % Processor Time = 100%）操作而且

7、一半以上时间用于为 DPC 提供服务上（即如果 Processor: % DPC Time > 50%），那么可以通过调整 ProcessorAffinityMask 来提高性能。 警告 注册表编辑器使用不当会导致严重的问题，有可能需要重新安装操作系统。使用注册表编辑器要特别小心。Microsoft 不保证能够解决因注册表编辑器使用不当而导致的问题。建议您在执行修改前备份注册表内容，这样在注册表修改出现问题时就能够还原内容。有关备份和还原注册表信息的说明可以在注册表编辑器的联机帮助中找到。 在能够进行对称中断分发的多处理器服务器上，将 Windows NT 注册表中 ProcessorA

8、ffinityMask 数值项的值设置为零。这样就会将网络 I/O 请求动态分发到最有能力处理请求的处理器中。ProcessorAffinityMask 位于：HKEY_LOCAL_MACHINE System CurrentControlSet Services NDIS Parameters。 启动注册表编辑器以设置 ProcessorAffinityMask 1. 在开始菜单上，单击运行。 2. 键入 regedt32。 在注册表编辑器中找到适当关键字 1. 在窗口菜单中选择 HKEY_LOCAL_MACHINE。 2. 在注册表编辑器左边窗格中，双击 SYSTEM。 3. 双击 Cur

9、rentControlSet，双击 Services，双击 NDIS ，然后双击 Parameters。 将 ProcessorAffinityMask 设置为零 1. 在注册表编辑器右边窗格中，双击 ProcessorAffinityMask。 2. 键入 0（零），然后单击确定。 3. 在注册表菜单上，单击退出。 SQL Server 配置 SQL Server 内存的推荐设置取决于 R/3 实例对数据库服务器的使用情况。如果 SQL Server 是作为专用数据库服务器而运行，建议 SQL Server 按照默认值动态调整它所需要的内存。 R/3 实例最小值最大值专用数据库服务器默认值默

10、认值更新实例已安装 RAM 的 40%已安装 RAM 的 65%中心实例已安装 RAM 的 45%已安装 RAM 的 45%在有 2 GB RAM 的中心实例上设置内存的例子 (Enterprise Manager) 1. 在右边窗格中，双击 SQL Server Group 图标。 2. 双击 R/3 数据库服务器的 SQL Server 图标。 3. 单击 Memory 选项卡，然后单击 Use a fixed memory size (MB)。 4. 将 Use a fixed memory size (MB) 下的滑块移动到 900。 5. 选定 Reserve physical me

11、mory for SQL Server ，单击 Apply ，然后单击 OK。 完成 SQL Server 的内存配置之后，建议使用 set working set size 选项保留与 SQL Server 内存设置相等的物理内存空间。设置该选项意味着 Windows NT 不转换出 SQL Server 页。 配置设置工作集大小选项的例子 (Enterprise Manager) 1. 在右边窗格中，双击 SQL Server Group 图标。 2. 双击 R/3 数据库服务器的 SQL Server 图标。 3. 单击 Memory 选项卡，然后选定 select Reserve ph

12、ysical memory for SQL Server。 4. 单击 Apply，然后单击 OK。 SAP 测试显示出，在大多数 R/3 数据库服务器操作环境下网络数据包大小为 8,192 字节时性能最佳。该选项需要使用 SQL Server Query Analyzer 来设置。 设置网络数据包大小 (Query Analyzer) 1. 键入 exec sp_configure 'network packet size', 8192。 2. 键入 reconfigure with override。 3. 按 CTRL + E 执行以上命令。 在专用服务器上，建议使用 S

13、QL Server priority boost 选项。 设置优先级提升选项 (Enterprise Manager) 1. 在右边窗格中，双击 SQL Server Group 图标。 2. 双击 R/3 数据库服务器的 SQL Server 图标。 3. 单击 Processor 选项卡，然后在 Processor Control 框中，选定 Boost SQL Server priority on Windows NT。 建议将 SQL Server index create memory 选项配置为 16 MB。该选项需要使用 SQL Server 查询分析器来设置。 设置索引创建内存

14、选项 (Query Analyzer) 1. 键入 exec sp_configure 'index create memory', 16000。 2. 键入 reconfigure with override。 3. 按 CTRL + E 执行以上命令。 禁用 VBHDR、VBMOD 和 VBDATA 表的页面锁定 (Query Analyzer) 1. 在查询窗口键入以下命令： exec sp_indexoption 'VBHDR','allowpagelocks','false' exec sp_indexoption &#

15、39;VBMOD','allowpagelocks','false' exec sp_indexoption 'VBDATA','allowpagelocks','false' 2. 按 CTRL + E 执行以上命令。 如果数据库服务器上的所有处理器的利用率都很高（性能监视器表明在多处理器服务器上所有处理器的处理器利用率全部大于 95%），那么就有必要打开 SQL Server lightweight pooling。在所有处理器都非常接近完全利用时，lightweight pooling 可以帮助恢复大

16、约 5% 到 7% 的 CPU。 打开 SQL Server lightweight pooling (Enterprise Manager) 1. 在右边窗格中，双击 SQL Server Group 图标。 2. 双击 R/3 数据库服务器的 SQL Server 图标。 3. 单击 Processor 选项卡，选定 select Use Windows NT Fibers，然后单击 Apply。 4. 当提示重新启动 SQL Server 时，单击 Yes，然后单击 Ok。 SQL Server affinity mask 配置选项提供了不能执行 SQL Server 线程的特定处理器的

17、规范。最好运用 SQL Server 关系掩码的默认设置，即零。关系掩码的设置为零表明允许在所有处理器上执行 SQL Server 线程。几乎在所有情况下，该设置都会使性能最佳，因为它避免了在其它处理器上有剩余容量的情况下而在某一台处理器上的 SQL Server 连接非常繁忙的情况。Microsoft 的 IT 机构和参与 SQL Server 7.0 Early Adopter 计划的 SAP R/3 用户已运用了关系掩码的默认设置，结果性能良好。 索引设计与维护 “Microsoft SQL Server 7.0 性能优化指南”提供了有关 SQL Server 索引和性能优化的重要信息。

18、此文档可在“查找详细信息”处找到。 大型的 SAP R/3 安装会有一些包含非常多行的 SQL Server 表。有了大的表，索引对数据库 I/O 性能影响极大。 在单个数据库行或少量数据库行上搜索并执行的操作，应该有一个基于提供最高级别选择性的一列或几列所定义的非聚集或聚集索引。这样，SQL Server 查询处理器和存储引擎就能够将检索行所需的 I/O 控制到最小化。例如，如果必须定期从非常大的 Orders 表中根据 orderid 来检索单个定单记录，那么基于 orderid 列定义索引可以加快查询的速度就是有意义的。 在多数数据库行上搜索和执行的操作应该基于定义范围扫描的列定义聚集索

19、引。从非常大的七月份 Orders 表中检索所有定单的查询就是范围扫描的一个例子。在这种情况下，Orders 表的日期列就是聚集索引的最佳列。 即将发行的 SAP R/3 4.5B 版本将带有一个影响 SQL Server 聚集索引选择灵活性的重要功能。在 4.5B 版中，R/3 数据字典对除主键列以外的列聚集索引有被动支持。被动支持的意思是：在数据库表已改变以致于聚集索引从主键移至了另一列或列集的情况下，SAP R/3 数据字典会识别并记录 SQL Server 聚集索引的位置。创建聚集索引需要使用与 R/3 工具对应的 SQL Server 工具。但是，聚集索引的位置在创建之后不会在数据库

20、转换和 R/3 版本升级过程中丢失。 4.5B 以后的 SAP R/3 版本中有可能包含 SQL Server 聚集索引的积极支持。积极支持的意思是：除前面所讲到的 R/3 Data Dictionary供的支持外，R/3 工具还将支持在 SQL Server 表上的列（除主键列以外）的聚集索引创建。 聚集索引支持中的这些更改与希望改进其 R/3 报告查询性能的 R/3 数据库管理员的想法有很大出入。运行 SAP R/3 的大型公司的月末和季度报告可能会使用数据库服务器上的范围扫描。通常情况下会是这样一种情况：在大表上所执行的范围扫描将不以与定义表的主键相同的列为基础。目前，SAP R/3 S

21、QL Server 数据库实施将所有表上的主键配置为聚集主键。某些情况对测试非主键组成部分且常常为了报告目的而用于大型表的列的聚集索引的使用非常有利。ALTER TABLE 命令用于将聚集主键更改为非聚集主键。 以下索引分析示例讨论了这样一种情况，即将聚集主键更改为非聚集主键以便聚集索引可以基于另一列进行定义并预排将聚集主键更改为非聚集主键所涉及的步骤，这一点是有意义的。 有关聚集和非聚集索引选择的详细信息，应参阅“Microsoft SQL Server 7.0 性能优化指南”。 SAP R/3 在 ST04 事务处理中提供 MSSTATS 工具，可以帮助 R/3 数据库管理员跟踪数据库服务

22、器上所执行的 SQL Server 存储过程的资源占用。所有 R/3 与数据库服务器的正常交互作用都使用存储过程执行。 MSSTATS 提供有助于根据资源使用区分存储过程的信息。MSSTATS 返回的信息的例子包括：存储过程的调用次数、用于调用存储过程的平均和最长时间、存储过程调用所返回的行平均数和总行数、存储过程是否使用了游标、存储过程用于提取的时间与空闲时间等等。 MSSTATS 提供了用于确定在 R/3 数据库服务器上所运行的费用最高的存储过程的重要工具。性能分析应集中于这些费用最高的查询。 使用以下 SQL Server 表示例可以模仿与许多 SAP R/3 表非常类似的数据模式。两个

23、示例查询将使用该此测试表进行分析。其目的是说明如何在 R/3 数据库服务器环境中很好地利用 SQL Server 索引。 示例数据以下脚本创建名为 saptest1 的表并在其中加入 100,000 个记录。第一个名为 col1 的列没有选择性。每一行的 col1 数值是一样的 ('000')。这是为了模拟 SAP R/3 中非常常见的 MANDT 列，通常它不太具有选择性。第二个名为 col2 的列设计为带有一些选择性，因为每隔 100 行就插入了一个 'a' 数值。 SQL Server modulo ('%') 操作符用于检测每第 100

24、行的插入。最后一个名为 col3 的列的选择性极高。每一行都有唯一的 col3 数值。 创建示例数据 (Query Analyzer) 1. 在查询窗口键入以下命令： create table saptest1 ( col1 char(4) not null default '000', col2 char(4) not null default 'zzzz', col3 int not null, filler char(300) default 'abc' ) declare counter int set nocount on set co

25、unter = 1 while (counter <= 100000) begin if (counter % 1000 = 0) PRINT 'loaded ' + CONVERT (VARCHAR(10),counter) + ' of 100000 record' if (counter % 100 = 0) begin insert saptest1 (col2,col3) values ('a',counter) end else insert saptest1 (col3) values (counter) set counte

26、r = counter + 1 end 2. 按 CTRL + E 执行命令。 示例索引SQL Server 主键的 SAP R/3 默认配置是将主键设置为聚集主键。这在大多数情况下都提供了极好的性能。但是，可能有一些独立的表，将聚集索引放在除包含表的主键列以外的某一列上会对这些表很有用。 为 saptest1 定义的聚集主键列在 R/3 数据库环境下是很典型的，因为它将完整的非选择性列 col1（模拟典型 R/3 环境的 MANDT ）放在索引的开始处。 非聚集索引 nkey2 模仿典型的 R/3 索引就在于它是多列索引。 创建示例索引 (Query Analyzer) 1. 在查询窗口键入

27、以下命令： alter table saptest1 add constraint sapt_c1 PRIMARY KEY clustered (col1,col2,col3) create index nkey2 on saptest1(col2,col3) 2. 按 CTRL + E 执行命令。 示例查询select * from saptest1 where col3 = 5000 查询 1 根据与名为 col3 的列的匹配值从测试表中提取一行： select * from saptest1 where col2 = 'a' 查询 2 是一个范围扫描，它根据与名为 col

28、2 的列的匹配值从表中提取 1,000 行。 在 SQL Server Query Analyzer 中报告查询 I/O 统计SQL Server Query Analyzer 能够从每个在 Query Window 执行的查询中提供有用价值的 I/O 统计。这在 SQL Server 文档中一般称为 Statistics IO。要启用该功能，可以执行一个 T-SQL 命令，或设置 Query Analyzer 菜单选项。 通过 T-SQL 命令使用 SET STATISTICS IO 选项 (Query Analyzer) 1. 在查询窗口键入以下命令： set statistics io

29、on 2. 按 CTRL + E 执行命令。 通过菜单选项使用 SET STATISTICS IO 选项 (Query Analyzer) 1. 在 Query Analyzer 菜单上，单击 Query，然后单击 Current Connection Options。 2. 选定 Show Stats I/O 然后单击 Apply。 3. 单击 OK。 第一组索引的结果单行提取数据：select * from saptest1 where col3 = 5000基于文本的 ShowPlan 输出： |-Bookmark Lookup(BOOKMARK:(Bmk1000), OBJECT:(p

30、ubs.dbo.saptest1) |-Index Scan(OBJECT:(pubs.dbo.saptest1.nkey2), WHERE:(saptest1.col3=5000) 等量图形显示计划输出。 Stats I/O 的查询结果集和数据库 I/O (Query Analyzer)： col1 col2 col3 filler 000 a 5000 abc (1 row(s) affected) Table 'saptest1'.Scan count 1, logical reads 240, physical reads 0, read-ahead reads 0.

31、Showplan 输出显示查询处理器需要执行非聚集索引 nkey2 的索引扫描。索引扫描是指：SQL Server 需读取 nkey2 的 B 树结构的叶级的部分或全部，以便找到键值 5000。此操作要求 SQL Server 数据高速缓存中的 240 个 I/O。也就是说要从 SQL Server 高速缓冲存储器中读取 240 个 8 KB 的页面。物理读取和预读读取所显示的零说明要为此查询检索数据，没有必要从磁盘中进行读取。 备注 I/O 统计是专用于运行的。当查询在某次运行中，所有读取都会从高速缓冲存储器中出来，并作为逻辑读取而计数，而在其它运行中，完全一样的查询有可能需要使用预读读取和

32、/或物理读取以满足查询的 I/O 要求。I/O 统计的这种变化可能由许多因素造成的，最常见的就是其它连接可能正在执行查询并将数据放入高速缓冲存储器（它取代了被监视查询正在使用的数据页）。当分析查询时，在打开 I/O 统计的情况下数次运行查询并比较其结果是很有帮助的。 标为 Cost 的 Graphical Showplan 中标明的百分数表示在每个查询的特定部分上所花费的时间所占执行查询花费的总时间的百分数。 范围扫描：select * from saptest1 where col2 = 'a'基于文本的 ShowPlan 输出： |-Clustered Index Scan

33、(OBJECT:(pubs.dbo.saptest1.sapt_c1), WHERE:(saptest1.col2='a') 等量图形显示计划输出： 统计 I/O 的查询结果集和数据库 I/O (Query Analyzer)： col1 col2 col3 filler 000 a 100 abc 000 a 200 abc 000 a 300 abc . . . 000 a 99800 abc 000 a 99900 abc 000 a 100000 abc (1000 row(s) affected) Table 'saptest1'.Scan count

34、 1, logical reads 4500, physical reads 1, read-ahead reads 4010. Showplan 输出表明查询处理器需要执行聚集索引 sapt_c1 的索引扫描。索引扫描的意思是：SQL Server 需要读取 sapt_c1 B 树结构（即表的实际行）的叶级的的部分或全部以便找到键值 'a'。此操作需要从高速缓冲存储器中读取 4,500 个 8K 的页。 预读读取的数字 4,010 表明 SQL Server 使用 Read-Ahead Manager 在 64 KB 组块中读取了 4,010 个 8 KB 页面。预读读取比物

35、理读取效率更高。物理读取的数字 1 表明 SQL Server 需要从磁盘中读取一个 8 KB 页面作为单一 8 KB 页面。由于预读读取和物理读取都是物理磁盘读取，因此它们比高速缓冲存储器的读取的逻辑读取速度慢得多。这就是为什么您的首要性能优化的目的应该是限制物理磁盘读取，并尽量满足高速缓冲存储器的所有数据库页读取。 索引更改建议索引设计的目的是将 I/O 减到最少以实现性能最优。在前面所示的几个例子中，出现了索引扫描。与索引扫描相比，执行索引搜索的效率更高。要想作到这一点，将注意力集中于查询的 WHERE 子句是很重要的。 在单行提取情况下，col3 就是正在搜索的列。由于 col3 有极

36、佳的可选择性，因此它是非聚集索引的合适候选项。对 I/O 性能最好的做法就是在 col3 上定义非聚集，使 col3 成为唯一列或索引中的第一列。 在范围扫描的情况下，col2 就是正在搜索的列。Col2 的选择性还可以（100,000 行中带有 'a' 数值的有 1,000 行）。因为需要所有带 'a' 的行，所以 col2 是聚集索引的合适候选项。 ALTER TABLE 语句用于将聚集主键更改为非聚集主键。 警告 在任何情况下都不要更改与表的主键关联的列。如果更改可以保证性能优势，可以将聚集主键更改为非聚集主键，也可以为同时作为主键一部分的列创建新的非聚集

37、索引，但是在任何情况下主键列必须都保持一致。记住这一点极其重要。 备注 在 SAP R/3 环境中，建议使用 SE11 事务定义非聚集、非主键索引，以便在 R/3 数据字典中维护索引信息。有关使用 SE11 事务创建索引的详细信息，请参见 SAP 联机帮助，其位于 SE11 事务菜单项 Help -> Extended Help。单击 Indexes，然后按照联机指示执行。 执行索引更改 (Query Analyzer) 1. 在查询窗口键入以下命令： alter table saptest1 drop constraint sapt_c1 alter table saptest1 ad

38、d constraint sapt_c1 PRIMARY KEY NONCLUSTERED (col1,col2,col3) create clustered index ckey1 on saptest1(col2) create index nkey1 on saptest1(col3) 2. 按 CTRL + E 执行命令。 第二组索引的结果改进索引的单行取数据：select * from saptest1 where col3 = 5000基于文本的 ShowPlan 输出： |-Bookmark Lookup(BOOKMARK:(Bmk1000), OBJECT:(pubs.dbo.

39、saptest1) WITH PREFETCH) |-Index Seek(OBJECT:(pubs.dbo.saptest1.nkey1), SEEK:(saptest1.col3=5000) ORDERED) 等量图形显示计划输出。 统计 I/O 的查询结果集和数据库 I/O (Query Analyzer)： col1 col2 col3 filler 000 a 5000 abc (1 row(s) affected) Table 'saptest1'.Scan count 1, logical reads 5, physical reads 0, read-ahead

40、 reads 0. Showplan 现在表明 SQL Server 正在对索引 nkey1 使用索引搜索而不是索引扫描。索引搜索是指：SQL Server 能够快速浏览 nkey1 B 树结构（而不是象前面例子中的扫描索引的叶级），并使用书签查找来找到与键值 5000 关联的数据行。这一从索引扫描到索引搜索的改变对 I/O 性能有极大的影响。要执行查询，仅需要 SQL Server 数据高速缓存中的 5 个 I/O，而前一个例子中，需要 240 个。 改进索引的范围扫描：select * from saptest1 where col2 = 'a'基于文本的 ShowPlan

41、 输出： |-Clustered Index Seek(OBJECT:(pubs.dbo.saptest1.ckey1), SEEK:(saptest1.col2='a') ORDERED) 等量图形显示计划输出。 统计 I/O 的查询结果集和数据库 I/O (Query Analyzer)： col1 col2 col3 filler 000 a 100 abc 000 a 200 abc 000 a 300 abc . . . 000 a 99800 abc 000 a 99900 abc 000 a 100000 abc (1000 row(s) affected) Ta

42、ble 'saptest1'.Scan count 1, logical reads 48, physical reads 0, read-ahead reads 0. Showplan 再次表明 SQL Server 正在对索引 ckey1 使用索引搜索而不是索引扫描。在聚集索引搜索情况下，不需要进行书签查找，因为聚集索引 B 树的叶级已包含表数据。索引扫描到索引搜索的改变再次对 I/O 性能产生重大而又积极影响。要提取 1,000 行，仅需要对 SQL Server 高速缓冲存储器的 48 次读取，而在前一个例子中则需要 4,500 个 I/O。I/O 的数量已大大减少，因

43、此，完成该查询不再需要物理磁盘 I/O，因为所需要的页在 SQL Server 数据高速缓存中已经有了。物理读取和预读读取的数字都是零，这就说明了这一点。切记：预读读取是每个读取为 64 KB 的物理磁盘读取，物理读取是每个读取为 8 KB 的物理磁盘读取。 索引分析的观察与结论从前面的例子中可以清楚看出：索引设计对 SQL Server 查询中的 I/O 性能起着举足轻重的作用。范围扫描的例子特别用来说明：在 R/3 环境中，还可能有适合于除主键列以外的列的聚集索引的情况。特别是对于要在其上执行报告的大型表来说，这是很有可能的。例如，如果大型表根据日期列进行报告，但是日期列未包含在聚集主键中

44、，那么对于聚集索引测试日期列的使用就非常有用。 在 SQL Server 7.0 中，聚集索引中所包含的列和字节越多，该特定表的非聚集索引越大。这是因为该表的聚集索引和非聚集索引都使用构成聚集索引的列。非聚集索引包含群集键并使用群集键定位 rowdata。如果表上没有聚集索引，则此情况不适用。相反，在这种情况下表是作为堆集来管理的。有关详细信息，请参见 SQL Server 联机图书。 既然非聚集索引在非聚集 B 树结构中包含群集键，要注意两种影响。第一，一定要保持较小的聚集索引规模，因为它不仅影响聚集索引的大小和性能，而且还影响该表中的所有非聚集索引的大小和性能。第二，一定要记住非聚集索引包

45、含群集键，因为在非聚集索引包含满足查询（群集键除外）所需的所有列的情况下，查询处理器可以使用键值列帮助涵盖查询。 涉及群集键的涵盖查询示例以下是一个简单的例子，它说明了在群集键仅包含非聚集索涵盖给定查询所需的其它信息的情况下，查询处理器将非聚集索引用作涵盖索引，并无须使用书签查找从表中提取数据。有关涵盖查询与索引设计的详细信息，请参见“Microsoft SQL Server 7.0 性能优化指南”。 建立以下表 (Query Analyzer) 1. 在查询窗口键入以下命令： create table saptest2 (col1 int, col2 char(4) default '

46、;a', filler char(300) default 'zzzz') declare counter int set counter = 1 while (counter <= 1000) begin insert saptest2 (col1) values (counter) set counter = counter + 1 end insert saptest2 values (1001,'sap','R/3') create clustered index sap_CK1 on saptest2(col1) crea

47、te nonclustered index sap_NCK1 on saptest2(col2) 2. 按 CTRL + E 执行命令。 显示并比较以下两种查询计划 (Query Analyzer) 1. 在查询窗口键入以下命令： select * from saptest2 where col2 = 'sap' select col1,col2 from saptest2 where col2 = 'sap' 2. 分别选择每个查询，然后按 CTRL + L 显示图形显示计划。 使用第二个查询不需要书签查找，因为非聚集索引暗含群集键，因此涵盖了查询。 当解决

48、R/3 程序长期存在的问题时，要牢记一种潜在的操作方法，即使用 sp_recompile 标记存储过程以便快速重编译。sp_recompile 命令执行占用时间极少而且很有用。sp_recompile 命令迅速标记存储过程，以便为存储过程生成新的查询计划，它反映表的数据、索引及统计的最新情况。 备注 在大多数 R/3 正常操作情况下，没有必要运行 sp_recompile，因为在对其有利的情况下，SQL Server 会自动重新编译存储过程。但是在 R/3 环境中也有过这样的情况，即 SAP 和 Microsoft 已发现在有长期存在且性能不良的更新和批处理过程运行的表中运行sp_recomp

49、ile 是很有好处的。 使用 sp_recompile 最简便的方法之一是将表名称作为命令参数提交。这样就会为重新编译标记所有与表名称关联的存储过程。例如，如果 CCMS 显示在 VBRP 表上操作的更新过程的执行时间特别长，那么就有必要在该表上运行 sp_recompile。 执行 sp_recompile 的示例 (Query Analyzer) 1. 键入 exec sp_recompile 'VBRP'。 2. 按 CTRL + E 执行命令。 SQL Server 7.0 提供列和索引统计的自动生成与维护。统计辅助查询处理器确定最佳查询计划。默认情况下，为所有索引创建

50、了统计，并且在为列统计可能有用和优化器可能需要对其进行推测的列编译查询时，SQL Server 会自动创建单列统计。 为避免对未使用的统计的长期维护，SQL Server 为自动创建的统计（仅指那些不是索引创建的附带统计）制定有效时间。在几次自动更新之后，列统计不再被更新，而是被丢弃。如果以后需要，可以再次创建。统计更新与创建间的开销上没有什么大的差异。此有效时间的制定不影响用户创建的统计。 为使性能最佳，建议使用自动统计。统计的自动创建和更新是 SQL Server 7.0 的默认配置。该建议不适用于表 VBHDR、VBMOD 和 VBDATA。对于这些表，建议关闭自动统计。从本质上讲，VB

51、HDR、VBMOD 和 VBDATA 的大小极不稳定，也就是说它们可能频繁地从空变成非常大，然后又变回空。R/3 仅通过主键访问这些表。这些表的其它统计不会有什么用，因为使用主键的同一查询计划也用于每个访问。基于上述这些原因，在这些表中关闭自动统计功能是有利的。 以下命令集将防止以后在 VBHDR、VBMOD 和 VBDATA 表上的统计生成。 要关闭 VBHDR、VBMOD 和 VBDATA 的自动统计 (Query Analyzer) 1. 在查询窗口键入以下命令： exec sp_autostats VBHDR,'OFF' exec sp_autostats VBMOD,

52、'OFF' exec sp_autostats VBDATA,'OFF' 2. 按 CTRL + E 执行命令。 VBHDR、VBMOD 和 VBDATA 表上的现有统计可以通过以下命令从数据库中删除。 除去现有统计 (Query Analyzer) 1. 使用 sp_helpindex 命令确定要除去的统计名。例如，要显示任何 VBMOD 上的现有统计名称，在查询窗口中键入下列命令： exec sp_helpindex VBMOD 2. 按 CTRL + E 执行命令。查询分析器结果窗格中的 index_name 列将显示所有索引与统计的名称。 3. 在 dr

53、op statistics 命令中使用统计名称。例如，要除去名为 _WA_Sys_VBELN_0AEA10A3 的统计，在查询窗口中键入以下命令： drop statistics VBRP._WA_Sys_VBELN_0AEA10A3 4. 按 CTRL + E 执行命令。 5. 对表 VBMOD、VBHDR 和 VBDATA 上的所有统计重复执行 1 至 4 步骤。 DBCC SHOWCONTIG 命令用于评估表中出现的物理碎片（如果有）等级。 运行 DBCC SHOWCONTIG 的示例 (Query Analyzer) 1. 在查询窗口键入以下命令： declare id int sel

54、ect id = object_id('saptest1') dbcc showcontig (id) 2. 按 CTRL + E 执行命令。 3. 应该出现以下输出： DBCC SHOWCONTIG scanning 'saptest1' table. Table:'saptest1' (933578364); index ID:1, database ID: 5 TABLE level scan performed. - Pages Scanned.: 4167 - Extents Scanned.: 521 - Extent Switche

55、s.: 520 - Avg.Pages per Extent.: 8.0 - Scan Density Best Count:Actual Count.: 100.00% 521:521 - Logical Scan Fragmentation .: 11.21% - Extent Scan Fragmentation .: 0.96% - Avg.Bytes Free per Page.: 198.6 - Avg.Page Density (full).: 97.55% DBCC execution completed.If DBCC printed error messages, cont

56、act your system administrator. Scan Density 和 Extent Scan Fragmentation 有助于评定磁盘上表的组织情况。百分之百 Scan Density 是最佳的可能值，因为它表明使用中的扩展的最理想数量（例如，每个扩展八个页面的情况下，扩展完全得到利用）。Extent Scan Fragmentation 通过表明表关联的扩展是否曾经物理移动至磁盘序列之外，从而提供页拆分的附加信息。Extent Scan Fragmentation 仅在表定义了聚集索引的情况下才是有用的信息。 Avg. Page Density (full) 以百分数

57、表示每个 SQL Server 数据页上的平均数据量。有时，这个百分数也称为数据页的充满度。高的百分数说明每个 8 KB 读取带入 SQL Server 高速缓冲存储器的数据多。总而言之，高的百分数说明高速缓存将包含更多的有用信息。举个例子，考虑一下如果 DBCC SHOWCONTIG 显示在您的数据库中有好几个平均页密度为 50% 的表的情况。如果这些表含有检索的多数数据，那么 SQL Server 高速缓冲存储器将包含多数仅包含 50% 有用数据的数据页。这就是说 1 GB 高速缓冲存储器仅包含 500 MB SQL Server 数据。如果将读取到高速缓冲存储器的所有表的平均页面密度提高

58、到接近 100%，那么一个 1 GB 的高速缓冲存储器将包含接近 1 GB 的 SQL Server 数据，这样就好多了。 如果访问某表的查询的应答时间增长到无法忍受的程度，则在该表上运行 DBCC SHOWCONTIG 命令。如果 Avg. Pages per Extent 大大小于 8.0， Extent Scan Fragmentation大于 10-20%或 Avg. Page Density (full) 大大小于 100% 时，有必要考虑重建表的聚集索引，以便将表中的数据物理重排到物理连续的扩展中。重建聚集索引还提供了选择更完整的页面填充的选项，以便每 8 KB 页压缩更多的数据。 磁盘上有经过良好压缩而连续的数据对 I/O 性能是有利的，因为 SQL Server 可以在从此表中提取数据时利用有序 I/