CPU瓶颈（三）--过度的查询语句编译及优化

解决方案

       SQL Server 2008同样产生了一个query_plan_hash值，这个值作为描绘查询计划访问路径的“证书”（也就是，那种关联算法被使用，关联的顺序，索引的选择，等等）。一些应用程序可能依靠优化器评估出特定的参数值并传给正在执行的查询，从而获得不同的查询计划。如果是这种情况的话，你不能参数化这些查询。

      你可以交互使用query_hash和query_plan_hash值去确定是否有一个集合的即时查询语句使用相同的query_hash值，从而导致查询计划使用相同或不同的query_plan_hash值，或者访问路径。要查看这个结果，可以通过在之前的查询语句上进行很小的修改来实现。

 select q.query_hash,
 q.number_of_entries,
 q.distinct_plans,
 t.text,
 p.query_plan from
 (select top 20 query_hash,
 count(*) as number_of_entries,
 COUNT(distinct query_plan_hash) as distinct_plans,
 min(sql_handle) as sample_sql_handle,
 min(plan_handle) as sample_plan_handle
 from sys.dm_exec_query_stats
 group by query_hash
 having COUNT(*)>1
 order by COUNT(*) desc) as q
 cross apply sys.dm_exec_sql_text(q.sample_sql_handle) as t
 cross apply sys.dm_exec_query_plan(q.sample_plan_handle) as p

 

      请注意：这个新的查询语句返回了给定的query_hash对应的单独查询计划的一个汇总值（query_plan_hash值）。那些number_of_entries值很大并且distinct_plans值是1的行，是实施参数化的最佳对象。即使distinct_plans的值大于1，你可以使用sys.dm_exec_query去获取不同的查询计划，并去检查这些不同是否可以达到优化性能的目的。

      在你确定了哪些查询语句可以实施参数化后，最好的实现方式是在客户端应用程序中实施参数化。对于在客户端实施参数化的详细步骤，取决于你使用哪种客户端的API，但是相同的一点是不要在查询语句中使用文字化的谓词，而是使用问号（?）作为一个参数化的标志。

-----Submitting as ad hoc query

select * from Sales.SalesOrderHeader where SalesOrderID = 100

-----Submitting as parameterized

select * from Sales.SalesOrderHeader where SalesOrderID=?

 

     对于不同的技术的API，你可以使用对应格式的参数替换到上面的问号。客户端就可以使用sp_executesql去提交对应的参数化的语句。

exec sp_executesql N'select * from Sales.SaleOrderHeader where SalesOrderId = @p1',N'@p1 int', 100

      因为这个查询语句是被参数化的，所以它能匹配并且重新使用缓存的计划。如果任务所对应的某个给定的数据库是可以被参数化的，但是你并没有权限在客户端应用程序去改变参数话设置，你也可以在数据库上强制使用参数化设置选项。请记下前面我们已经给出的警告：这样做会阻止优化器在匹配的索引视图及计算列上进行优化。

      Alter database AdventureWorks Set PARAMETERIZATION FORCED

      如果你不能在客户端应用程序上参数化或者在数据库层次上进行强制参数化，你依然可以为指定的查询创建一个临时的计划向导，可以使用选项提示（parameterization forced）来实现。关于更多实现这些步骤的信息，请参阅这个链接：

Forced Parameterization