软件测试中数据仓库的 RDBMS 性能优化指南

非聚集索引最适于根据特定的键值，从大型 SQL Server 表中提取少数几个具有良好选择性的行。如前所述，非聚集索引是由 8 KB 索引页形成的二进制树。索引页二进制树的底层或叶层包含组成该索引的列中的所有数据。在使用非聚集索引根据键值的匹配项从表中检索信息时，会遍历索引的 B 树，直到在索引的叶层找到键的匹配项。如果需要表中不构成索引的列，指针就会跳转。这种指针跳转将有可能需要针对磁盘执行非顺序 I/O 操作。它甚至可能需要从另一磁盘中读取数据，尤其是在表及其伴随的索引 B 树很大时。如果多个指针指向同一个 8 KB 数据页，对 I/O 性能的影响就会比较小，因为只需将该页读入数据缓存一次。如果 SQL 查询涉及到用非聚集索引进行搜索，则对于对该查询返回的每一行，至少需要一次指针跳转。

注意 由于指针每次跳转都会带来与之相关的开销，因此非聚集索引更适于处理从表中只返回一行或几行的查询。聚集索引更适于处理需要一系列行的查询。

下图说明了非聚集索引的存储。请注意，添加的叶层指向对应的数据页。在使用非聚集索引而不是聚集索引来访问表数据时，添加的指针跳转就会在那里起作用。有关非聚集索引的更多信息，请参阅“SQL Server 联机丛书”。

如果您的浏览器不支持嵌入式框架，请单击此处在单独的页中查看。

唯一索引

聚集索引和非聚集索引均可用于强制表内的唯一性，方法是在现有表上创建索引时指定 UNIQUE 关键字。确保表内唯一性的另一种方法是使用 UNIQUE 约束。如同唯一索引，UNIQUE 约束强制一组列中各值的唯一性。实际上，UNIQUE 约束的赋值自动创建基础唯一索引，以利于强制该约束。由于唯一性可以作为 CREATE TABLE 语句的一部分来加以定义和记录，因此，UNIQUE 约束通常优先于单独唯一索引的创建。

计算列上的索引

SQL Server 2000 引入了在计算列上创建索引的功能。如果查询是以一般方式提交的，而且会例行提供计算列，但管理员不愿意只是为了允许创建索引而在实际的表列中持续存放数据，在这样的情况下，使用这项功能就会很方便。在此情况下，只要计算列满足索引所需的全部条件，就可以通过引用计算列来创建索引。其他限制包括，计算列表达式必须有确定性、精确，并且不得取值为 text、ntext 或 image 数据类型。

确定性

如果要在视图或计算列上创建索引，视图和计算列均无法唤醒调用没有确定性的用户定义函数。所有函数要么有确定性，要么没有确定性：

• 无论何时使用一组特定的输入值调用有确定性的函数，这些函数总是会返回相同的结果。
• 每次用特定的一组输入值调用没有确定性的函数时，这些函数返回的结果可能各不相同。

例如，DATEADD 内置函数有确定性，因为对于通过该函数的三个输入参数传入的一组给定参数值，它始终返回可预测的结果。GETDATE 没有确定性。虽然始终用相同的参数值唤醒调用 GETDATE 函数，但每次执行调用返回的值各不相同。

精确

如果满足下列条件，说明计算列表达式是精确的：

• 它不是 float 数据类型的表达式。
• 它不在自己的定义中使用 float 数据类型。例如，在下面的语句中，列 y 是 int 并且有确定性，但不精确。

  CREATE TABLE t2 (a int, b int, c int, x float,
y AS CASE x
WHEN 0 THEN a
WHEN 1 THEN b
ELSE c
END)


COLUMNPROPERTY 函数的 IsPrecise 属性报告 computed_column_expression 是否精确。

注意 任何 float 表达式均被视为不精确，不能作为索引的键；float 表达式可以在索引视图中使用，但不能用作键。这一规则同样适用于计算列。任何函数、表达式、用户定义函数或视图定义，只要包含任何 float 表达式，包括逻辑表达式（比较），均被视为没有确定性。

如果在计算列或视图上创建索引，先前能够正确执行的 INSERT 或 UPDATE 操作现在可能无法执行。在计算列导致算术错误时，可能会发生此类无法执行的情况。例如，虽然下表中的计算列 c 导致算术错误，但 INSERT 语句会起作用：

CREATE TABLE t1 (a int, b int, c AS a/b)
GO
INSERT INTO t1 VALUES ('1', '0')
GO


如果改为在创建该表之后在计算列 c 上创建索引，相同的 INSERT 语句将会失败。

CREATE TABLE t1 (a int, b int, c AS a/b)
GO
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1.c
GO
INSERT INTO t1 VALUES ('1', '0')
GO


索引视图

