SQLServer定量性能分析

引言

描述揭示执行计划成本规则所必要的工具和测试脚本。查询成本和性能要达到我们的预期对表和索引有一些高水平的理解也是必要的。一些主题在其他地方有详细介绍，所以这里只做简短的讨论。 SQLServer 工具包括查询分析器和事件探查器。 SET 选项包括 SET STATISTICS IO 和 SET SHOWPLAN_ALL 。为揭示查询执行计划成本结构特别创建测试表。为测试表生成数据的脚本。 

表组织和索引结构

SQLServer 里的表和索引以一系列 8K 的页的形式存储。表和索引结构的详细介绍在 SQLServer 的帮助文档里已给出。表组织和索引结构这两方面跟性能有重大的关联，做一下简短的介绍。表要么作为堆要么作为聚集索引来组织。表要么有聚集索引要么没有（视图索引稍后讨论）。没有聚集索引的表称为堆。堆里的行不是按顺序存放的。图 1-1 显示了堆表的组织结构。

图1-1 堆表的组织结构

在有聚集索引的表里，行是基于键值顺序存放的。聚集索引的根和任何中间层有索引值。叶级有整个数据行，除在某些环境下的 text 和 image 数据库类型外。图 1-2 显示了有聚集索引的表的组织结构。

图1-2 聚集索引表结构

非聚集索引里的信息依赖于表是堆表还是聚集索引表。对于堆表来说，非聚集索引有一个指向行的指针包括文件、页和行位置。图 1-3 显示了堆上的非聚集索引。注意根和页节点属于非聚集索引。索引中间层没有显示。数据页属于堆表。

图1-3 建在堆表上的非聚集索引

对于聚集索引表来说，非聚集索引包含了聚集索引键。跟随非聚集索引搜索的书签查找操作的性能依赖于表的组织形式。如果是堆表，书签查找真正是一个查找操作。如果是聚集索引表，书签查找是在聚集索引上附加的索引搜索操作。 

工具

执行计划成本信息可以通过几个方式获得。在查询分析器里要么用显示估计的执行计划要么用显示执行计划功能可以图形化的显示执行计划。每一个组件操作的成本都作为整个查询成本的一个百分比显示，如下图 1-4 。当有不止一个查询的时候，每个查询的成本也作为批里所有查询的总成本的一个百分比显示。

图1-4 两个查询的显示估计执行计划输出结果

当鼠标指针放在一个特定的组件上的时候，在出现的细节窗口里就会用数字形式显示各个组件操作的成本。每个组件操作的成本依赖于涉及的行数或页数。显示估计执行计划提供了在没有实际执行查询下生成执行计划的功能。如果使用了主键或其他的唯一索引，就能决定每个操作的行数或页数，或者从使用搜索参数（ SARG ）的表的列上由 SQLServer 自动产生和维护的统计信息里估计出来。如果用显示执行计划功能执行查询，那么就可以知道每个操作的精确的行数和页数。对于基于实际的行数和页数的查询来说，成本值原封不动的反映了成本规则。 图 1-5 显示了在选择显示估计执行计划时的成本细节。估计行计数正好是主键上使用搜索参数的结果。

图1-5 显示估计执行计划下索引搜索的成本细节

图 1-6 显示了在显示执行计划下的成本细节。行数、执行数和成本都不再是估计的了。


图1-6 显示执行计划下的索引搜索的成本细节

执行计划细节也可以通过下面任何一个 SET 选项产生： SHOWPLAN_ALL 、 SHOWPLAN_TEXT 、 SET STATISTICS PROFILE 和其他一些资源包括事件探查器。来自 SET SHOWPLAN_ALL 选项的成本信息更加精确且精确度高于四舍五入后仅有一些重要数字的图形化显示的值，图 1-7 显示了 SET SHOWPLAN_ALL 输出的一个示例。


图1-7 SHOWPLAN_ALL 的输出结果

每个组件操作都有 I/O 成本和 CPU 成本。仅当 number of executes 是 1 的组件操作的总成本为 I/O 成本与 CPU 成本的总和。当 number of executes 大于 1 时，成本不是简单的 I/O 成本与 CPU 成本的和再乘以 number of executes 。因为子树成本包括所有操作的成本都计算进该特定组件操作里了。

