3.4  编译、重新编译和重用执行计划

我曾提到过，创建存储过程时，SQL Server分析代码并尝试对它进行解析。如果解析被延迟，将在第一次调用时执行解析。第一次执行存储过程时，如果解析阶段顺利完成，SQL Server将分析和优化存储过程中的查询并生成执行计划。执行计划保存了处理查询的指令。这些指令包括访问表的顺序、使用哪些索引、访问的方法以及要使用的联接算法、是否缓存中间行集等。SQL Server一般会生成多个执行计划并选择一个成本最低的执行计划。

需要注意的是，SQL Server不必创建所有可能的执行计划的排列组合。如果这样的话，优化阶段会花费很长时间。SQL Server通过计算优化阀值（threshold for optimization）来限制优化器，该计算基于相关表的大小以及其他一些因素。

存储过程可以重用之前缓存的执行计划，从而节省了生成新的执行计划所需的资源。这一节将讨论执行计划的重用，无法重用执行计划的情况，以及与计划重用相关的“参数嗅探问题（parameter sniffing problem）”。

重用执行计划

优化的过程主要消耗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相同。如果这个存储过程中没有其他查询，它们会潜在地重用执行计划。

重新编译

我前面曾提到过，默认情况下存储过程会重用之前缓存的执行计划。但也有一些例外会导致重新编译。在SQL Server 2000中，重新编译发生在整个存储过程级别上，而在SQL Server 2005中，它可以发生于语句级别上。

与计划正确性（plan correctness）和计划最优性（plan optimality）相关的问题可能会导致这种情况。计划正确性问题包括基对象的架构更改（例如，添加/删除列、添加/删除索引等）或更改可以影响查询结果的SET选项（例如，ANSI_NULLS、CONCAT_NULL_ YIELDS_NULL等）。导致重新编译的计划最优性问题包括更改被引用对象的数据到一定程度，以致于使用新计划可能会更合适。例如，由于更新统计信息。

导致重新编译的两种原因有许多特例。在本节的最后，我将为你提供一个更为详细描述它们的资源。

当然，如果计划在一段时间内没有重用而被移出缓存，再次调用该存储过程时SQL Server将生成一个新的计划。

来看一个导致重新编译例子，运行下面的代码，它创建存储过程usp_CustCities。

IF OBJECT_ID('dbo.usp_CustCities') IS NOT NULL

  DROP PROC dbo.usp_CustCities;

GO

CREATE PROC dbo.usp_CustCities

AS

SELECT CustomerID, Country, Region, City,

  Country + '.' + Region + '.' + City AS CRC

FROM dbo.Customers

ORDER BY Country, Region, City;

GO

该存储过程查询Customers表，并串联消费者三个物理位置：Country、Region和City。SET选项CONCAT_NULL_YIELDS_NULL默认为ON，表示当你用任意字符串和NULL串联时，得到的结果都为NULL。

第一次运行存储过程，你会得到表7-7所示的输出（被简化）。

EXEC dbo.usp_CustCities;

表7-7  CONCAT_NULL_YIELDS_NULL为ON时usp_CustCities 的输出(被简化)

CustomerID	Country	Region	City	CRC
CACTU	Argentina	NULL	Buenos Aires	NULL
OCEAN	Argentina	NULL	Buenos Aires	NULL
RANCH	Argentina	NULL	Buenos Aires	NULL
ERNSH	Austria	NULL	Graz	NULL
PICCO	Austria	NULL	Salzburg	NULL
MAISD	Belgium	NULL	Bruxelles	NULL
SUPRD	Belgium	NULL	Charleroi	NULL
QUEDE	Brazil	RJ	Rio de Janeiro	Brazil.RJ.Rio de Janeiro
RICAR	Brazil	RJ	Rio de Janeiro	Brazil.RJ.Rio de Janeiro
HANAR	Brazil	RJ	Rio de Janeiro	Brazil.RJ.Rio de Janeiro
GOURL	Brazil	SP	Campinas	Brazil.SP.Campinas
WELLI	Brazil	SP	Resende	Brazil.SP.Resende
TRADH	Brazil	SP	Sao Paulo	Brazil.SP.Sao Paulo
FAMIA	Brazil	SP	Sao Paulo	Brazil.SP.Sao Paulo
COMMI	Brazil	SP	Sao Paulo	Brazil.SP.Sao Paulo
…	…	…	…	…

