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

　　这些天看了一篇微软官方发布的MS SQL Server2008性能问题处理及优化的英文文档，里面知识点介绍地很详细，在现实工作中也很实用，遂产生了想把它翻译一下的念头。翻译的过程，既可以帮助自己复习一下这些技术，也可以向其他还不熟悉这一块的朋友介绍一些新的知识，何乐而不为呢。只是这篇文章有点长，我会分成几篇随笔去介绍，所以，不光是对我耐性的考验，也是对你的考验哦！
--------------------------------------------

　　查询语句编译及优化是一项CPU密集处理操作。查询语句优化的花费会因为语句的复杂性及基础架构的增长而增加，但是即使是一个非常简单的查询语句也可以花费CPU10-20毫秒的时间去解析和编译。
为了减缓这种花费，SQL Server缓存并且重用已经编译过的查询计划。每次从客户端接收到一个新的查询语句时，SQL Server首先从计划缓存（有些时候被叫做过程缓存）中查找是否存在一个编译过的计划可被重复使用。如果找不到一个匹配的查询计划，SQL Server会将传进来的查询语句进行解析和辨析，然后才执行它。

　　对于一个OLTP类型的工作任务，被传送过来的查询语句集非常小并且是静态的。通常情况下，优化查询计划并不依赖于某一精确的值或者用在谓词中的某个值，因为查询是基于关键字的。所以对于这种类型，重复使用一个查询计划非常重要，因为重复编译的开销可能相当于甚至高于执行查询的开销。但是，对于数据仓库类型的工作任务，可能使用即时的查询语句并且让查询优化器按照每个集的取值查找优化计划会更好些，因为对于这种类型的查询，基本上执行查询的时间都要比执行编译的时间要长，并且优化查询计划更有可能依赖于查询语句中的谓词。对于基于OLTP的应用程序而言，使用参数化查询或者存储过程更能从实质上提升使用缓存计划的机会，进而降低CPU的消耗。你可以启用参数化在数据库级别或者查询语句级别，方式是使用PARAMETERIZATION FORCED数据库选项或者查询提示。 对于更多重要限制的信息，特别是如果你的索引依赖于计算列或者索引视图，请查看SQL Server 2008 在线文档。然而，参数化查询语句，最好的方式是在应用程序本身（在设计时），这也可以降低在连接字符串中使用参数化而可能存在的sql注入风险。对于更多信息，可以查看：

 

SQL Injection

 

Using sp_executesql

诊断

　　在编译期间，SQL Server 2008 为查询语句生成了一个“证书”并且显示在sys.dm_exec_requests和sys.dm_exec_query_stats的query_hash列中，并且作为Query_hash属性存在于Showplan/Statistics XML文档中。对于已经写进了query_hash参数化格式的实体，如果具有相同的query_hash，它们很可能涉及到相同的查询文本。对于只是文字本身不一样的查询，它们拥有相同的query_hash值。例如，前两个查询共享同一个query hash， 然而第三个有不同的query hash，因为它执行的是不同的操作：

select * from sys.objects where object_id = 100
select * from sys.objects where object_id = 101
select * from sys.objects where name = 'sysobjects'

　　Query hash 产生于编译期间的树形结构。空白会被忽略掉，因为在select 语句中使用 * 和 使用多个列名，差别还是很大的。此外，如果一个查询语句使用全名而另一个查询语句只是使用table名，但是它们对应于同一个对象，这种情况是没有关系的。下面所有的语句将会使用同一个query_hash。

Use AdventureWorks
Go
set showplan_xml on
go
select * from SaleOrderHeader h
select * from Sales.SalesOrderHeader h
select SalesOrderId,..., modifiedDate from Sales.SalesOrderHeader h
go
set showplan_xml off
go

　　请注意在query_hash值生成的时候，数据库名称部分在全名中被忽略掉了。这样做允许资源在instance上的所有数据库中进行使用。

　　为了去诊断应用程序提交过来了多个即时查询语句，一个简单的方法是按照sys.dm_exec_query_stats.query_hash列进行分组，如下：

select q.query_hash,q.number_of_entries,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

　　对于那些number_of_enties列中的值在几百甚至几千的查询语句，它们是设定参数化的最好对象。如果你查看XML查询计划中<QueryPlan>tag下的CompileTime及ComplileCPU属性，并且将这些value与number_of_entries的值相乘，你会得到一个估计值，这也就是如果你使用参数化，会有多少编译时间和CPU被消除（这也就意味着查询语句被编译了一次，然后它被缓存并且后来被重复运行）。消除这些不需要的缓存计划可以带来一些意想不到的好处，比如释放内存去缓存其它的编译计划（从而更深层次减少编译开支）并且留出更多的内存空间给缓存。