SQLServer 参数化查询经验分享

什么是参数化查询?
　　一个简单理解参数化查询的方式是把它看做只是一个t-sql查询，它接受控制这个查询返回什么的参数。通过使用不同的参数，一个参数化查询返回不同的结果。要获得一个参数化查询，你需要以一种特定的方式来编写你的代码，或它需要满足一组特定的标准。

　　有两种不同的方式来创建参数化查询。第一个方式是让查询优化器自动地参数化你的查询。另一个方式是通过以一个特定方式来编写你的t-sql代码，并将它传递给sp_executesql系统存储过程，从而编程一个参数化查询。这篇文章的后面部分将介绍这个方法。

　　参数化查询的关键是查询优化器将创建一个可以重用的缓存计划。通过自动地或编程使用参数化查询，sql server可以优化类似t-sql语句的处理。这个优化消除了对使用高贵资源为这些类似t-sql语句的每一次执行创建一个缓存计划的需求。而且通过创建一个可重用计划，sql server还减少了存放过程缓存中类似的执行计划所需的内存使用。

　　现在让我们看看使得sql server创建参数化查询的不同方式。

　　参数化查询是怎样自动创建的?

　　微软编写查询优化器代码的人竭尽全力地优化sql server处理你的t-sql命令的方式。我想这是查询优化器名称的由来。这些尽量减少资源和最大限度地提高查询优化器执行性能的方法之一是查看一个t-sql语句并确定它们是否可以被参数化。要了解这是如何工作的，让我们看看下面的t-sql语句：

　select *
　　from adventureworks.sales.salesorderheader
　　where salesorderid = 56000;
　　go

在这里，你可以看到这个命令有两个特点。第一它简单，第二它在where谓词中包含一个用于salesorderid值的指定值。查询优化器可以识别这个查询比较简单以及salesorderid有一个参数(“56000”)。因此，查询优化器可以自动地参数化这个查询。

　　如果你使用下面的select语句来查看一个只包含用于上面语句的缓存计划的、干净的缓冲池，那么你会看到查询优化器将t-sql查询重写为一个参数化t-sql语句：

 

select stats.execution_count as cnt,
　　p.size_in_bytes as [size],
　　[sql].[text] as [plan_text]
　　from sys.dm_exec_cached_plans p
　　outer apply sys.dm_exec_sql_text (p.plan_handle) sql
　　join sys.dm_exec_query_stats stats
　　on stats.plan_handle = p.plan_handle;
　　go

当我在一个sql server 2008实例上运行这个命令时，我得到下面的输出，(注意，输出被重新格式化了，以便它更易读)：

　　cnt size plan_text

　　--- ------- --------------------------------------------------------------

　　1 49152 (@1 int)select * from [adventureworks].[sales].[salesorderheader]

　　where [salesorderid]=@1

　　如果你看看上面输出中的plan_text字段，你会看到它不像原来的t-sql文本。如前所述，查询优化器将这个查询重新编写为一个参数化t-sql语句。在这里，你可以看到它现在有一个数据类型为(int)的变量(@1)，它在之前的select语句中被定义的。另外在plan_text的末尾， 值“56000”被替换为变量@1。既然这个t-sql语句被重写了，而且被存储为一个缓存计划，那么如果未来一个t-sql命令和它大致相同，只有salesorderid字段被赋的值不同的话，它就可以被用于重用。让我们在动作中看看它。

如果我在我的机器上运行下面的命令：

dbcc freeproccache;
　　go
　　select *
　　from adventureworks.sales.salesorderheader
　　where salesorderid = 56000;
　　go
　　select *
　　from adventureworks.sales.salesorderheader
　　where salesorderid = 56001;
　　go
　　select stats.execution_count as cnt,
　　p.size_in_bytes as [size],
　　[sql].[text] as [plan_text]
　　from sys.dm_exec_cached_plans p
　　outer apply sys.dm_exec_sql_text (p.plan_handle) sql
　　join sys.dm_exec_query_stats stats
　　on stats.plan_handle = p.plan_handle;
　　go
　　我从最后的select语句得到下面的输出，(注意，输出被重新格式化以便它更易读)：
　　cnt size plan_text
　　--- -------- --------------------------------------------------------------
　　2 49152 (@1 int)select * from adventureworks].[sales].[salesorderheader]
　　where [salesorderid]=@1

