最近使用到sqlserver数据库并要对查询语句进行调优，之前接触的不多，搜索网上一些帖子和查阅微软msdn资料对sqlserver的查询计划才大致了解了一些，用这篇文章做个总结。本文主要侧重对查询计划的理解，尤其是对微软复杂的运算函数的理解，如理解有误请指出
最近使用到sqlserver数据库并要对查询语句进行调优，之前接触的不多，搜索网上一些帖子和查阅微软msdn资料对sqlserver的查询计划才大致了解了一些，用这篇文章做个总结。本文主要侧重对查询计划的理解，尤其是对微软复杂的运算函数的理解，如理解有误请指出。
如何查看查询计划
先介绍一下如何查看查询计划。使用【 SQL Server Management Studio 】进行查询，大致有如下几个方式看查询计划
1、 set showplan_all on 在查询前显示计划 ， 显示形式为每个查询步骤一行
2、 set statistics profile on 在查询后显示计划 ， 并包括每个查询步骤的扫描行数和执行次数
3、 选中 sql 语句按 Ctrl + L ( 同工具栏 [ 显示预估的查询计划 ] 按钮 ) 以图形方式显示查询计划 。 这个还可以使用 set showplan_xml on 命令 ， 后者生成的 xml 被 【 SQL Server Management Studio 】 打开就是图形查询计划
关于查询计划的说明
在msdn上找到 set showplan_all on 返回结果各列的说明
具体查询计划分析
初步分析计划
开启 set showplan_all on
执行如下 sql
SELECT dbll . N_SZJY GBM , sum ( data . N_SL ) NNum
FROM DB_SHARE . dbo . DA_JGXT_VW_QBF_FFJL data
left join DB_SHARE . dbo . T_DBLL dbll
ON dbll . C_ZFBH = data . ZFBH and dbll . D_KSRQ data . D_RQ and ( dbll . D_JSRQ >= data . D_RQ OR dbll . D_JSRQ IS NULL)
where data . D_RQ >= '2012-07-02 0.0' and data . D_RQ '2012-08-01 23:59:59.0'
GROUP BY dbll . N_SZJY
执行计划左半部分
接上图计划的右半部分如下：
Sqlserver 的执行计划一出来给人一种特别复杂的感觉有木有，其中命令特别多，还可以看到我们没有写出来的语句比如 Expr1006 ， Expr1012 ， Bmk1003 这都是什么啊？
一步一步来先看看执行计划中每个列的说明
（ msdn 有对执行计划所有运算符的说明
http://msdn.microsoft.com/zh-cn/library/ms191158(v=sql.105)
）
根据 msdn 说明，对上面的执行计划分析如下，对于整个计划树的执行是从下到上，从叶子到根的，最上边是整个查询语句：
12 、【 RID Lookup (OBJECT:([DB_SHARE].[dbo].[T_DBLL] AS [dbll]), SEEK:([Bmk1003]=[Bmk1003]), WHERE:([DB_SHARE].[dbo].[T_DBLL].[D_JSRQ] as [dbll].[D_JSRQ]>=[DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[D_RQ] as [data].[D_RQ] OR [DB_SHARE].[dbo].[T_DBLL].[D_JSRQ] as [dbll].[D_JSRQ] IS NULL) LOOKUP ORDERED FORWARD) 】
这是一个书签查找步骤，， RID （ record id ） Lookup 是使用行标示符在堆上进行书签查找， SEEK 后面有一个 [Bmk1003]=[Bmk1003] ，看命名是一个书签，在 11 行的 DefinedValues 列有对此的定义。
11 、【 Index Seek(OBJECT:([DB_SHARE].[dbo].[T_DBLL].[I_DBLL_ZFBH_KSRQ] AS [dbll]), SEEK:([dbll].[C_ZFBH]=[DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[ZFBH] as [data].[ZFBH] AND [dbll].[D_KSRQ] 】
这是使用 I_DBLL_ZFBH_KSRQ 索引在 T_DBLL 表检索数据， SEEK 部分是检索的具体条件，此步骤定义了 Bmk1003 ， 11 步应该先于 12 执行，看来在同一层级下顺序是从上到下的。
11 步骤和 12 步骤的意思是先用 I_DBLL_ZFBH_KSRQ 索引检索数据，再使用书签查找的方式获取每一行的其他数据。因为 I_DBLL_ZFBH_KSRQ 是一个非聚集索引，它只包含索引列的数据，实际上对 T_DBLL 检索出的数据列还包括 N_SZJY 和 D_JSRQ ，前者要进行 Group ，后者是进行与 DA_JGXT_VW_QBF_FFJL 的 D_RQ 的比对，查询语句涉及到非聚集索引不包含的列时就要通过书签查找或聚集索引查找来提取非索引列（ T_DBLL 此时没有聚集索引，只能用书签查找的方式）。
10 、【 Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003])) 】
一次嵌套循环连接，将 11 和 12 步骤数据连接起来， 11 步骤的数据作为 Outer ，在进行书签查找的父步骤一般都是 Nested Loops Join ，这基本符合内表较大且有索引的条件。
9 、【 Table Scan(OBJECT:([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL] AS [data]), WHERE:([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[D_RQ] as [data].[D_RQ]>='2012-07-02 0.000' AND [DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[D_RQ] as [data].[D_RQ] 】
在 DA_JGXT_VW_QBF_FFJL 表全表 扫描数据，条件是 D_RQ 在一个时间段内。显然这是需要增加索引的
8 、【 Parallelism(Repartition Streams, RoundRobin Partitioning) 】
这是什么呢？看看微软古板的说明[ Parallelism 运算符执行分发流、收集流和对流重新分区逻辑操作。 Argument 列可以包含一个 PARTITION COLUMNS:() 谓词和一个以逗号分隔的分区列的列表。 Argument 列还可以包含一个 ORDER BY:() 谓词，以列出分区过程中要保留排序顺序的列。 Repartition Streams 运算符处理多个流并生成多个记录流。记录的内容和格式不会改变 ]。大约是这样， Parallelism 表示查询会被并行执行（如果服务器负荷太高可能最终不会并行），这算是 sqlserver 的一个优化处理，如果服务器处理多任务能力强这就会比串行更有效率，这个并行应该是表示和其他任务的关系，由于 10 步骤会依赖 8 步骤的数据， 8 步骤应该先于 10 步骤执行
7 、【 Nested Loops(Left Outer Join, OUTER REFERENCES:([data].[ZFBH], [data].[D_RQ], [Expr1012]) WITH UNORDERED PREFETCH) 】
一次嵌套循环连接，它将 8 和 10 两个 步骤得到的数据连接， Outer 表是 DA_JGXT_VW_QBF_FFJL ，因 OUTER REFERENCES 中的字段都是属于该表。
这里出现了 Expr1012 ，这是个神秘的列，联系上下语句，这个列应该是 T_DBLL 的 N_SZJY ，因为后面要用此列数据进行分组。
6 、【 Sort(ORDER BY:([dbll].[N_SZJY] ASC)) 】
使用 N_SZJY 列排序，后面要进行 Group ，这里必须先进行一次排序
5 、【 Stream Aggregate(GROUP BY:([dbll].[N_SZJY]) DEFINE:([partialagg1007]=COUNT_BIG([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[N_SL] as [data].[N_SL]), [partialagg1009]=SUM([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[N_SL] as [data].[N_SL]))) 】
名词解释，[ Stream Aggregate 运算符按一列或多列对行分组，然后计算查询返回的一个或多个聚合表达式。此运算符的 输出可供查询中的后续运算符引用和 / 或返回到客户端。 Stream Aggregate 运算符要求输入在组中按列进行排序。如果由于前面的 Sort 运算符或已排序的索引查找或扫描导致数据尚未排序，优化器将在此运算符前面使用一个 Sort 运算符 ]
继续根据微软生硬的解释进行分析，这行的处理就是进行一个分组，因为 GROUP BY dbll.N_SZJY 一句而产生此行处理。其中还有一个 COUNT_BIG ，这是一个类似 COUNT 的函数，区别是前者返回 bigint 后者返回 int ，不过语句中只有个 sum 没有 count ，为什么还要计算呢？跳过先。
4 、【 Parallelism(Gather Streams, ORDER BY:([dbll].[N_SZJY] ASC)) 】
又一个并行查询。[ Gather Streams 运算符仅用在并行查询计划中。 Gather Streams 运算符处理几个输入流并通过组合这几个输入流生成单个记录 输出流。不更改记录的内容和格式。如果此运算符保留顺序，则所有的输入流都必须有序。如果 输出已排序，则 参数 列包含一个 ORDER BY:() 谓词和正在排序的列名称。 ] Gather Streams 函数把输入流组合，不过 4 行只有一个子节点，我理解到 4 之前仍存在两个输入流，分别来自 DA_JGXT_VW_QBF_FFJL 表和 T_DBLL 表，上面的步骤对两个输入流分别有索引条件过滤和分组，但仍然没有整合，在 Gather Streams 步骤将两个输入流数据整合起来，那么之后的数据看到的就是一个输入流了
3 、【 Stream Aggregate(GROUP BY:([dbll].[N_SZJY]) DEFINE:([globalagg1008]=SUM([partialagg1007]), [globalagg1010]=SUM([partialagg1009]))) 】
再次进行流聚合。这里和第 5 步骤的不同在哪里
第 5 步骤 DefinedValues 中有如下定义
[partialagg1007]=COUNT_BIG([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[N_SL] as [data].[N_SL]), [partialagg1009]=SUM([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[N_SL] as [data].[N_SL])
而此步骤执行了
[globalagg1008]=SUM([partialagg1007]), [globalagg1010]=SUM([partialagg1009])
这就是它们的不同，此步骤再次用 N_SZJY 分组对之前的计算结果求和，这应该是个避免整合流后分组数据出现重复的操作。
2 、【 Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [globalagg1008]=(0) THEN NULL ELSE [globalagg1010] END)) 】
Compute Scalar 的意思是计算标量。标量，相对于向量而言，无方向数据，就是计算一个数值。这个步骤 sqlserver 优化器执行了一个 case when ， globalagg1008 是针对每个 N_SZJY 的 count （ N_SL ），看来之前自动执行count是为了此步， globalagg1010 是针对每个 N_SZJY 的 sum （ N_SL ），对照我们的语句是 SELECT dbll . N_SZJY GBM , sum ( data . N_SL ) NNum ， 此步骤的意图看来 sqlserver 会对 sum 特殊情况的检测，对于整个语句而言就是如果 T_DBLL 表中 N_SZJY 列有数据而 DA_JGXT_VW_QBF_FFJL 中 N_SL 都为 null ，那么 sum 就返回 null
1 、【整条语句】。作为查询计划树的根节点，在计划列表中大多数列都是 Null ，这个计划的估算行是 12 行，仅供参考，实际查询的结果是 1 行
关于表扫描
Sql Server 会有以下方法来查找您需要的数据记录：
1. 【 Table Scan 】：遍历整个表，查找所匹配的记录行。这个操作将会一行一行的检查，当然，效率也是最差的。
2. 【 Index Scan 】：根据非聚集索引，扫描索引的全部记录，查找所匹配的记录行，匹配的条件可从查询计划的 Argument 列中看到。 比第一种方式的查找范围要小（ B+ 索引叶子之间有指针类似链表），因此比【 Table Scan 】要快。
3. 【 Index Seek 】：根据非聚集索引，定位（获取）记录的存放位置，然后取得记录（这会使用 B+ 索引查找树的定位算法，基本一条记录 2-4 次 IO ，取决于表数据量产生的索引树高度），这个方式比起前二种方式会更快。
4. 【 Clustered Index Scan 】：根据聚集索引扫描全部记录。这个的效率要根据实际情况分析。出现这个步骤可能是效率很差的表现，因为如果条件中的列没有索引，数据库引擎在提取数据的时会考虑进行优化，基于磁盘顺序读比随机读快的原理，数据按照聚集索引的顺序存放，那么用聚集索引来提取数据是一种对更差方式的优化。
比如 DA_JGXT_VW_QBF_FFJL 表有 715455 条记录，
如下记录返回 8 条记录，优化器使用 Clustered Index Scan
select * from DB_SHARE . dbo . DA_JGXT_VW_QBF_FFJL where N_ID = 14000
执行时间 10s ，计划如下， IO 消耗是 7.21 多，此时使用聚集索引扫描来顺序提取数据，这个步骤在这里就是避免更差的随机磁盘读取
如下语句返回 24 条记录 , 优化器使用 Clustered Index Seek
select * from DB_SHARE . dbo . DA_JGXT_VW_QBF_FFJL where D_RQ = '2012-07-25 0.0'
执行时间 0s 毫秒级，计划如下， IO 消耗是 0.000232
而直接使用 select top 10 * from DB_SHARE.dbo.DA_JGXT_VW_QBF_FFJL 查询计划也会使用 Clustered Index Scan ，
select top 10 * from DB_SHARE.dbo.DA_JGXT_VW_QBF_FFJL
|--Top(TOP EXPRESSION:((10)))
|--Clustered Index Scan (OBJECT:([DB_SHARE].[dbo].[DA_JGXT_VW_QBF_FFJL].[I_DA_JGXT_VW_QBF_FFJL_RQ]))
对于这个无条件的语句 这个计划已经是最优的了
5. 【 Clustered Index Seek 】：根据聚集索引获取记录，不解释，最快！
优化 Table Scan
使用日期过滤数据，对 D_RQ 建立聚集索引
CREATE CLUSTERED INDEX I_DA_JGXT_VW_QBF_FFJL_RQ ON dbo . DA_JGXT_VW_QBF_FFJL ( D_RQ )
再次查看执行计划
左半部分：
右半部分
Table Scan 变为 Clustered Index Seek ，看右半部分计划中的 EstimateRows 、 EstimateIO 、 EstimateCPU 、 TocalSubTreeCost 等都有很大提升
优化 RID Lookup前面提到 RID Lookup 是使用非聚集索引时提取了索引外的列产生的一种操作，中文解释为书签查找。
微软有一篇专门的文章 http://blogs.msdn.com/b/craigfr/archive/2006/06/30/652639.aspx 对此作出了解释。每次书签查找会产生一次随机 IO ，随机 IO 对于磁盘来说是比较耗费资源的，虽然 sqlserver 优化器认为这个比不用索引的消耗小些因而选择了这个方式，但可能的情况下我们还是要考虑优化。
优化书签查找的方式大致两种，一种是给目前已经使用的索引加入要查询的列，使得查询的列都在索引中；另一种是使索引成为聚集索引。那么可以考虑创建 ( C_ZFBH , D_KSRQ , D_JSRQ , N_SZJY ) 4 键联合索引或将 ( C_ZFBH , D_KSRQ) 的索引改为聚集索引。
我先采用了聚集索引优化，查询计划的 IO 、 CPU 、 Cost 都有所提升，主要是 TotalSubTreeCost 一项提升较多。按照微软对此项的说明为查询开销，这是一个综合的数值，一般这个开销较小的更好，不过也不绝对。
那么如果用 4 键联合索引呢？
我发现对比两者的查询计划相差很小，于是我用了 set statistics profile on 来查看实际的执行情况，这个开关比前面的计划多两列，会返回每个步骤的实际扫描行数和执行次数
列名
说明
Rows
各运算符生成的 实际 行数
Executes
运算符执行的次数
先看聚集索引，本次执行耗费 8s
再看 4 键联合索引，本次执行耗费 12s ，值得一提的是此时两个索引都存在，是 sqlserver 优化器选择了 4 键联合索引
相比之下，后者的 TotalSubTreeCost 较小因 IO 少，但是前者实际扫描的行数较少，且执行时间更短。我在执行前已使用了 dbcc dropcleanbuffers 和 dbcc freeproccache 清除缓存，不过我使用的数据库是虚拟机，在执行效率上经常有波动，后者的执行时间长可能因为索引还没有全部加载到内存中，实际测试时有时后者的时间更短。不过鉴于 D_JSRQ 是存在空值并且检索的时候都要使用 (D_JSRQ> 日期 or D_JSRQ is null) 这样的条件，前者可能更好。
看懂 Sqlserver 查询计划
http://www.cnblogs.com/fish-li/archive/2011/06/06/2073626.html
msdn 逻辑运算符和物理运算符引用
http://msdn.microsoft.com/zh-cn/library/ms191158(v=sql.105)