重用执行计划



重用执行计划

优化的过程主要消耗CPU资源。默认情况下，SQL Server将重用原来调用存储过程时缓存的执行计划，而且不检查这样做是否合适。

为演示计划重用，运行下面的代码，它将创建usp_GetOrders存储过程：

USE Northwind; GO IF OBJECT_ID('dbo.usp_GetOrders') IS NOT NULL DROP PROC dbo.usp_GetOrders; GO CREATE PROC dbo.usp_GetOrders @odate AS DATETIME AS SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM dbo.Orders WHERE OrderDate >= @odate; GO

该存储过程接收一个订单日期作为输入（@odate）并返回在输入的订单日期当天及之后发生的订单。
打开STATISTICS IO选项以得到操作的I/O信息：

SET STATISTICS IO ON;

第一次运行该存储过程时，提供一个高选择性（high selectivity）的输入（即返回较低百分比的行的输入），生成的输出如表7-5所示。

EXEC dbo.usp_GetOrders '19980506';

表7-5  执行EXEC dbo.usp_GetOrders '19980506' 的输出

OrderID	CustomerID	EmployeeID	OrderDate
11074	SIMOB	7	1998-05-06 00:00:00.000
11075	RICSU	8	1998-05-06 00:00:00.000
11076	BONAP	4	1998-05-06 00:00:00.000
11077	RATTC	1	1998-05-06 00:00:00.000

检查图7-1所示该查询所生成的执行计划。

图7-1  执行计划表明使用了OrderDate上的索引

因为这是第一次调用该存储过程，SQL Server按这个输入值生成执行计划并缓存该计划。
优化器使用基数（cardinality）和密度（density）信息评估它要使用的访问方法的成本，筛选器的选择性是一个非常重要的因素。例如，使用一个具有高选择性筛选器的查询将受益于非聚集的非覆盖索引（nonclustered noncovering index），而低选择性（low selectivity）筛选器（即返回较高百分比的行的筛选器）则不使用这些索引。
像刚才为存储过程提供的这种高选择性的输入，优化器所选择的计划是使用了OrderDate列上的非聚集非覆盖索引的计划。该计划首先在索引中执行Seek操作（Index Seek运算符），在索引的叶级别上找到与筛选器匹配的第一个索引项。这个Seek操作读取两个页面，该索引有两个级别，在每个级别读取一个页面。在一个大型表中，这样的索引可能有三、四级。
Seek操作之后，计划在索引的叶级别执行局部的顺序向前扫描（虽然计划中并未显示，但它是Znclex Seek运算符的一部分）。局部扫描找到操作与查询筛选器匹配的所有索引实体（即所有大于或等于 @odate的OrderDate值）。因为输入选择性非常高，只找到四个匹配的OrderDate值。在这个特定案例中，局部扫描不需要访问叶级别上查找操作所到达的叶级页之外的页，所以不会产生额外的I/O操作。
该计划使用Nested Loops运算符，它调用一系列的Clustered Index Seek操作，为局部扫描找到的四个索引实体查找对应的数据行。因为在这个小表上的聚集索引只有两级，查找成本是8次逻辑读取 （logical reads）：2 × 4 = 8。一共是10次逻辑读取：2 （seek） + 2 × 4 （lookups） = 10。这是STATISTICS IO报告的逻辑读取数。
相对于已有的索引，这是该选择最佳计划。
我曾提到过，存储过程默认将重用以前缓存的计划。既然你已经在缓存中保存了计划，以后对该存储过程的调用将重新使用该计划。如果你一直用高选择性的输入调用该存储过程那就太好了。你可以充分利用计划重用，SQL Server也不会因重新生成新计划而浪费资源。这对于调用存储过程非常频繁的系统来说尤其重要。
然而，如果存储过程输入的选择性变化非常大，有些调用的选择性很高，有些则非常低。例如，下面的代码使用低选择性的输入调用存储过程。

EXEC dbo.usp_GetOrders '19960101';

因为计划已经保存在缓存中，将被重用，但在这个例子中却不太适宜。我用表中最小的OrderDate值作为输入。这意味着表中所有的行（830）都符合条件。计划将为每个符合条件的行执行聚集索引查找。该调用产生1,664次逻辑读取（logical reads），尽管整个表才有22个数据页。Orders表非常小，但在生产环境中这样的表一般都会有几百万行。在类似的情况中，重用这种计划的成本将更为显著。例如，一个表有1,000,000个订单，这些数据大概保存在25,000个页上。假设聚集索引包含三级，查找的成本将达到3,000,000 次逻辑读取：1,000,000 × 3 = 3,000,000。
很明显，在涉及大量数据访问且选择性变化非常大时，重用之前缓存的执行计划是一个非常糟糕的主意。
同样，如果第一次调用存储过程时使用低选择性的输入，你得到的计划对于该输入是最理想的执行表扫描（无序聚集索引扫描），并缓存该计划。然后，在以后的调用中，该计划将被重用，即使输入是一个高选择性的值。
这时，你可以关闭STATISTICS IO选项。