索引视图是为了实现快速访问而将其结果持续存放于数据库内并创建索引的视图。与任何其他视图一样，索引视图也依靠基表来提供视图数据。此类相关性意味着，如果更改为索引视图提供数据的基表，索引视图可能变得无效。例如，重命名为视图提供数据的列会使该视图无效。为了避免此类问题，SQL Server 支持创建具有架构绑定的视图。架构绑定禁止对表或列进行任何会使视图无效的修改。使用视图设计器创建的索引视图自动获得架构绑定，因为 SQL Server 要求该索引视图具有架构绑定。架构绑定并不是说您不能修改视图；它的意思是您不能按更改视图结果集的方式来修改基础表或视图。另外，就像计算列上的索引一样，索引视图也必须有确定性、精确，且不得包含 text、ntext 或 image 等列。

索引视图在基础数据不经常更新的情况下效果最佳。维护索引视图的成本可能高于维护表索引的成本。如果基础数据更新频繁，索引视图数据的维护成本就可能超过使用索引视图带来的性能收益。

索引视图改进了以下几类查询的性能：

• 处理多行的联接和聚合。
• 许多查询经常执行的联接和聚合操作。

  例如，在某个记录清单的 OLTP 数据库中，预计许多查询要联接 Parts、PartSupplier 和 Suppliers 表。虽然执行这一联接的每个查询不一定都会处理许多行，但成千上万个查询的联合处理加在一起仍然是非常庞大的操作。因为不太可能经常更新这些关系，所以通过定义存储联接结果的索引视图，即可改进整个系统的总体性能。

• 决策支持工作负荷。
• 分析系统的特点是存储不经常更新的概要数据、聚合数据。许多决策支持查询的特点是进一步聚合数据和联接许多行。

索引视图通常不会改进以下几类查询的性能：

• 经常写入的 OLTP 系统。
• 经常更新的数据库。
• 不涉及聚合或联接的查询。
• 键基数程度高的数据聚合。基数程度高意味着该键包含许多不同的值。唯一键具有基数可能的最高程度，因为每个键的值各不相同。索引视图通过减少查询必须访问的行的数量来改进性能。如果视图结果集的行数量几乎与基表的行数量相同，那么使用该视图几乎就无任何性能收益可言。例如，对于具有 1,000 行的表考虑使用此查询：

  SELECT PriKey, SUM(SalesCol)
FROM ExampleTable
GROUP BY PriKey


  如果表键的基数为 100，使用此查询的结果生成的索引视图就只有 100 行。使用该视图的查询平均需要的读取次数为读取基表次数的十分之一。如果该键是一个唯一的键，而该键的基数是 1000，视图结果集将返回 1000 行。使用该索引视图，而不直接读取基表，查询不会带来任何性能改进。

• 展开联接，这些联接是结果集大于基表内原始数据的视图。

您设计的索引视图应能满足多个操作。因为，即使未在 FROM 子句中指定索引视图，优化程序也能使用索引视图，所以设计完好的索引视图可以加快许多查询的处理速度。例如，考虑在以下视图上创建索引：

CREATE VIEW ExampleView (PriKey, SumColx, CountColx)
AS
SELECT PriKey, SUM(Colx), COUNT_BIG(Colx)
FROM MyTable
GROUP BY PriKey


该视图不仅满足直接引用视图列的查询，而且可以用来满足查询基础基表并且包含 SUM(Colx)、COUNT_BIG(Colx)、COUNT(Colx) 和 AVG(Colx) 等表达式的查询。所有此类查询的速度都会更快，因为它们只需检索视图中的少数几列，而不必读取基表中的所有列。

在视图上创建的第一个索引必须是唯一的聚集索引。创建了唯一的聚集索引之后，您可以创建其他非聚集索引。视图上的索引命名规则与表上的索引命名规则相同。唯一不同的是表名会替换为视图名。

如果除去视图，视图上的所有索引也将被除去。如果除去聚集索引，视图上的所有非聚集索引也将被除去。非聚集索引可被单独除去。除去视图上的聚集索引会删除存储的结果集，优化程序会恢复以标准视图的处理方式来处理该视图。

虽然在 CREATE UNIQUE CLUSTERED INDEX 语句中仅指定构成聚集索引键的列，但视图的完整结果集存储于数据库中。就像在基表的聚集索引中一样，聚集索引的 B 树结构只包含键列，但数据行包含视图结果集中的所有列。

注意 SQL Server 2000 的任何版本均可创建索引视图。在 SQL Server 2000 企业版中，索引视图将由查询优化程序自动考虑。要在所有其他版本中使用索引视图，必须使用 NOEXPAND 提示。

覆盖索引

覆盖索引是根据满足 SQL 查询（在选择条件和 WHERE 谓词两方面满足）所需的所有列建立的非聚集索引。覆盖索引可以节省大量 I/O，从而极大地提升了查询的性能。但是，您需要平衡考虑新索引的创建成本（及其相关的 B 树索引结构维护成本）与覆盖索引带来的 I/O 性能收益。如果覆盖索引将会极大地提升某个查询或某组查询的性能，而这些查询经常在 SQL Server 上运行，那么就值得创建覆盖索引。

下面的示例说明如何使用覆盖索引交集：