如你所见，只要Region为NULL，串联后的字符串则为NULL。SQL Server缓存了存储过程的执行计划以备日后重用。除了执行计划，SQL Server还会存储所有可以影响查询结果的SET选项。你可以在sys.syscacheobjects的setopts位图中观察到这一点。

设置CONCAT_NULL_YIELDS_NULL选项为OFF，通知SQL Server在串联时把NULL当作空字符串处理。

SET CONCAT_NULL_YIELDS_NULL OFF

重新运行这个存储过程，将生成表7-8所显示的输出（被简化）。

EXEC dbo.usp_CustCities;

表7-8  CONCAT_NULL_YIELDS_NULL为OFF时usp_CustCities 的输出(被简化)

CustomerID	Country	Region	City	CRC
CACTU	Argentina	NULL	Buenos Aires	Argentina‥Buenos Aires
OCEAN	Argentina	NULL	Buenos Aires	Argentina‥Buenos Aires
RANCH	Argentina	NULL	Buenos Aires	Argentina‥Buenos Aires
ERNSH	Austria	NULL	Graz	Austria‥Graz
PICCO	Austria	NULL	Salzburg	Austria‥Salzburg
MAISD	Belgium	NULL	Bruxelles	Belgium‥Bruxelles
SUPRD	Belgium	NULL	Charleroi	Belgium‥Charleroi
QUEDE	Brazil	RJ	Rio de Janeiro	Brazil.RJ.Rio de Janeiro
RICAR	Brazil	RJ	Rio de Janeiro	Brazil.RJ.Rio de Janeiro
HANAR	Brazil	RJ	Rio de Janeiro	Brazil.RJ.Rio de Janeiro
GOURL	Brazil	SP	Campinas	Brazil.SP.Campinas
WELLI	Brazil	SP	Resende	Brazil.SP.Resende
TRADH	Brazil	SP	Sao Paulo	Brazil.SP.Sao Paulo
FAMIA	Brazil	SP	Sao Paulo	Brazil.SP.Sao Paulo
COMMI	Brazil	SP	Sao Paulo	Brazil.SP.Sao Paulo
…	…	…	…	…

你可以看到，当Region为NULL，它被当作空字符串来处理，这样，CRC列就不会包含NULL了。在这个例子中，更改会话选项会改变查询的意义。运行这个存储过程时，SQL Server首先检查是否存在缓存的计划，且该计划所包含的状态与当前SET选项是否相同。SQL Server没发现这样的计划 ，所以它生成一个新的计划。注意，不论对SET选项的更改是否真的影响了查询的意义，SQL Server都会查询匹配的SET选项状态再重用计划。

查询sys.syscacheobjects，你会找到usp_CustCities的两个计划，并包含两个不同的setopts位图，如表7-9所示。

SELECT cacheobjtype, objtype, usecounts, setopts, sql

FROM sys.syscacheobjects

WHERE sql NOT LIKE '%cache%'

  AND sql LIKE '%usp_CustCities%';

表7-9  sys.syscacheobjects 中usp_CustCities的执行计划

cacheobjtype	objtype	usecounts	setopts	sql
Compiled Plan	Proc	1	4347	CREATE PROC dbo.usp_CustCities …
Compiled Plan	Proc	1	4339	CREATE PROC dbo.usp_CustCities …

