CPU 瓶颈：
CPU瓶颈可能因为某个负载所需的硬件资源不足而引起。但是过多的CPU使用通常可以通过查询优化（特别是在一个服务器上没有增加额外负载或不同查询时CPU突然上升的情况）、寻找应用程序设计方面的问题和优化系统配置来减少。 在你急于买更快、更多的CPU之前，先要找出最耗费CPU资源的查询，并且尝试去调整那些查询或者调整设计/系统配置因素。
性能监视器是用于监测和识别CPU是否已经成为了性能瓶颈的其中一个有用的工具。你要查看：Processor:%Processor Time计数器是否很高。如果每个CPU的该值持续在80%以上，那么意味着CPU已经成为瓶颈。
通过SQL Server你也可以通过DMVs检查CPU是否存在瓶颈。如果在请求等待类型中发现有SOS_SCHEDULER_YIELD等待类型或者一个高值的runnable任务都可是提示可运行线程在计划等待中。这意味着处理器上出现了CPU瓶颈。如果你有可用的数据收集器，SQLServer的等待图表可以很容易地在任何时候查看到现有活动中的是否有CPU瓶颈。消耗CPU和SOS_SCHEDULER_YIELD等待都在报表中被纳入CPU等待范畴。当你看到CPU在高比例使用时，你可以深入查找那些耗资源最多的查询。
下面的查询为你提供一个高级视图去查找目前缓存批处理或者存储过程中使用做多CPU资源的查询。这个查询聚合计算所有执行相同计划句柄（Plan handle意味着他们来自相同批处理或者存储过程）CPU消耗。如果计划句柄超过一个语句，你就必须去深入分析以便找到在整个CPU中耗费最大的那个特定查询：
 

这节剩余部分将讨论一下通过SQL Server和其他一些有效的方法来增强CPU以解决这些问题。