Create index indexname1 on table1(col2,col1,col3)
Select col3 from table1 where col2 = 'value'


执行上述查询时，只会读取较少的索引页，所以能够迅速从基础表中检索到所需的值，从而非常高效地解析该查询。通常，如果覆盖索引较小（就索引中所有列的字节数与该表的单行内的字节数相比较而言），并且使用覆盖索引的查询确实会经常执行，那么适合使用覆盖索引。

索引选择

对索引的选择会极大地影响生成的磁盘 I/O 数量，进而极大地影响性能。非聚集索引适合于检索少量的行，而聚集索引适合于区间扫描。下列原则有助于选择要使用的索引类型：

• 尽量精简索引（行数和字节数保持最小）。该原则尤其适用于聚集索引，因为非聚集索引将使用聚集索引作为定位行数据的方法。
• 在非聚集索引中，选择性非常重要。如果在只有几个唯一值的大表上创建非聚集索引，使用该非聚集索引不会在数据检索过程中节省大量 I/O。实际上，使用该索引生成的 I/O 很可能远远多于只是执行连续表扫描生成的 I/O。适合使用非聚集索引的对象包括发票号、唯一的客户号、社会保障号和电话号码。
• 对于涉及到区间扫描的查询，或在经常使用列联接其他表时，聚集索引比非聚集索引的效果好。原因是聚集索引对表数据进行物理排序，允许键值上存在连续的 64 KB I/O。适合使用聚集索引的对象包括省、公司分支机构、销售日期、邮政编码和客户地区。

  针对一个表只能创建一个聚集索引；如果典型查询经常从表的某一列提取大量连续区间，而表的其他列包含唯一值，则在第一列上使用聚集索引，在包含唯一值的列上使用非聚集索引。在每个表上尝试选择用来创建聚集索引的最佳列时，要问的关键问题是：“是否会有大量查询需要根据该列的顺序来提取大量的行？”。答案视每个用户环境的具体情况而定。某个公司可能根据日期区间进行大量查询，而另一个公司可能根据银行分支机构的区间进行大量查询。

索引的创建和并行操作

在 SQL Server 2000 企业版和 Developer Edition 中，针对创建索引而建立的查询计划允许在配有多个微处理器的计算机上进行并行、多线程的索引创建操作。

SQL Server 在为创建索引操作确定并行度（同时运行的单个线程的总数）时使用的算法与为其他 Transact-SQL 语句确定并行度时使用的算法相同。唯一不同是创建索引的 CREATE INDEX、CREATE TABLE 或 ALTER TABLE 语句不支持 MAXDOP 查询提示。索引创建的最大并行度取决于最大并行度服务器配置选项，但您不能为各索引创建操作设置不同的 MAXDOP 值。

在 SQL Server 建立创建索引查询计划时，并行操作的数量以下面的最小值为准：

• 计算机中微处理器或 CPU 的数量。
• 最大并行度服务器配置选项中指定的数量。
• 尚未超过 SQL Server 线程工作阈值的 CPU 数量。

例如，某台计算机配有八个 CPU，但最大并行度选项设置为 6，那么为创建索引生成的并行线程不会超过六个。如果在建立索引创建执行计划时，计算机中的五个 CPU 超过 SQL Server 工作的阈值，执行计划将只指定三个并行线程。

并行索引创建的主要阶段包括：

• 协调线程对表进行快速随机的扫描，以估计索引键的分发情况。协调线程建立键边界，键边界的作用是创建多个键区间，键区间的数量与并行操作度相等，预计每个键区间内包含的行数大致相同。例如，如果表中有四百万行，而最大并行度选项设置为 4，协调线程将确定分隔四个行集的键值，每个行集包含一百万行。
• 协调线程根据并行操作度分派同等数量的多个线程，然后等待这些线程完成工作。每个线程使用筛选器扫描基表，筛选器只在分配给线程的区间内检索具有键值的行。每个线程为其键区间内的行建立索引结构。

在所有并行线程完成工作后，协调线程将多个索引子单元连接到单个索引中。单独的 CREATE TABLE 或 ALTER TABLE 语句可以具有多个需要创建索引的约束。虽然可以在配有多个 CPU 的计算机上并行执行每个索引创建操作，但此处所述的多个索引创建操作仍然按序执行。

索引维护

在数据库中创建索引时，查询使用的索引信息存储在索引页中。连续索引页之间通过指针一页一页相互链接在一起。对影响索引的数据进行更改时，数据库中的索引信息会被拆散。重建索引将重新组织索引数据的存储（如果为聚集索引，还将重新组织表数据的存储），以便删除碎片。这样可以减少为获得请求数据所需的页读取的数目，从而改进磁盘性能。

插入活动或更新会修改聚集索引的搜索键值，在执行大量的插入活动或更新时，即会出现碎片。因此，为了防止拆分索引页和数据页，您应该尝试在索引页和数据页上保留一定的开放空间，这一点很重要。如果索引页或数据页不能再存放任何新行，而且由于该页中定义的数据的逻辑排序，需要将某一行插入该页，则会拆分该页。出现此情况时，SQL Server 需要对一整页的数据进行分割，将大约一半的数据移到新页上，这样，新旧两页都能保留一定的开放空间。因为这样会耗用系统资源和时间，所以建议不要经常这样做。

