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计数，影响一些基本操作的表、索引的结构细节。下一步就是为通常的查询操作实际的检查执行成本结构。