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

《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)

■简单查询的不足（Drawbacks of Simple Parameterization）

在前面的查询输出中，你可能已经注意到自动参数化的的一项内容是SQL Server自己选择了参数的数据类型，不过，这个数据类型可能不是你想要的数据类型。例如，SQL Server在前文查询中假定TinyInt为参数，即范围0-255。此时，如果给定的值超过此范围，则不会使用相同的自动参数化查询。

如下示例，即便改变参数值大小的先后顺序，仍然生成了两个查询计划：

USE Northwind2;
GO
DBCC FREEPROCCACHE;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 6;
GO
SELECT FirstName, LastName, Title FROM Employees WHERE EmployeeID = 622;
GO
SELECT usecounts, cacheobjtype, objtype, [text]
FROM sys.dm_exec_cached_plans P
        CROSS APPLY sys.dm_exec_sql_text (plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
        AND [text] NOT LIKE '%dm_exec_cached_plans%';
GO

强制SQL Server两个查询使用相同的数据类型的唯一方法是为数据库启用Parameterization Forced

正如前述，简单参数化并不总是适当的，因此，SQL Server保守地选择它的使用。看下面这个例子,NorthWind2数据库的Bigorders表有4150行和105页。我们预料一个读取105个页的表扫描对于任何访问这个表的查询都将是最差性能的。在CustomerID列有一个非聚集的、非惟一的索引。如果我们为数据库NorthWind2启用Parameterization Forced,第一个select语句的计划可以用于第二个select语句，即便常量不同。第一个查询返回5行，第二个查询返回155行。正常情况下，一个非聚集索引seek被用于第一个select，而一个聚集索引被用于第二个，因为修饰行数超过表中的页的数量。然而，由于Parameterization Forced，事实上，我们得到的结果如下：

USE Northwind2;
GO
ALTER DATABASE Northwind2 SET PARAMETERIZATION FORCED;
GO
SET STATISTICS IO ON;
GO
DBCC FREEPROCCACHE;
GO
SELECT * FROM BigOrders WHERE CustomerID = 'CENTC'
GO
SELECT * FROM BigOrders WHERE CustomerID = 'SAVEA'
GO

 

在上例中，强制SQL Server把常量参数化不是一个好的选择。此时可以把选项设置为默认的Simple模式。注意，在我们使用PARAMETERIZATION FORCED时，为参数化查询选择的参数尽最大可能为规则字符类型。

ALTER DATABASE Northwind2 SET PARAMETERIZATION SIMPLE;
GO

那么实际操作中我们不知道是否该参数化时，怎么办？开发人员可以选择使用预查询机制中的一个来标记参数值为他们知道的类型。 SQL Server性能监视器包括一个对象,叫SQL Server：SQL　Statistics(其中有一些计数器用来处理自动参数化。）你可以监测这些计数器以决定是否有若干自动参数化操作是不安全的，或失败的。当这些数量很高时，你应该为这些案例侦查应用程序使开发人员对此显式地标记这些参数。

■预查询(Prepared Quries)

正如前面提到的，在缓存计划无数据中objType值为Prepared的查询是预查询。程序员除了可以设置是否需要参数化，此外，不像简单参数化，程序员还可在预查询中决定参数的数据类型。一个结构便是存储过程sp_executesql。

sp_executesql存储过程

sp_executesql是一个位于adhoc缓存和存储过程之间的一个过渡。sp_executesql需要事先确认参数和他们的数据类型。

更多说明，请参照MSDN:http://msdn.microsoft.com/zh-cn/library/ms188001.aspx

下列查询使用相同的缓存计划：

EXEC sp_executesql N'SELECT FirstName, LastName, Title
       FROM Employees
       WHERE EmployeeID = @p', N'@p tinyint', 6;
EXEC sp_executesql N'SELECT FirstName, LastName, Title
       FROM Employees
       WHERE EmployeeID = @p', N'@p tinyint', 2;
EXEC sp_executesql N'SELECT FirstName, LastName, Title
       FROM Employees
       WHERE EmployeeID = @p', N'@p tinyint', 6;

正如自动参数化也会有时是不恰当的，使用sp_executesql强制重用计划有时也会令人郁闷。看下面例子：

SET STATISTICS IO ON;
GO
DBCC FREEPROCCACHE;
GO
EXEC sp_executesql N'SELECT * FROM BigOrders
        WHERE CustomerID = @p', N'@p nvarchar(10)', 'CENTC';
GO
EXEC sp_executesql N'SELECT * FROM BigOrders
        WHERE CustomerID = @p', N'@p nvarchar(10)', 'SAVEA';
GO

 

 Prepare和Execute方法

 与sp_executesql类似的第二个机制是批处理的参数需要被应用程序确认，但还是有关键差异。Prepare和Execute方法并不需要批处理的全部文本被传至每一个Execution。相反，全文第一次在Preapre时间被传出，在执行时间返回了一个能够被用于调用批处理的句柄。ODBC和OLE DB通过SQLPrepare/SQLExecute和ICommandPrepare公示(expose)这个功能。你也可以在游标被调用时通过ODBC和OLE DB使用这个机制。当你使用这些函数时，SQL Server被通知这个批处理将重复使用。

Caching Prepared Queries

如果你的查询已经在客户端使用Prepare和Execute参数化，元数据显示你Prepared了查询，如同查询在服务器端被参数化一样，无论自动化还是使用sp_executesql。然而，没有参数化的查询（包括简单参数化和强制参数化）在cache中并没有相应的adhoc shelll查询，并包含非参数化的实际值，它们仅有prepared 计划。这并不是一个保险的方法来判断一个Prepared计划是被SQL Server使用简单、强制参数化还是被开发人员通过客户端参数化预备(Prepared)。如果你看到了相关的shell查询，那可以肯定查询是被SQL Server参数化的，反之则未必。因为shell查询消耗零成本，在SQL Serve由于内存压力下移走的首要候选对象。因此，没有shell查询仅仅可能是adhoc计划已经被从cache中移走，并不是从来就没有过shell查询。

 下文继续关注编译对象(包括存储过程和函数），以及重编译(Recompilation)