最初建立索引时，SQL Server 尝试将索引的 B 树结构放在物理上连续的页上；这样就能在使用连续 I/O 扫描索引页时优化 I/O 性能。在发生拆分页和需要将新页插入索引的逻辑 B 树结构时，SQL Server 必须分配新的 8 KB 索引页。如果在硬盘的其他位置发生此情况，将会破坏索引页的物理连续特性。这样会导致 I/O 操作的执行从连续切换为不连续，而且还会极大地降低性能。通过重建索引来恢复索引页的物理连续顺序，应该能解决页拆分过多的问题。同样的行为还可能在聚集索引的叶级别上发生，从而影响表的数据页。

在系统监视器中，尤其要注意“SQL Server：访问方法 – 页拆分/秒”。该计数器的非零值表示正在进行页拆分，应使用 DBCC SHOWCONTIG 作进一步分析。

DBCC SHOWCONTIG 命令也可用来揭示表上是否已进行了过多的页拆分。扫描密度是 DBCC SHOWCONTIG 提供的关键指标。该值应尽量接近 100%，越接近越好。如果该值大大低于 100%，请考虑对出现问题的索引运行维护。

DBCC INDEXDEFRAG

一个索引维护选项要使用 SQL Server 2000 中引入的新语句 (DBCC INDEXDEFRAG)。DBCC INDEXDEFRAG 可以为表和视图上的聚集索引和非聚集索引整理碎片。DBCC INDEXDEFRAG 在索引的叶级别整理碎片，因此各页的物理顺序与叶节点从左至右的逻辑顺序一致，从而改进了索引扫描性能。

DBCC INDEXDEFRAG 还压缩索引的各页，它会考虑在创建索引时指定的 FILLFACTOR。由压缩创建的空页将被删除。

如果索引跨越多个文件，DBCC INDEXDEFRAG 一次为一个文件整理碎片。索引页不会在文件之间迁移。DBCC INDEXDEFRAG 每隔五分钟向用户报告一次预计已完成的百分比。在执行过程中，您随时都可以终止 DBCC INDEXDEFRAG，已完成的所有工作会被保留。

与 DBCC DBREINDEX（或一般的索引建立操作）不同，DBCC INDEXDEFRAG 是联机操作。它不会长期保持锁定，因而不会阻止运行查询或更新。为相对而言没有碎片的索引整理碎片可以比建立新索引快，因为整理碎片所需的时间与碎片量相关。为非常零碎的索引整理碎片的时间可能比重建索引的时间要长得多。此外，不论数据库恢复模型设置如何，始终完全记录碎片整理情况（请参阅 ALTER DATABASE）。为非常零碎的索引整理碎片所生成的日志甚至可能比记录整个索引创建过程所生成的日志还多。不过，由于碎片整理是作为一系列小事务执行的，因此，如果经常进行日志备份，或者恢复模型设置为 SIMPLE，则不需要大日志。

另外，如果两个索引在磁盘上交错存放，则不适合使用 DBCC INDEXDEFRAG，因为 INDEXDEFRAG 会打乱索引页的位置。要改进索引页的聚集，请重建索引。出于相同的原因，DBCC INDEXDEFRAG 无法更正页拆分。对于已按反映搜索键的连续顺序分配的索引页，它实质上会进行重新排序。索引页的次序可能因多种原因而变得不正确，这些原因包括：无序数据装载、过多的插入、更新、删除活动，等等。

“SQL Server 联机丛书”中提供了一段示例代码，您只需对该代码稍加修改，即可使用它来自动执行各种索引维护任务。该示例说明如何用一种简单的方法来对数据库中碎片量大于声明阈值的所有索引进行碎片整理。有关更多信息，请参阅“SQL Server 联机丛书”中的主题“DBCC SHOWCONTIG”。

DBCC DBREINDEX

根据所用语法不同，DBCC DBREINDEX 可以只重建表的某一个指定索引，或者也可以重建表的所有索引。与除去并重新创建各个索引时采用的方法类似，DBCC DBREINDEX 语句也具备能够在一条语句中重建表的所有索引这一优点。这样比编写单独的 DROP INDEX 和 CREATE INDEX 语句更方便，并且，在重建表的一个或多个索引时，不必知道表结构或任何指定的约束条件。另外，DBCC REINDEX 语句固有原子性。如果要在编写单独的 DROP INDEX 和 CREATE INDEX 语句时获得相同的原子性，必须将多个单独的命令包含在一个事务内。

与单独的 DROP INDEX 和 CREATE INDEX 语句相比，DBCC DBREINDEX 会自动利用更多优化方案，在多个非聚集索引引用具有聚集索引的表时尤其如此。DBCC DBREINDEX 也可用于重建强制 PRIMARY KEY 或 UNIQUE 约束的索引，而不必删除和重新创建约束（因为，如果不先删除约束，则无法删除为了强制 PRIMARY KEY 或 UNIQUE 约束而创建的索引）。例如，您可能希望通过在 PRIMARY KEY 约束上重建索引来为索引重新建立给定的填充因子。