在这里，我首先释放过程缓存，然后我执行两个不同、但却类似的非参数化查询来看看查询优化器是会创建两个不同的缓存计划还是创建用于这两个查询的一个缓存计划。在这里，你可以看到查询优化器事实上很聪明，它参数化第一个查询并缓存了计划。然后当第二个类似、但有一个不同的salesorderid值的查询发送到sql server时，优化器可以识别已经缓存了一个计划，然后重用它来处理第二个查询。你可以这么说是因为“cnt”字段现在表明这个计划被用了两次。

　　数据库配置选项parameterization可以影响t-sql语句怎样被自动地参数化。对于这个选项有两种不同的设置，simple和forced。当parameterization设置被设置为simple时，只有简单的t-sql语句才会被参数化。要介绍这个，看下下面的命令：

select sum(linetotal) as linetotal
　　from adventureworks.sales.salesorderheader h
　　join adventureworks.sales.salesorderdetail d on d.salesorderid = h.salesorderid
　　where h.salesorderid = 56000

这个查询类似于我前面的示例，除了在这里我添加了一个额外的join标准。当数据库adventureworks的parameterization选项被设置为simple时，这个查询不会被自动地参数化。simple parameterization设置告诉查询优化器只参数化简单的查询。但是当选项parameterization被设置为forced时，这个查询将被自动地参数化。

　　当你设置数据库选项为使用force parameterization时，查询优化器试图参数化所有的查询，而不仅仅是简单的查询。你可能会认为这很好。但是在某些情况下，当数据库设置parameterization为forced时，查询优化器将选择不是很理想的查询计划。当数据库设置parameter为forced时，它改变查询中的字面常量。这可能导致当查询中涉及计算字段时索引和索引视图不被选中参与到执行计划中，从而导致一个无效的计划。forced parameterization选项可能是改进具有大量类似的、传递过来的参数稍有不同的查询的数据库性能的一个很好的解决方案。一个在线销售应用程序，它的客户对你的产品执行大量的类似搜索， 产品值不同，这可能是一个能够受益于forced parameterization的很好的应用程序类型。

不是所有的查询从句都会被参数化。例如查询的top、tablesample、 having、group by、order by、output...into或for xml从句不会被参数化。

　　使用sp_execute_sql来参数化你的t-sql

　　你不需要依赖于数据库的parameterization选项来使得查询优化器参数化一个查询。你可以参数化你自己的查询。你通过重新编写你的t-sql语句并使用“sp_executesql”系统存储过程执行重写的语句来实现。正如已经看到的，上面包括一个“join”从句的select语句在数据库的parameterization设置为simple时没有被自动参数化。让我重新编写这个查询以便查询优化器将创建一个可重用的参数化查询执行计划。

　　为了说明，让我们看两个类似的、不会被自动参数化的t-sql语句，并创建两个不同的缓存执行计划。然后我将重新编写这两个查询使得它们都使用相同的缓存参数化执行计划。

　　让我们看看这个代码：

　dbcc freeproccache
　　go
　　select sum(linetotal) as linetotal
　　from adventureworks.sales.salesorderheader h
　　join adventureworks.sales.salesorderdetail d on d.salesorderid = h.salesorderid
　　where h.salesorderid = 56000
　　go
　　select sum(linetotal) as linetotal
　　from adventureworks.sales.salesorderheader h
　　join adventureworks.sales.salesorderdetail d on d.salesorderid = h.salesorderid
　　where h.salesorderid = 56001
　　go
　　select stats.execution_count as cnt,
　　p.size_in_bytes as [size],
　　left([sql].[text], 200) as [plan_text]
　　from sys.dm_exec_cached_plans p
　　outer apply sys.dm_exec_sql_text (p.plan_handle) sql
　　join sys.dm_exec_query_stats stats on stats.plan_handle = p.plan_handle;
　　go

