SQL Server执行计划——《SQL Server2008查询性能优化》笔记

查询性能取决于优化器确定的执行计划的效率，高效的执行计划被缓存下来重复使用可以提高查询效率。但是一些非参数化查询生成的执行计划，重复使用率很低，在高并发的环境下，为了维护这些查询计划会消耗大量的服务器资源。本文使用AdventureWorks做为测试库，通过一些简单脚本来揭示SQL Server执行计划的一些有趣特征。

SQL Server处理一个查询请求流程：

T-SQL编写-->解析器（Parser）-->代数优化器（Algebrizer）-->查询优化器-->执行计划生成，缓冲和hash计划生成-->执行查询。

可以通过访问动态管理视图sys.dm_exec_cached_plans来查看执行计划信息，

select * from sys.dm_exec_cached_plans

下面简单解释下sys.dm_exec_cached_plans中一些字段的意义，

Refcounts（引用计数）：表示缓冲中引用这个计划的其他对象数量

Usecounts（使用计数）：该对象添加到缓冲以来使用的次数

Size_in_bytes（字节大小）：保存在缓冲中的计划大小

Cacheobjtype（缓冲对象类型）：计划的类型，compiled plan:完整的执行计划；compiled plan stub:即时查询的标记；Parse tree:用于访问视图的计划

Objtype（对象类型）：生成该计划的对象类型。Proc（过程）；Prepared（预定义的）；Ad Hoc（即时）；View（视图）

Plan_handle（计划句柄）：内存中这个计划的标识符，用于检索查询文本和执行计划

考虑下面这个即时查询：

SELECT  soh.SalesOrderNumber
       ,soh.OrderDate
       ,sod.OrderQty
       ,sod.LineTotal
FROM    Sales.SalesOrderHeader AS soh
        JOIN Sales.SalesOrderDetail AS sod
        ON soh.SalesOrderID = sod.SalesOrderID
WHERE   soh.CustomerID = 20824
        AND sod.productid = 932

这个查询里过滤条件嵌入到查询本身并且没有明确的参数化以将其与该查询隔离。执行后运行以下脚本查看执行计划信息

select c.usecounts,c.cacheobjtype,c.objtype,t.text,c.plan_handle
from sys.dm_exec_cached_plans as c
cross apply sys.dm_exec_sql_text(c.plan_handle) t
where t.text='SELECT  soh.SalesOrderNumber
       ,soh.OrderDate
       ,sod.OrderQty
       ,sod.LineTotal
FROM    Sales.SalesOrderHeader AS soh
        JOIN Sales.SalesOrderDetail AS sod
        ON soh.SalesOrderID = sod.SalesOrderID
WHERE   soh.CustomerID = 20824
        AND sod.productid = 932'

执行结果：

从执行结果可以看出类型是即时查询，使用次数为1。如把CustomerID改为29248，执行后，修改查看执行计划脚本

select c.usecounts,c.cacheobjtype,c.objtype,t.text,c.plan_handle
from sys.dm_exec_cached_plans as c
cross apply sys.dm_exec_sql_text(c.plan_handle) t
where t.text like 'SELECT  soh.SalesOrderNumber
       ,soh.OrderDate
       ,sod.OrderQty
       ,sod.LineTotal
FROM    Sales.SalesOrderHeader AS soh
        JOIN Sales.SalesOrderDetail AS sod
        ON soh.SalesOrderID = sod.SalesOrderID%'

执行结果如下：

可以看出即时查询的执行计划没有被重用，usecounts都是1。再次执行CustomerID=29248的查询后，

可以看出计划被重用了usecounts=2。如果把脚本修改为：

SELECT  soh.SalesOrderNumber
       ,soh.OrderDate
       ,sod.OrderQty
       ,sod.LineTotal
FROM    Sales.SalesOrderHeader AS soh
        JOIN Sales.SalesOrderDetail AS sod
        ON soh.SalesOrderID = sod.SalesOrderID
WHERE   soh.CustomerID =29248--20824
        AND sod.productid = 932

只是添加个注释，执行后查看执行计划：

执行计划并没有被重用而是重新生成了一个计划。下面是执行计划text部分内容

从结果可以看出即时查询生成的执行计划是基于查询的精确文本，包含注释、大小写、后续空格和硬回车。

可以把以上查询脚本修改为存储过程使查询参数化达到计划被重用的效果，

IF(SELECT OBJECT_ID('spBasicSalesInfo')) IS NOT NULL
  DROP PROC dbo.spBasicSalesInfo
GO
CREATE PROC dbo.spBasicSalesInfo
@ProductID INT
,@CustomerId INT
AS
SELECT  soh.SalesOrderNumber
       ,soh.OrderDate
       ,sod.OrderQty
       ,sod.LineTotal
FROM    Sales.SalesOrderHeader AS soh
        JOIN Sales.SalesOrderDetail AS sod
        ON soh.SalesOrderID = sod.SalesOrderID
WHERE   soh.CustomerID = @CustomerId
        AND sod.Productid = @ProductId

先用脚本删除缓存的执行计划，

dbcc freeproccache

而后执行存储过程

EXEC spBasicSalesInfo @ProductID=932,@CustomerId=29248

连续执行两次后，用以下脚本查看

select c.usecounts,c.cacheobjtype,c.objtype,t.text,c.plan_handle
from sys.dm_exec_cached_plans as c
cross apply sys.dm_exec_sql_text(c.plan_handle) t
where t.text like 'CREATE PROC dbo.spBasicSalesInfo%'

执行结果：

修改参数值再次执行

EXEC spBasicSalesInfo @ProductID=932,@CustomerId=20824

执行结果：

可以看出执行计划被重用了。

对于一个简单的即时查询SQL Server通过分析可以安全的对其进行参数化查询。这个简单的即席查询只能是一张表。对于两张或者多张表之间连接的即时查询，对其进行参数化查询SQL Server认为不是安全的，因为不好的计划开销可能远远超过生成一个新计划的开销。执行以下脚本

select a.*
from Person.Address as a
where a.AddressID=42

而后通过sys.dm_exec_cached_plans查看执行计划信息

生成了两个类型的执行计划，一个adhoc，一个Prepared。这个adhoc不被执行，但是被编译以创建查询简单参数化所需的查询树。

把a.AddressID=52后执行查询，而后查看执行计划

预定义的查询计划usecounts=2,说明即时查询可以使用不同的过滤条件值重新执行，重用现有的预定义计划。