DROP_EXISTING

重建索引或整理索引碎片的另一种方法是：除去索引后再重新创建索引。通过删除旧索引，然后再重新创建相同的索引来重建聚集索引，这种方法很昂贵，因为所有二级索引都依赖指向数据行的聚集键。如果只删除聚集索引，然后再重新创建索引，您可能会不慎导致所有引用非聚集索引被删除和重新创建两次。在除去聚集索引时进行第一次除去/重新创建。在重新创建聚集索引时进行第二次除去/重新创建。

为了避免这一开销，使用 CREATE_INDEX 的 DROP_EXISTING 子句就可以一步完成这一重新创建的过程。采用一个步骤重新创建索引会告诉 SQL Server 您要重新组织现有索引，并避免删除和重新建相关非聚集索引等不必要的工作。这种方法还有一点明显的好处：可以使用现有索引中已预先排序的数据，因而不需要执行数据排序。这样就可以明显减少重新创建聚集索引的时间和成本。

DROP INDEX / CREATE INDEX

维护索引的最后一种方法是：直接除去索引，然后再重新创建索引。此选项仍在广泛使用，并且可能是以下人员的首选：熟悉此选项的人员、其处理窗口能够容纳表上所有索引完整的重新创建的人员。使用此方法的缺点是必须手动控制事件，以使事件按照适当的顺序发生。在手动除去和重新创建索引时，一定要在除去和重新创建聚集索引之前，除去所有非聚集索引。否则，在创建聚集索引时，将自动创建所有非聚集索引。

手动创建非聚集索引有一个优点：各个非聚集索引可以同时重新创建。不过，您的分区策略可能会影响所生成的索引的物理布局。如果同时在同一个文件（文件组）上重建两个非聚集索引，这两个索引的索引页可能在磁盘上交错在一起。这可能会打乱数据的存储顺序。如果多个文件（文件组）位于不同的磁盘上，您可以指定单独的文件（文件组）在创建索引之后保存索引，从而保持了索引页的顺序连续性。

前面提及的有关在预先排序的数据上建立索引的问题在此处同样适用。在已排序的数据上建立的聚集索引不必执行额外的排序步骤，从而可以极大地减少建立索引所需的时间和处理资源。

FILLFACTOR 和 PAD_INDEX

FILLFACTOR 选项提供了一种方法，用于指定在索引页和数据页上保留的开放空间的百分比。CREATE INDEX 的 PAD_INDEX 选项会在非叶级别的索引页上应用 FILLFACTOR 的设置。如果没有 PAD_INDEX 选项，FILLFACTOR 主要影响聚集索引的叶级别索引页。最好同时使用 PAD_INDEX 选项和 FILLFACTOR 选项。

PAD_INDEX 和 FILLFACTOR 用于控制页拆分。为 FILLFACTOR 指定的最佳值取决于在给定时间段内插入 8 KB 索引页和数据页的新数据量。请记住，通常，SQL Server 索引页包含的行数远远多于数据页包含的行数，因为索引页只包含与该索引相关的列数据，而数据页包含整行的数据，这一点很重要。

另外，请记住维护窗口的出现频率，维护窗口允许重建索引，以便更正即将发生的页拆分。请尝试只在大多数索引页和数据页已填满数据时再重建索引。如果表的聚集索引选择得当，则不会经常需要重建索引。如果聚集索引均匀地分布数据，从而所有与表相关的数据页上都会在该表中插入新行，那么，数据页将会均匀填充。总体说来，这将在开始发生页拆分且有必要重建聚集索引之前提供更多的时间。

为了确定用于 PAD_INDEX 和 FILLFACTOR 的适当的值，您需要发出判断请求。在作决定之前，您应该考虑两方面：一是在页上保留大量开放空间，二是可能发生的拆分页的数量，这两方面要保持性能上的平衡。如果为 FILLFACTOR 指定的百分比很小，它将在索引页和数据页上保留大量开放空间，这样，为了回答查询，SQL Server 就需要读取大量部分填充的页。对于大量读取操作而言，如果索引页和数据页上的压缩数据越多，SQL Server 的处理速度会明显加快。指定过高的 FILLFACTOR 会使各页上保留的开放空间过少，这样，各页很快就会溢出，从而导致页拆分。

在确定 FILLFACTOR 或 PAD_INDEX 值之前，请记住，在许多数据仓库环境中，读取操作的数量往往比写入操作的数量多得多。不过，如果定期装载数据，可能就不是这种情况了。许多数据仓库管理员尝试对表/索引进行分区和组织，以便容纳预计会出现的定期数据装载。

