运用计划缓冲的建议 - 铭轩同学

公告

　　计划缓冲的基本目的是通过重用执行计划来改进性能。因为，确认执行计划确实可重用很重要。因为即席查询的计划可重用性效率低下，所以一般建议尽可能依赖预定义工作负载技术。为了确保计划缓冲的高效实用，遵循以下建议。

明确地参数化查询的可变部分；
使用存储过程实现业务功能；
使用sp_executesql避免存储过程维护；
使用准备/执行模式避免重新发送查询字符串；
避免即席查询；
对动态查询使用sp_executesql代替EXECUTE；
小心地参数化查询的可变部分；
避免修改链接之间的环境设置；
避免查询中对象的隐含解析；

一、明确地参数化查询的可变部分

　　查询一般运行多次，每次运行的唯一不同是可变部分的不同值。它们的计划可以重用，但是，查询的静态和可变部分应该分离。虽然SQL Server有简单参数化和强制参数化特性，但是它们有各种局限性。应该始终使用标准的预定义工作负载结束明确地执行参数化。

二、使用存储过程实现业务功能

　　如果已经明确地参数化查询，将它放置在存储过程中可能带来最好的可重用性。因为只需要发送参数和存储过程的名称，所以网络流量减少了。存储过程预先编译，它们运行得比即席查询快速。存储过程还能为包含在存储过程中的一组查询维护单一的参数化计划，而不是维护大量单独查询的小型计划。这避免了计划缓冲被单独查询的计划充满。

三、使用sp_executesql编程以避免存储过程维护

　　如果考虑存储过程所需的对象维护，或者打算使用客户端生成的查询，则使用sp_executesql将查询作为预定义工作负载提交。和存储过程模式不同，sp_executesql不在数据库中创建持续性对象。sp_executesql适合执行单一的查询或小型批查询。在存储过程中实现完整的业务员逻辑还可使用sp_executesql作为一个大的查询串提交。但是，随着业务逻辑复杂性的增加，为整个逻辑创建并维护一个查询字符串将变得困难。

四、实现准备/执行模式以避免重传查询字符串

　　sp_executesql需要在每次重新执行时通过网络发送查询字符串。它还需要服务器端查询字符串匹配，以确认对应执行计划在过程缓冲中的开销。在ODBC或OLEDB(OLEDB.NET)应用程序中，可以使用准备/执行模式以避免在多次执行中重传查询字符串，因为只需要提交计划句柄和参数。

　　在准备/执行模式中，因为计划句柄返回给应用程序，该计划可以被其他用户连接重用--这并不仅限于创建该计划的用户。

五、避免即席查询

　　不要使用即席查询设计新的应用程序。为即席查询创建的执行计划不能在查询以不同的可变部分值重新提交时重用。尽管SQL Server有简单参数化和强制参数化特性来隔离查询的可变部分，但因为SQL Server在参数化中的保守，这个特性只限于简单查询。为了更好的计划可重用性，将查询作为预定义工作负载提交。

六、对于动态查询sp_executesql优于EXECUTE

　　在存储过程或数据库应用程序中动态生成的SQL查询字符串应该使用sp_executesql而不是EXECUTE命令执行。EXECUTE命令不允许查询的可变部分明确地参数化。

　　如执行以下语句:

DECLARE @n VARCHAR(3)
SET @n = '678'
DECLARE @sql VARCHAR(MAX)
SET @sql = 'SELECT * FROM PersonTenThousand 
INNER JOIN Province ON PersonTenThousand.PId = Province.Id 
WHERE PersonTenThousand.Id = ' + @n + '' 
EXECUTE (@sql)

　　生成的是即席计划：

　　而如果换成：

DECLARE @n NVARCHAR(3)
SET @n = 776
DECLARE @sql NVARCHAR(MAX),@param NCHAR(6)
SET @sql = 'SELECT * FROM PersonTenThousand INNER JOIN Province ON PersonTenThousand.PId = Province.Id WHERE PersonTenThousand.Id = @1'
SET @param = N'@1 INT' 
EXECUTE sp_executesql @sql,@param,@1 = @n