《Microsoft Sql server 2008 Internals》读书笔记--第九章Plan Caching and Recompilation(4)

《Microsoft Sql server 2008 Internals》读书笔记订阅地址：

http://www.cnblogs.com/downmoon/category/230397.html/rss

《Microsoft Sql server 2008 Internals》索引目录：

《Microsoft Sql server 2008 Internal》读书笔记--目录索引

上文我们了解简单参数化查询的不足和预查询(Prepared Queries)，现在我们了解编译对象和重编译的原因。

■编译对象(Complied Objects)

存储过程

存储过程和用户自定义标量函数几乎一样。元数据表明objtype为Proc的编译计划被缓存，并被不断重用。默认情况下，缓存计划被用于成功的执行(Execution),就像sp_executesql那样。然而，存储过程和用户自定义标量函数还有一个选项可以在对象被执行的时候强制重编译。此外，你可以创建对象以便于每次存储过程被执行时创建一个新的计划。

对于一个单个的执行语句，要强制重编译，你可以使用execute....with recompile选项。我们看一个例子：

USE Northwind2;
GO
CREATE PROCEDURE P_Customers
  @cust nvarchar(10)
AS
  SELECT RowNum, CustomerID, OrderDate, ShipCountry
  FROM BigOrders
WHERE CustomerID = @cust;
GO
DBCC FREEPROCCACHE;
GO
SET STATISTICS IO ON;
GO
EXEC P_Customers 'CENTC';
GO
EXEC P_Customers 'SAVEA';
GO
EXEC P_Customers 'SAVEA' WITH RECOMPILE;

诸位可以猜猜，此时usecount应该是几？
 

 原因很简单，因为第三个计划使用了WITH RECOMPILE,从而强制使用了一个新计划，而原计划的使用次数是2不是3
注意，存储过程的WITH RECOMPILE选项只在当前执行有效，并不会保持在缓存以备重用。

函数

用户自定义标量函数，几乎同存储过程一样。如果你使用Execute语句，而不是表达式的一部分，你也可以使用强制重编译。

看一个例子：

 而如果使用如下语句，则没有办法实现请求重编译：

 SELECT dbo.fnMaskSSN(au_id), au_lname, au_fname, au_id  FROM authors; 

TVFs(Table Valued Functions )是否被看作存储过程，取决于你如何定义它们。你可以定义TVFs为内联函数或一个多声明（Multistatement）函数。这两种都不允许在函数被调用时强制重编译。

USE Northwind2;
GO
CREATE FUNCTION Fnc_Inline_Customers (@cust nvarchar(10))
RETURNS TABLE
AS
  RETURN
  (SELECT RowNum, CustomerID, OrderDate, ShipCountry
  FROM BigOrders
  WHERE CustomerID = @cust);
GO

CREATE FUNCTION Fnc_Multi_Customers (@cust nvarchar(10))
RETURNS @T TABLE (RowNum int, CustomerID nchar(10), OrderDate datetime,
  ShipCountry nvarchar(30))
AS
BEGIN
  INSERT INTO @T
    SELECT RowNum, CustomerID, OrderDate, ShipCountry
    FROM BigOrders
    WHERE CustomerID = @cust
  RETURN
END;
GO

 

调用函数如下：

DBCC FREEPROCCACHE
GO
SELECT * FROM Fnc_Multi_Customers('CENTC')
GO
SELECT * FROM Fnc_Inline_Customers('CENTC')
GO
SELECT * FROM Fnc_Multi_Customers('SAVEA')
GO
SELECT * FROM Fnc_Inline_Customers('SAVEA')
GO

而内联函数实际上被看作视图了，还记得前文所述的完全相同参数化么？即select语句调用精准相同的参数的函数时才会重用。

■重编译的起因(Causes of Recompliation)

到目前为止，我们讨论了SQL Server会通过自动参数化而重用一个不恰当的计划，我们不得不强制重编译。然而，还有一些已经存在的计划因为潜在对象或执行环境的改变而没有被使用。这些非预期的重编译的原因归为两类：Correctness-based 重编译和Optimality-based 重编译
Correctness-based 重编译

SQL Server如果有理由怀疑已存在的计划不再正确，那么可能会选择重编译。这个不再正确的原因可能是潜在的对象改变引起的冲突，比如改变数据类型或删除一个索引等。Correctness-based 重编译又分为两大类：架构改变和环境改变。下列改变表明一个对象的架构发生了改变：

1、增加或删除表或视图的一个列

2、增加或删除表的约束、默认值或规则

3、删除一个定义在表或索引视图的一个索引（前提是索引被一个计划使用）

4、删除一个定义在表上的统计，从而引起了一个correctness-related的使用该表的任何计划的重编译。

5、增加或删除一个表的触发器

此外，在一个表或视图上运行sp_recomplile存储过程时，将导致该对象的修改，你可以在sys.objects中的modify_date列观察到这个变化。通过监测这个变化，SQL Server可以决定架构的改变是否发生，以使重编译在存储过程、函数、触发器访问表或视图时发生。在存储过程、触发器、或函数中运行sp_recompile,将清除所有过时的缓存，以确保在下次执行时被重编译。

其他的Correctness-based 重编译在环境（如各种set选项）改变时被调用。改变某个Set选项会引起查询返回不同和结果。SQL Server在一个计划被执行时保持对SET选项的跟踪。你可以通过一个DMF(名称为sys.dm_exec_plan_attributes)访问一个set选项的位图。这是通过一个视图(sys.dm_exec_plan_attributes)的计划句柄值来访问。

例如：

select * from sys.dm_exec_plan_attributes
(0x06000B00CCEBA617B820A005000000000000000000000000)

我们得到set_options的值4347,对应bit字符串1000011111011,如果改变ANSI_Nulls为OFF，对应Bit字符串1000011011011，注意差异在第六位，即32,如果我们不清除计划缓存，我们将对同同一个批处理的两个计划，对应各自的set_Options值：

通过下列PiVOT操作获取想要的任何句柄值：

 

SELECT plan_handle, pvt.set_options, pvt.object_id, pvt.sql_handle
FROM (SELECT plan_handle, epa.attribute, epa.value
    FROM sys.dm_exec_cached_plans
      OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
    WHERE cacheobjtype = 'Compiled Plan'
    ) AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute
  IN ("set_options", "object_id", "sql_handle")) AS pvt;

 不是所有的Set选项都引起重编译，下列Set选项改变时会引起重编译：
1、ANSI_NULL_DFLT_OFF
2、ANSI_NULL_DFLT_ON

3、ANSI_NULLS

4、ANSI_PADDING

5、ANSI_WARNING

6、ARITHABORT

7、CONCAT_NULL_YIELDS_NULL
8、DATEFIRST

9、DATEFORMAT

10、LANGUAGE

11、NO_BROWSERTABLE

12、NUMRIC_ROUNDABORT

13、QUOTED_IDENTITY
上述列表有两个选项，在与对象（存储过程、函数、视图、触发器）关联时有一个特殊的行为。ANSI_NULLS和QUOTED_IDENTITY实际上与对象值一起被保存。

我们试下列语句：
SELECT OBJECTPROPERTY(object_id('<object name>'), 'ExecIsQuotedIdentOn');  
SELECT OBJECTPROPERTY(object_id('<object name>'), 'ExecIsAnsiNullsOn');
注意返回值为1表明Set选项是ON，为0表明OFF,如果是NULL意味着拼写错误或没有合适的许可。

Optimality-based 重编译

下文介绍。