在这里，我释放了过程缓存，然后运行这两个包含一个join的、不同的非简单的t-sql语句。然后我将检查缓存计划。这是这个使用dmv 的select语句的输出(注意，输出被重新格式化了，以便它更易读)：

cnt size plan_text
　　--- ----------- -------------------------------------------------------------------------------
　　1 49152 select sum(linetotal) as linetotal
　　from adventureworks.sales.salesorderheader h
　　join adventureworks.sales.salesorderdetail d
　　on d.salesorderid = h.salesorderid
　　where h.salesorderid = 56001
　　1 49152 select sum(linetotal) as linetotal
　　from adventureworks.sales.salesorderheader h
　　join adventureworks.sales.salesorderdetail d
　　on d.salesorderid = h.salesorderid
　　where h.salesorderid = 56000

正如你从这个输出看到的，这两个select语句没有被查询优化器参数化。优化器创建了两个不同的缓存执行计划，每一个都只被执行了一次。我们可以通过使用sp_executesql系统存储过程来帮助优化器为这两个不同的select语句创建一个参数化执行计划。
下面是上面的代码被重新编写来使用sp_executesql 系统存储过程：

　dbcc freeproccache;
　　go
　　exec sp_executesql n'select sum(linetotal) as linetotal
　　from adventureworks.sales.salesorderheader h
　　join adventureworks.sales.salesorderdetail d on d.salesorderid = h.salesorderid
　　where h.salesorderid = @salesorderid', n'@salesorderid int', @salesorderid = 56000;
　　go
　　exec sp_executesql n'select sum(linetotal) as linetotal
　　from adventureworks.sales.salesorderheader h
　　join adventureworks.sales.salesorderdetail d on d.salesorderid = h.salesorderid
　　where h.salesorderid = @salesorderid', n'@salesorderid int', @salesorderid = 56001;
　　go
　　select stats.execution_count as exec_count,
　　p.size_in_bytes as [size],
　　[sql].[text] as [plan_text]
　　from sys.dm_exec_cached_plans p
　　outer apply sys.dm_exec_sql_text (p.plan_handle) sql
　　join sys.dm_exec_query_stats stats on stats.plan_handle = p.plan_handle;
　　go

如同你所看到的，我重新编写了这两个select语句，使它们通过使用“exec sp_executesql”语句来执行。对这些exec语句中的每一个，我都传递三个不同的参数。第一个参数是基本的select语句，但是我将salesorderid的值用一个变量(@salesorderid)替代。在第二个参数中，我确定了@salesorderid的数据类型，在这个例子中它是一个integer。然后在最后一个参数中，我传递了salesorderid的值。这个参数将控制我的select根据salesorderid值所生成的结果。sp_executesql的每次执行中前两个参数都是一样的。但是第三个参数不同，因为每个都有不同的salesorderid值。

　　现在当我运行上面的代码时，我从dmv select语句得到下面的输出(注意，输出被重新格式化了，以便它更易读)：

cnt size plan_text
　　--- ----------- -----------------------------------------------------------------------------------------
　　2 49152 (@salesorderid int)select sum(linetotal) as linetotal
　　from adventureworks.sales.salesorderheader h
　　join adventureworks.sales.salesorderdetail d on d.salesorderid = h.salesorderid
　　where h.salesorderid = @salesorderid

从这个输出，你可以看出，我有一个参数化缓存计划，它被执行了两次，为每个exec语句各执行了一次。

　　使用参数化查询来节省资源和优化性能

　　在语句可以被执行之前，每个t-sql语句都需要被评估，而且需要建立一个执行计划。创建执行计划会占用宝贵的cpu资源。当执行计划被创建后，它使用内存空间将它存储在过程缓存中。降低cpu和内存使用的一个方法是利用参数化查询。尽管数据库可以被设置为对所有查询force参数化，但是这不总是最好的选择。通过了解你的哪些t-sql语句可以被参数化然后使用sp_executesql存储过程，你可以帮助sql server节省资源并优化你的查询的性能。