根据一般经验，如果预计的写入量相当于读取量的一大部分，最佳方法是按可行情况尽高地指定 FILLFACTOR，同时在每个 8 KB 页上保留足够的可用空间，以避免经常发生页拆分，至少要让 SQL Server 能够到达重新创建索引所需的下一个可用时间窗。该策略均衡了 I/O 性能（尽量填满各页），并且避免了页拆分（不让各页溢出）。如果不写入 SQL Server 数据库，FILLFACTOR 应设置为 100%，以便填满所有索引页和数据页，获得最佳 I/O 性能。

用于分析和优化的 SQL Server 工具

本节提供在表中装载数据的示例代码，稍后再用该段代码说明如何使用 SQL 事件探查器和 SQL 查询分析器分析和优化性能。

样本数据和工作负荷

下面的示例说明如何使用 SQL Server 性能工具。首先构造下表：

create table testtable
(nkey1 int identity,
col2 char(300) default 'abc',
ckey1 char(1))


然后，在该表中装载 20,000 行测试数据。装载到 nkey1 列的数据适用于非聚集索引。ckey1 列中的数据适用于聚集索引，col2 中的数据只是为了将每行的大小增加 300 个字节而填入的数据。

declare @counter int
set @counter = 1
while (@counter <= 4000)
begin
insert testtable (ckey1) values ('a')
insert testtable (ckey1) values ('b')
insert testtable (ckey1) values ('c')
insert testtable (ckey1) values ('d')
insert testtable (ckey1) values ('e')
set @counter = @counter + 1
end


下列查询构成了数据库服务器工作负荷：

select ckey1 from testtable where ckey1 = 'a'
select nkey1 from testtable where nkey1 = 5000
select ckey1,col2 from testtable where ckey1 = 'a'
select nkey1,col2 from testtable where nkey1 = 5000


SQL 事件探查器

优化性能的常用方法通常称为标记和度量。要验证为改进性能所做的更改是否确实改进了性能，首先需要建立现有不良性能情况的基线或标记。度量 指建立一些可量化的方法，用来证明性能正在得到改进。

SQL 事件探查器是用来进行标记和度量的工具。它不仅可以捕获服务器内发生的活动，供您进行性能分析；而且可以稍后再回放该活动。SQL Server 中的回放功能提供了一种有用的回归测试工具。使用回放功能，您可以方便地确定目前为了改进性能而采取的操作是否能达到预期效果。

回放功能还可以模拟负载或压力测试。您可以设置多个事件探查器客户端会话，让它们同时回放。例如，利用这一功能，管理员可以轻松地捕获五个并发用户的活动，然后同时启动十个回放，模拟有 50 个并发用户时的系统性能。您还可以跟踪数据库活动，然后在正进行修改的数据库中回放该活动，或在正进行测试的新硬件配置中回放该活动。

请记住，您可以利用 SQL 事件探查器记录 SQL Server 数据库中发生的活动。可以对 SQL 事件探查器进行配置，让它监视和记录对 SQL Server 执行查询的一个或多个用户。除了 SQL 语句外，使用该工具还能捕获各种各样的性能信息。使用 SQL 事件探查器记录的某些性能信息包括：I/O 统计信息、CPU 统计信息、锁定请求、Transact-SQL 和 RPC 统计信息、索引和表扫描、引发的警告和错误、数据库对象的创建/除去、建立连接/断开连接、存储过程操作、游标操作，等等。

捕获供索引优化向导使用的事件探查器信息

SQL 事件探查器和索引优化向导的结合使用，形成了一个功能非常强大的工具组合，它可以帮助数据库管理员确保在表和视图上放置正确的索引。SQL 事件探查器可以将查询的资源耗用情况记录到三个位置上。可以将输出定向到 .trc 文件、SQL Server 表或监视器。之后，索引优化向导从 .trc 文件或 SQL Server 表读取捕获的数据。索引优化向导对捕获的工作负荷中的信息和有关表结构的信息进行分析，然后针对改进性能提出应该创建哪些索引的建议。有了索引优化向导，

您就能自动完成以下任务：为数据库创建正确的索引、调度稍后进行的索引创建、生成可以手动检查和执行的 Transact-SQL 脚本。

分析查询负载需要完成以下步骤：

设置 SQL 事件探查器

1. 在工具菜单上选择 SQL 事件探查器，从 SQL Server 企业管理器中启动 SQL 事件探查器。
2. 按 CTRL+N 键新建一个 SQL 事件探查器跟踪。在连接到 SQL Server 对话框中，选择要连接到的服务器。
3. 从下拉列表框中选择 SQLProfilerTuning 模板。
4. 选中另存为文件或另存为表复选框。另存为表选项将打开连接对话框，在该对话框中，您可以将跟踪信息保存到探查查询的服务器以外的其他服务器。如果要将跟踪的活动同时另存为文件和表，请选中这两个复选框。如果要另存为 .trc 文件，请指向有效的目标和文件名。如果以前已运行过跟踪，现在再次运行同一跟踪，请指向现有的跟踪表；如果这是第一次将跟踪活动捕获到表中，您也可以提供新的表名。单击确定。
5. 单击运行。