SET STATISTICS IO OFF;

通过查询sys.syscacheobjects系统视图（在SQL Server 2000中是master.dbo. syscacheobjects）你可以观察被重用的执行计划，该视图包含有关执行计划的信息：

SELECT cacheobjtype, objtype, usecounts, sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cache%' AND sql LIKE '%usp_GetOrders%';

查询产生的输出如表7-6所示。
表7-6  sys.syscacheobjects 中usp_GetOrders的执行计划

cacheobjtype	objtype	usecounts	sql
Compiled Plan	Proc	2	CREATE PROC dbo.usp_GetOrders …

在缓存中找到一个usp_GetOrders存储过程的执行计划，该计划被使用了两次(usecounts = 2)。
解决该问题的一个方法是创建两个存储过程，一个用于高选择性的请求，另一个用于低选择性的请求。你可以创建一个包含流程逻辑（flow logic）的存储过程，检查输入并根据输入的选择性决定调用哪个存储过程。这个办法理论上虽然不错，但在实际中要实现它非常困难。不消耗额外的资源，要动态地计算分界线是非常复杂的。而且，该存储过程只接收一个输入，要是有多个输入情况就变的更复杂了。

解决这个问题的另外一个办法是使用RECOMPILE选项创建（或修改）存储过程，就像这样：

ALTER PROC dbo.usp_GetOrders @odate AS DATETIME WITH RECOMPILE AS SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM dbo.Orders WHERE OrderDate >= @odate; GO

RECOMPILE选项告诉SQL Server在每次调用它时创建新的执行计划。如果生成计划所花费的时间只占该存储过程运行时间的一小部分，而使用不合适的计划运行存储过程会显著地增加执行时间，这时该方法非常有用。
运行修改过的存储过程，指定一个高选择性的输入。

EXEC dbo.usp_GetOrders '19980506';

你会得到如表7-1所示的计划，在这种情况下它是最佳计划，产生的I/O成本为10次逻辑读取。
然后指定一个低选择性的输入并再次运行它。

EXEC dbo.usp_GetOrders '19960101';

你会得到如图7-2所示的计划，图中显示了一个表扫描（无序的聚集索引扫描），对于该输入它是最理想的计划，这时的I/O成本是22次逻辑读取。

图7-2  执行计划显示一个表扫描（无序聚集索引扫描）

注意，当使用RECOMPILE选项创建存储过程时，SQL Server不在缓存中保存执行计划。如果再查询sys.syscacheobjects，将无法找到usp_GetOrders存储过程的执行计划。

SELECT * FROM sys.syscacheobjects WHERE sql NOT LIKE '%cache%' AND sql LIKE '%usp_GetOrders%';

在SQL Server 2000中，整个存储过程是一个编译单元。所以如果你只想重新编译某个特定的查询，是无法实现的。如果你使用RECOMPILE选项创建存储过程，每次调用它时整个存储过程都会被重新编译。

SQL Server 2005支持语句级的重编译。与原来重编译存储过程中的所有语句不同，SQL Server现在可以编译单条语句。它提供了新的RECOMPILE查询提示，允许显式地请求重编译某特定的查询。这样，其他的查询还是可以利用以前缓存过的执行计划，没有必要在每次调用存储过程时都重新编译它们。
运行下面的代码修改该存储过程，并指定RECOMPILE查询提示。

ALTER PROC dbo.usp_GetOrders @odate AS DATETIME AS SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM dbo.Orders WHERE OrderDate >= @odate OPTION(RECOMPILE); GO

在我们的示例中，存储过程中只有一个查询，所以无论在存储过程上还是在查询级别上指定RECOMPILE选项都无所谓。但如果一个存储过程中包含多个查询，使用该提示就有它的优势了。
要观察你得到的计划，运行该存储过程，指定一个高选择性的输入。

EXEC dbo.usp_GetOrders '19980506';

你会得到图7-1所示的计划，I/O成本为10次逻辑读取。
然后指定一个低选择性的输入再次运行它。

EXEC dbo.usp_GetOrders '19960101';

你会得到图7-2所示的计划，I/O成本为22次逻辑读取。
查询Syscacheobjects的结果只包含一个usecounts值为2的一个计划，不要对此感到困惑。

SELECT cacheobjtype, objtype, usecounts, sql FROM sys.syscacheobjects WHERE sql NOT LIKE '%cache%' AND sql LIKE '%usp_GetOrders%';

输出与表7-6相同。如果这个存储过程中没有其他查询，它们会潜在地重用执行计划。

==========================

select * from sys.syscacheobjects

http://msdn.microsoft.com/zh-cn/library/aa260394(v=sql.80).aspx