当建立数据库连接时，客户端接口和工具通常会更改一些SET选项的状态。不同的客户端接口更改不同的SET选项，形成不同的执行环境。如果使用多个数据库接口和工具连接到数据库，而且它们拥有不同的执行环境，你将无法重用其他应用程序的计划。在应用程序连接到数据库时，通过运行一个跟踪，你可以很容易地识别出客户端工具更改的SET选项。如果发现执行环境有差异，你可以在所有的应用程序中显式地设置SET命令，并在建立连接时提交。通过这种方法，所有的应用程序都将拥有一致的执行环境并重用其他应用程序的计划。

实验完成后，把CONCAT_NULL_YIELDS_NULL选项改回ON：

SET CONCAT_NULL_YIELDS_NULL ON;

这只是不重用执行计划的情况之一，还有很多这种情况。下面这一节的最后，我会提供一些资源，通过这些资源你可以找到更多的信息。

参数嗅探问题

就像我在前面提到的，SQL Server在第一次调用存储过程时根据为其提供的输入生成计划，不论好坏。“第一次调用”也包括因一定时间内没有重用或其他原因从缓存中移除计划之后的第一次调用。优化器“知道”输入参数的值，能够生成适合该输入的计划。然而，当查询中引用局部变量时情况就不一样了。为了便于我们讨论，这些局部变量是在批处理中还是在存储过程中都无关紧要。优化器无法“嗅探”出变量的内容。因此，当它优化查询时，它必须猜测。很明显，如果你不了解这个问题而且没有采取纠正措施将导致性能低下的计划。

为演示这个问题，向Orders表插入一个新订单，为OrderDate列指定GETDATE函数。

INSERT INTO dbo.Orders(OrderDate, CustomerID, EmployeeID)

  VALUES(GETDATE(), N'ALFKI', 1);

修改usp_GetOrders存储过程，声明一个局部变量并在查询筛选器中使用它。

ALTER PROC dbo.usp_GetOrders

  @d AS INT = 0

AS

DECLARE @odate AS DATETIME;

SET @odate = DATEADD(day, -@d, CONVERT(VARCHAR(8), GETDATE(), 112));

SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM dbo.Orders

WHERE OrderDate >= @odate;

GO

这个存储过程定义了整型输入参数@d，默认值为0。声明一个时间类型的局部变量@odate，它被赋值为今天的日期减去@d天之后的日期。然后执行查询，返回OrderDate

大于或等于@odate的所有订单。调用这个存储过程并使用@d的默认值，将生成表7-10中所示的输出。

EXEC dbo.usp_GetOrders;

表7-10  usp_GetOrders 的输出

OrderID	CustomerID	EmployeeID	OrderDate
11079	ALFKI	1	2006-02-12 01:23:53.210

当优化器优化查询时，它并不知道@odate的值是什么。所以它会使用一个保守的硬编码值（hard-coded value），它是表行数的30%。对于这样一个低选择性的估计，优化器通常会选择表扫描，即使实际中的查询是高选择性的，如果使用OrderDate上的索引会更快。

通过请求估计的执行计划（不是实际的），你可以观察到优化器估计和选择的计划。针对这次存储过程调用，你得到的估计执行计划如图7-3所示。

图7-3  执行计划显示估计的行数

你可以看到，优化器选择了执行表扫描（无序的聚集索引扫描），这个选择是根据它的30%的选择性估计作出的（249行/共计830行）。

解决该问题有几种方法。一种是在查询中尽可能地使用内联表达式代替变量，该表达式引用输入参数，而不是变量，而且尽可能地使用这种方法。这我们的示例中，应该是这样。

ALTER PROC dbo.usp_GetOrders

  @d AS INT = 0

AS

SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM dbo.Orders

WHERE OrderDate >= DATEADD(day, -@d, CONVERT(VARCHAR(8), GETDATE(), 112));

GO

再次运行usp_GetOrders，你会看到执行计划使用了OrderDate上的索引。