运行工作负荷若干 (3-4) 次

1. 从 SQL Server 企业管理器或开始菜单启动 SQL 查询分析器。
2. 连接到 SQL Server，然后将当前数据库设为在其中创建测试表的数据库。
3. 在 SQL 查询分析器的查询窗口中输入下列查询：

   select ckey1 from testtable where ckey1 = 'a'
select nkey1 from testtable where nkey1 = 5000
select ckey1,col2 from testtable where ckey1 = 'a'
select nkey1,col2 from testtable where nkey1 = 5000


4. 按 CTRL+E 键执行查询。反复执行该步骤三至四次，生成工作负荷样本。

停止 SQL 事件探查器

• 在“SQL 事件探查器”窗口中，单击红色方块，停止该事件探查器跟踪。

将跟踪文件或表装载到索引优化向导中

1. 在 SQL 事件探查器中，选择工具菜单上的索引优化向导，以启动该向导。单击下一步。
2. 选择要分析的数据库。单击下一步。
3. 选择是否要保留现有索引的选项，或者添加索引视图。
4. 选择一种优化模式（快速、适中或彻底）。在“快速”优化模式下，索引优化向导执行分析所需的时间较少，但分析不够彻底，在“彻底”模式下生成的分析最彻底，但所需的分析时间最长。
5. 要查找用 SQL 事件探查器创建的跟踪文件/表，请选择我的工作负荷文件或 SQL Server 跟踪表。单击下一步。
6. 在选择要优化的表对话框中，选择要分析的表，然后单击下一步。
7. 索引优化向导将分析跟踪的工作负荷和表结构，然后在索引建议对话框中确定应创建的正确索引。单击下一步。
8. 该向导提供了几个选项：立即创建索引、安排索引创建的时间（之后会自动执行的任务），或者创建包含用于创建索引的命令的 Transact-SQL 脚本。选择首选项，然后单击下一步。
9. 单击完成。

索引优化向导针对样本数据库和工作负荷生成的 Transact-SQL

/* Created by: Index Tuning Wizard */
/* Date: 9/6/2000 */
/* Time: 4:44:34 PM */
/* Server Name: JHMILLER-AS2 */
/* Database Name: TraceDB */
/* Workload File Name: C:\Documents and Settings\jhmiller\My Documents\trace.trc */
USE [TraceDB]
go
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
go
DECLARE @bErrors as bit

BEGIN TRANSACTION
SET @bErrors = 0