没有文档记载 SQLServer 执行计划成本的度量单位。众所周知对于 SQLServer7.0 和 2000 平台成本值是相同的，包括标准版和企业版和所有的 SP 。索引搜索和书签查找操作的 I/O 成本依赖于系统是否有 1G 或大于 1G 的内存。 CPU 成本再所有的平台测试都是相同的。描述 I/O 成本随处理器数量而变化的这篇文档的早期版本，现在看来是不正确的。

其他两个有用的工具是 SET STATISTICS IO 选项和事件探查器。 STATISTICS IO 记录其他信息的扫描计数和逻辑读。事件探查器能记录读和写。 STATISTICS IO 逻辑读和事件探查器读一起意味着从数据缓存里读取的页数。逻辑读也称为逻辑 I/O 。一个页在一个给定的查询里可以被多次访问。有趣的一点是事件探查器没必要为 INSERT 、 UPDATE 或 DELETE 语句显示写 I/O 。 

测试表

为了检查执行计划成本结构，建立如下的两个测试表。没有指定列的含义， ID 列可以被定义为聚集索引或非聚集索引的主键。在表扫描测试里，没有定义主键。仅仅指定固定长度的数据类型且不允许为 NULL 。第一个表设计成每页能存相当多的行（填充因子为 90 ％时大约 320 行），但是少于可能的行最大量。第二个表每页存接近 100 行，在填充因子为 99 ％时实际能存 99 行。

CREATETABLE N1x (
ID intNOT NULL,
Value char(10) NOT NULL
)

CREATETABLE M2x (
ID intNOT NULL,
ID2 intNOT NULL,
ID3 intNOT NULL,
ID4 intNOT NULL,
ID5 intNOT NULL,
ID6 intNOT NULL,
GroupID intNOT NULL,
CodeID intNOT NULL,
Value char(10) NOT NULL,
randDecimal decimal(9,4) NOT NULL,
randMoney moneyNOT NULL,
randDate datetimeNOT NULL,
seqDate datetimeNOT NULL )

不同类型的索引组合产生特定的执行计划。为给定查询产生几个不同的执行计划便于比较，有必要使用查询提示。后缀显示了主键索引类型，如， N1C 表示在 ID 列上有聚集索引主键， N1N 表示在 ID 列上有非聚集索引主键且表没有聚集索引。 N1H 表示没有索引。每个表都有一些列以便这些列上任何有用的搜索参数都能返回相同的行数，实际的行数尽可能的匹配估计行数。 

数据生成脚本

填充每一个表里的数据以便观察执行计划里的行数或页数。下面是一个脚本：

DECLARE @I int, @rowCnt int
SELECT @I = 1, @rowCnt = 50000
WHILE @I <= @RowCnt
BEGIN
INSERT M2C_01 (ID, ID2, ID3, ID4, ID5, ID6, GroupID, CodeID,
        Value, randDecimal, randMoney, randDate, seqDate)  VALUES (@I, …)
SET @I = @I+1
END

下面是一些得到数据特定分布的规则：

1. (@I-1)/10 + 1
2. (@I-1)%(@rowCnt/10) + 1
3. 1 + (@I-1)*100/@rowCnt + ((@I-1)*100)%@rowCnt
4. (@I-1)%(320) + 1

规则1通过对@I每隔10个增加1产生一个序列。这用于GroupID列，使得一个连续的数字有相同的GroupID值。

规则 2 通过尽可能的分开列值来产生一个序列。对于 50000 行中的一行，第一行的值为 1 ，第 5001 行的值也为 1 ，如此下去。这用于 CodeID 列。 在一些环境下， ID2 列的值和 ID 列一样。一个指定对 ID2 列的查询，结果同 ID 列上的查询一样。这是为了揭示执行计划成本的不同依赖于通过主键或唯一索引产生的唯一条件。

规则 3 产生一个序列如 1 、 101 …… 49901 ， 2 ， 102 等等。每个连续值增加 100 ，直到 50000 。这个用于 ID3 列，为了连接其他表里不同页面上的每个连续行（每页有 99 行）。

规则4通过用模320简单产生一个序列。 其他规则用来产生特定的数据分布。 

综述

这篇介绍性的文章包括：产生测试表的基本技术，揭示执行计划成本规则的工具和逻辑 IO 计数，影响一些基本操作的表、索引的结构细节。下一步就是为通常的查询操作实际的检查执行成本结构。