EXEC dbo.usp_GetOrders;

得到的计划与前面图7-1中的相似。I/O成本仅有4次逻辑读取。

解决这个问题的第二种方法是使用根存储过程（stub procedure）。也就是创建两个存储过程。第一个存储过程接收原始参数，把计算结果存入局部变量，然后调用第二个过程，把变量作为输入提供给它。第二个存储过程接收传递过来的输入订单日期并调用直接引用输入参数的查询。为实际调用查询的存储过程（第二个存储过程）生成计划时，优化器在优化时就可以知道参数的值了。

运行代码清单7-3中的代码并实现这个解决方案。

代码清单7-3  使用根存储过程

IF OBJECT_ID('dbo.usp_GetOrdersQuery') IS NOT NULL

  DROP PROC dbo.usp_GetOrdersQuery;

GO

CREATE PROC dbo.usp_GetOrdersQuery

  @odate AS DATETIME

AS

SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM dbo.Orders

WHERE OrderDate >= @odate;

GO

ALTER PROC dbo.usp_GetOrders

  @d AS INT = 0

AS

DECLARE @odate AS DATETIME;

SET @odate = DATEADD(day, -@d, CONVERT(VARCHAR(8), GETDATE(), 112));

EXEC dbo.usp_GetOrdersQuery @odate;

GO

调用usp_GetOrders存储过程。

EXEC dbo.usp_GetOrders;

你将得到该输入的最佳计划，该计划与前面表7-1中显示的计划相似，产生的I/O成本只有4次逻辑读取。

不要忘了我在上一节中描述的关于重用执行计划的问题。虽然得到了适合该输入的高效的执行计划，但这并不表示你希望在后面的调用中重用该计划。这完全取决于该输入是否具有代表性。我在前面对输入不具代表性这种情况提了一些建议，你应该遵循这些建议。

最后，SQL Server 2005还为你提供了一个新工具来解决这个问题，即查询提示OPTIMIZE FOR。该提示允许你为SQL Server提供一个字面值，用于表示具有代表性的变量的选择性。例如，如果你知道变量通常是一个高选择性的值，可以提供一个字符串‘99991231’, 就像这样：

ALTER PROC dbo.usp_GetOrders

  @d AS INT = 0

AS

DECLARE @odate AS DATETIME;

SET @odate = DATEADD(day, -@d, CONVERT(VARCHAR(8), GETDATE(), 112));

SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM dbo.Orders

WHERE OrderDate >= @odate

OPTION(OPTIMIZE FOR(@odate = '99991231'));

GO

运行这个存储过程：

EXEC dbo.usp_GetOrders;

你会得到适合高选择性OrderDate的最佳计划，与前面图7-1显示的计划类似，产生的I/O成本只有4次逻辑读取。

如果在查询筛选器使用输入参数之前更改了它们的值，你可能还会面临类似的问题。例如，假设你定义了变量@odat，并为它赋默认值为NULL。在查询筛选器使用该参数之前，你使用了下面的代码。

SET @odate = COALESCE(@odate, '19000101');

该查询使用OrderDate >= @odate条件筛选订单。当优化该查询时，优化器不知道@odate已经被修改，还会用原始值（NULL）优化这个查询。你将面临和前面描述的一样的问题，同样，应该用类似的逻辑解决该问题。

完成后，运行下面的代码进行清理。

DELETE FROM dbo.Orders WHERE OrderID > 11077;

GO

IF OBJECT_ID('dbo.usp_GetOrders') IS NOT NULL

  DROP PROC dbo.usp_GetOrders;

GO

IF OBJECT_ID('dbo.usp_CustCities') IS NOT NULL

  DROP PROC dbo.usp_CustCities;

GO

IF OBJECT_ID('dbo.usp_GetOrdersQuery') IS NOT NULL

  DROP PROC dbo.usp_GetOrdersQuery;

GO