CREATE CLUSTERED INDEX [testtable1] ON [dbo].[testtable] ([ckey1] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

CREATE NONCLUSTERED INDEX [testtable2] ON [dbo].[testtable] ([nkey1] ASC )
IF( @@error <> 0 ) SET @bErrors = 1

IF( @bErrors = 0 )
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION


索引优化向导为样本表和数据建议的索引正是我们所需要的：在 ckey1 上创建聚集索引，在 nkey1 上创建非聚集索引。ckey1 只有五个唯一值，每个值有 4000 行。假定其中一个样本查询 (select ckey1, col2 from testtable where ckey1 = 'a') 需要根据 ckey1 中的某个值来检索表，则适合在 ckey1 列上创建聚集索引。第二个查询 (select nkey1, col2 from testtable where nkey1 = 5000) 根据 nkey1 列的值提取一行。因为 nkey1 是唯一的，并且有 20,000 行，所以适合在该列上创建非聚集索引。

在使用了很多表并且要处理很多查询的实际数据库服务器环境中，将 SQL 事件探查器和索引优化向导组合使用，功能会非常强大。在数据库服务器处理典型的一组查询时，使用 SQL 事件探查器记录 .trc 文件或跟踪表。随后，将跟踪装载到索引优化向导中，以确定要建立的正确索引。按照索引优化向导中的提示执行操作，以自动生成索引，或安排索引创建作业在非高峰时间运行。您可能希望定期运行 SQL 事件探查器和索引优化向导的组合（也许每周一次或每月一次），以查看目前在数据库服务器上执行的查询是否发生了重大变化，这样就有可能会需要不同的索引。定期组合使用 SQL 事件探查器和索引优化向导，有助于数据库管理员在查询工作负荷不断变化和数据库日渐增大的情况下，仍保持 SQL Server 处于最佳运行状态。

使用 SQL 查询分析器分析事件探查器中记录的信息

在信息记录到 SQL Server 表中之后，可以使用 SQL 查询分析器来确定系统中哪些查询消耗资源最多。这样，数据库管理员就能集中精力改进那些最需要帮助的查询。如果将跟踪数据存储在表中，您就能方便地对跟踪数据的子集进行选择和筛选，从而为优化性能标识出性能最差的查询。例如，在上面的示例中，Duration 列是您使用 SQLProfiler Tuning 模板自动捕获的列，它可以用来标识需要最长执行时间（以毫秒计）的查询。要查找前 10% 的运行时间最长的查询，您可以运行下面这样的查询：

SELECT TOP 10 PERCENT *
FROM [TraceDB].[dbo].[Trace]
ORDER BY Duration DESC


要查找运行时间最长的前五个查询，您可以运行类似下面的查询：

SELECT TOP 5 *
FROM [TraceDB].[dbo].[Trace]
ORDER BY Duration DESC


要只将希望用于优化的行放在单独的表中，请考虑使用下面的 SELECT/INTO 语句：

SELECT TOP 10 PERCENT *
INTO TuningTable
FROM [TraceDB].[dbo].[Trace]
ORDER BY Duration DESC


前面提到的 SQLProfiler Tuning 模板只是针对优化建议的一组预选列和筛选器设置。您可能会发现，您需要捕获更多的信息。当然，您完全可以创建自己的自定义优化模板，方法是：只需打开预先提供的一个模板，然后用不同的名称保存即可。许多事件都可被捕获，包括 I/O 统计信息、锁定信息，等等。

SQL 查询分析器

SQL 查询分析器用于优化查询。该工具提供了多种类似“统计信息 I/O”的机制和用来解决查询问题的执行计划。

统计信息 I/O

SQL 查询分析器提供了一个选项，利用该选项，您能够获得在 SQL 查询分析器中执行的查询在 I/O 消耗方面的相关信息。要设置该选项，请在 SQL 查询分析器的查询菜单上，选择当前连接属性，以显示当前连接属性对话框。选中设置 statistics I/O 复选框，然后关闭该对话框。然后，执行查询并在结果窗格中选择消息选项卡，查看 I/O 统计信息。

例如，在选中设置 statistics IO 选项时，对前面的“SQL 事件探查器”一节中创建的样本数据进行以下查询，将在消息选项卡上返回以下 I/O 信息：

select ckey1, col2 from testtable where ckey1 = 'a'
Table 'testtable'.Scan count 1, logical reads 800, physical reads 62, read-ahead reads
760.


使用统计信息 I/O 是监视查询优化效果的一种好方法。例如，创建索引优化向导为样本数据建议的索引，然后再次运行该查询。

select ckey1, col2 from testtable where ckey1 = 'a'
Table 'testtable'.Scan count 1, logical reads 164, physical reads 4, read-ahead reads
162.


请注意，在可以使用索引时，逻辑读取和物理读取的数量会明显降低。

执行计划

使用图形化执行计划可以显示有关查询优化程序所做操作的详细信息，从而让您着重关注有问题的 SQL 查询。

查询的预计执行计划可以显示在 SQL 查询分析器的“结果”窗格中，方法是：用 CTRL+L 键执行 SQL 查询，或在查询菜单上选择显示预计的执行计划。各图标表明了查询优化程序如果执行了查询后会执行哪些操作。各箭头表示查询的数据流方向。将鼠标指针悬停于操作图标上方，即可显示有关每个操作的详细信息。各个操作图标下方还注明了每个操作步骤的大致成本。通过此标签，您可以迅速判断出查询中哪项操作是最昂贵的。

您也可以查看查询的实际执行计划，方法是在查询菜单上选择显示执行计划，然后执行查询。与显示预计的执行计划选项相比，显示执行计划先执行查询，然后才显示用于该查询的实际执行计划。

您可以创建执行计划的文本版本，方法是在查询菜单上选择当前连接属性，然后在该对话框中选中设置 showplan_text 选项。执行查询时，执行计划将在结果选项卡中显示为文本。

您还可以在查询内设置执行计划选项，方法是执行以下任一命令：

set showplan_all on
go
set showplan_text on
go


SET SHOWPLAN_ALL 供读取其输出的应用程序使用。使用 SET SHOWPLAN_TEXT 返回 Microsoft MS-DOS® 应用程序（如 osql 实用工具）可读取的输出。

SET SHOWPLAN_TEXT 和 SET SHOWPLAN_ALL 以一组文本行的形式返回信息，这些文本行所形成的分层树表示 SQL Server 查询处理器在执行每个语句时所采取的步骤。输出中反映的每个语句包含一个语句文本行，后面紧接若干行分别描述执行步骤的详细信息。

执行计划输出示例

这些结果是使用前面定义的查询示例和在 SQL 查询分析器中执行的“set showplan_text on”得出的。

查询 1

select ckey1,col2 from testtable where ckey1 = 'a'


基于文本的执行计划输出

|--Clustered Index Seek (OBJECT:([TraceDB].[dbo].[testtable].[testtable1]),
SEEK:([testtable].[ckey1]='a') ORDERED FORWARD)


同等的图形化执行计划输出

下图显示查询 1 的图形化执行计划。

如果您的浏览器不支持嵌入式框架，请单击此处在单独的页中查看。

执行计划利用 ckey1 列上的聚集索引来解析查询，正如聚集索引查找所示。

如果从表中删除了聚集索引，并且再次执行相同的查询，查询将恢复使用表扫描。下面的图形化执行计划表明该行为变化。

基于文本的执行计划输出

|--Table Scan(OBJECT:([TraceDB].[dbo].[testtable]), WHERE:([testtable].[ckey1]=[@1]))