过度的查询编译和优化：
查询编译和优化是一个高CPU消耗的过程。开销根据查询的复杂度和优先计划增加而增加。但是即使一个简单的查询也会耗用10-20毫秒的CPUT时间去解析和编译。
为了检查这种开销，SQLServer缓存并重用经过编译的查询计划。每次接收到来自客户端的查询时，SQLServer首先回去查找计划缓存，是否已经存在一个可以重复使用的编译计划。如果找不到适合的计划，SQLServer将对查询进行分合和编译。然后再执行。
• 对于OLTP系统。批处理的提交相对来说是小而固定的。一般情况下最佳的执行计划不会依赖于某些值或者作为谓词的值，因为查询是基于键值的。重用执行计划在这种类型的系统中非常重要，因为编译的开销往往接近甚至高于直接运行的开销。但是，对于一个数据仓库负载将能从专用SQL和允许查询优化器根据不同的值选择不同的执行计划中得到好处。因为运行这些查询的时间通常比编译时间要大得多。并且查询优化计划经常根据查询谓词而改变。使用参数化查询或者存储过程对OLTP系统能充分重用已经编译的执行计划。从而降低SQLServer对CPU的耗用。你可以在数据库或者查询级别上使用PARAMETERIZATION FORCED数据库选项或者查询提示来实现参数化。更多关于该特性使用的限制，比如当你依赖于计算列的索引或者索引视图等，请查看联机丛书。但是，参数化最好的使用地方还是在应用程序自己内部。同时能通过参数化减少被SQL注入的机会。相关只是可以查看联机丛书部分：
• SQL Injection (http://msdn.microsoft.com/en-us/library/ms161953.aspx)
• Using sp_executesql (http://msdn.microsoft.com/en-us/library/ms175170.aspx)

侦测问题：
在编译过程中，SQLServer2008计算查询的“签名”并作为sys.dm_exec_requests和sys.dm_exec_query_stats动态视图中的query_hash列的信息展示。这个queryhash属性在showplan/statistics xml实体中对具有相同query_hash值的高可能行设计相同查询文本，如果它被写成一个query_hash参数化形式。查询中仅是字面值不同但拥有相同的值。举个例子：有两个共享相同query hash的查询，当第三个查询有不同的query hash时。由于它执行不同的操作：

query hash将在编译阶段从产生结构中被重新计算。空格将被忽略，就像在SELECT 中，显式指定列和使用*号的query hash是不一样的。此外，在一个查询中使用完全限定名和另外一个使用全表名作为前缀的查询被认为是同一个查询，具有相同的query_hash：


注意当query_hash值产生时，数据库部分的完全限定名被忽略。这允许在相同实例的很多数据库中执行查询而不至于因为指定了数据库前缀使得查询不成功。
一个简单的识别应用程序提交的特殊查询是查看sys.dm_exec_query_stats.query_hash列：



查询结果中额number_of_entries值在千百级别意味着参数化很优秀。如果你在XML执行计划的<QueryPlan>标签中查看CompileTime和CompileCPU属性值是number_of_entries值的翻倍，你应该预测到多少编译时间和CPU用于参数化查询（意味着查询只编译了一次但是被重用了多次）。去除不必要的缓存计划还有一个难懂的好处。释放内存中的缓存用于其他编译计划，并且留下更多内存给缓存。

解决方案：
SQLServer2008同时也在执行计划的访问路径（即join算法、join顺序、索引选择等等）产生一个名为“签名”的query_plan_hash值。某些应用程序通过判断传入查询的参数来评估优化器给出的不同执行计划。在这种情况下如果你不想使得查询参数化，你可以使用query_hash和query_plan_hash值共同决定一个具有相同query_hash值的特定的查询结果是否拥有相同或不同的query_plan_hash值，或者访问路径。在之前提到的查询中稍作修改：


注意本查询根据给定的query_hash值返回一个唯一的查询计划（query_plan_hash值）。即使唯一的计划数量超过1个，你也可以使用sys.dm_exec_query_plan去检索不同的执行计划和检验它们对实现性能优化是否不同。
当你决定哪个查询需要被参数化时，参数化的发生地方最好是在客户端应用程序。具体的实现方法受到你所选择的API。但是有一件不变的事实就是所有API都用于替代创建文本化谓词，你可以创建一个带有问号（？）的字符串作为参数占位符。

你应该为你绑定参数值使用一个合适的APIs（ODBC,OLE DB,或者SQL Client）。客户端使用sp_executesql来执行参数化：
exec sp_executesql N’select * from Sales.SalesOrderHeader where SalesOrderID = @P1’, N’@P1 int’, 100
由于查询是参数化，能重用现有的缓存计划。如果希望整个数据库合理地参数化，并且你不需要控制或者修改客户端应用程序，你同样可以使用强制数据库参数来实现。注意前面的警告，这会阻止优化器选择有效的索引视图和索引：会阻止优化器选择有效的索引视图和索引：会阻止优化器选择有效的索引视图和索引：
ALTER DATABASE AdventureWorks SET PARAMETERIZATION FORCED
如果你不能在客户端应用程序参数化或者强制整个数据库参数化，你依然能对某些查询使用临时的计划暗示，使用OPTION（PARAMETERIZATION FORCED）。

非必要的重编译：
当一个批处理或者一个远程存储调用（RPC）被提交到SQLServer时，服务器会在开始执行钱检查查询计划的有效性和正确性。如果这些检查不通过，批处理就必须重新编译并产生一个不同的执行计划。这种编译叫“重编译”。重编译很多时候是必须的，用于确保正确性或者当服务器觉得底层数据更改时重编译更加有效。编译是一件耗费CPU资源的动作，因此过度的重编译结果可能是影响系统的CPU性能。
在SQLServer2000中，当SQLServer重编译一个存储过程时，整个存储过程都会被重编译。在SQLServer2005、2008中，存储过程的重编译是语句级别的。当2005、2008重编译存储过程时。只重编译需要编译的语句，而不是整个存储过程编译。这样能减少CPU频宽和更少的锁资源（比如COMPILE锁）。重编译一般出现在以下情况：

•  架构变更。
• 统计信息变更
• 延迟编译
• SET选项更改
• 临时表变更
• 在存储过程创建时使用了RECOMPILE提示。

探测：
可以使用性能监视器和SQLServer Profiler去探测过多的编译和重编译：
性能监视器（Performance Monitor）：
SQL Statistics对象提供编译监视计数器和SQLServer实例被发送请求的类型。你需要监控批处理的编译和重编译数来发现哪些制造了高CPU使用率。理想情况下，SQL Recompilations/sec和Batch Requests/sec都应该很低。除非用户提交了特殊查询。
主要计数器：
SQL Server:SQL Statistics:Batch Requests/sec
SQL Server:SQL Statistics:SQL Compilations/sec
SQL Server:SQL Statistics:SQL Recompilations/sec
详细信息请参考联机丛书中的SQL Statistics Object
SQL Server Profiler Trace：
如果性能监视器暗示重编译次数过高，重编译就有可能成为SQLServer耗费CPU资源的罪魁祸首。可以通过Profiler来跟踪存储过程重编译情况。Profiler会连同重编译的原因也追踪出来：
SP:Recompile/SQL:StmtRecompile：前者是存储过程，后者是语句的编译。当你编译一个存储过程时，会产生这个存储过程及其每个语句的相应事件。但是当存储过程重编译时，只有引起重编译的语句会被重编译。更重要的数据列是SP:Recompile事件。EventSubClass数据列对于发现重编译的原因是非常重要的。SP:Recompile会在存储过程中触发一次或者重编译时触发一次，并且不对不重编译操作的特殊批处理不触发。在05、08中，更加游泳的是SQL:StmtRecomile。因为这个计数器会在所有重编译时触发。重要的事件列：

• EventClass
• EvnetSubClass
• ObjectID(包含该语句的存储过程ID)
• SPID
• StartTime
• SqlHandle
• TextData

更多信息请查看联机丛书。
如果你已经有了一个跟踪文件，可以使用下面语句查看所有重编译事件：

其中：EventClass 37 = Sp:Recompile, 75 = CursorRecompile, 166 = SQL:StmtRecompile
你也可以更进一步组织结果，把sqlhandle和ObjectID列分组或者更多的列。要查看是否存储过程有过多的重编译或者因为某种原因重编译（比如SET选项）
Showplan XML For Query Compile：
该事件会在SQLServer编译或者重编译一个T-SQL语句时触发。这个事件是关于语句编译或重编译的信息。包括执行计划、ObjectID。获取这个时间对总体性能是有意义的，因为它能捕获每一个编译或重编译。如果你在SQL Compilations/sec指针长期处于高值。就要注意这个事件了。可以通过这个时间查看什么语句经常重编译。然后通过改变语句的参数来降低重编译次数。
DMVs：
当你使用sys.dm_exec_query_optimizer_info DMV是，可以看到SQLServer在优化时间方面的一些好建议。如果你执行该DMV两次，你将感受到在给定时间内用于优化的时间：
select * from sys.dm_exec_query_optimizer_info
counter occurrence value 
---------------- -------------------- --------------------- 
optimizations 81 1.0
elapsed time 81 6.4547820702944486E-2
要重点关注elapsed time，因为当优化时，这个时间通常接近用于优化的CPU时间。因为优化处理是非常耗费CPU资源的。你可以看到那些编译和重编译操作时最好CPU资源的。
另外一个有用的DMV是：sys.dm_exec_query_stats，主要关注列有：
• Sql_handle
• Total worker time
• Plan generation number
• Statement Start Offset
特别关注Plan_generation_num，因为是查询被重编译的次数。下面是一个查询前25个重编译最多的存储过程信息：