SQLServer2005性能测试实践-CPU篇编译与重编译

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

如果在没有额外复杂条件下突然出现CPU瓶颈，有可能是因为没有优化查询，错误的配置，或者是数据库上的原因和资源不足引起。在决定采用增加CPU数量或者使用更快速的CPU之前，应该先检查消耗CPU资源最多的操作是否能够被优化

如果发现性能计数器Processor:%ProcessorTime的值很高，每一个CPU的%ProcessorTime都超过80%时，可视为出现CPU瓶颈。也可以通过视图sys.dm_os_schedulers监视的进程调度（schedulers）来确认可执行的任务是否为非零值。非零值表示任务被迫等待时间片来运行，如果这个数值非常高，说明存在CPU瓶颈。

Selectscheduler_id,current_task_count,runnable_task_countfromsys.dm_os_schedulerswherescheduler_id<255

下面的查询将给出一个较高层的视图来说明当前被缓存的消耗CPU资源最多的批处理或者过程。查询通过相同查询句柄的所有语句合计CPU的消耗情况。

Selecttop50sum(qs_total_worker_time)astotal_CPU_time,sum(qs.execution_count)astotal_execution_count,count(*)asnumber_of_statements,qs.plan_handlefromsys.dm_exec_query_statsqsgroupbyqs.plan_handleorderbysum(qs.total_worker_time)desc

过多的compilation和recompilation

在批处理或者远程过程调用（RPC）提交到服务器执行之前，系统会检查查询计划的有效性和正确性。如果在检查过程中出现了失败的情况，这些批处理可能会被再次编译来产生新的查询计划。这样的编译被称为重编译（recompilations）。这些重编译一般必须确定正确性且通常在服务器认定在潜在数据发生变化后存在可能被优厚的查询计划时执行。编译的特性是CPU敏感的操作，因此过分的重编译可以导致CPU性能问题。

在SQLServer2000中，当SQLServer重新编译一个存储过程时，整个存储过程都会被重编译，而不只是触发重编译的语句。SQLServer2005引入了一种语句级别重编的存储过程。当SQLServer2005重新编译存储过程时，只有引起重编译的语句才会被编译而不是整个过程。这就减少了CPU带宽并且减少了资源锁出现的可能，例如：COMPLIElocks.重编译可以由于很多不同的原因造成，如：

l架构变化

l统计变化

l延期编译

lSET选项变化

l临时表变化

l存储过程以RECOMPLIE选项建立。

检测

使用SystemMonitor或者SQLServerProfiler来检测过多的编译和重编译。

SystemMonitor

SQLStatistics对象提供计数器来监视编译和发送到SQLServer实例的请求类型。必须通过监视查询编译和重编译的数量结合接收到的批处理数量来找出高CPU消耗是否是由编译引起。理想情况下，SQLRecompilations/sec和BatchRequests/sec的比率应该应该非常低，除非用户提交的是即席查询。

以下是关键数据计数器：

lSQLServer:SQLStatistics:BatchRequests/sec

lSQLServer:SQLStatistics:SQLCompilations/sec

lSQLServer:SQLStatistics:SQLRecompilations/sec

SQLTrace

如果性能计数器显示非常大的重编译数量，重编译可能正在造成高CPU消耗。接下来需要需要利用SQLProfiler纪录的trace来找出当时被重新编译的存储过程。SQLServerProfilertrace可以给出这些信息连同重编译的原因。可以使用事件来获取这些信息。

SP:Recompile/SQL:StmtRecompile.TheSP:RecompileandtheSQL:StmtRecompile事件类显示哪些存储过程和语句曾经被重新编译过。当编译一个存储过程时，为存储过程和每一个被编译的语句生成事件。然而，当一个存储过程被重新编译时，只有引起重新编译的语句才会被生成一个事件（不同于SQLServer2000中的整体存储过程编译）。

SP:Recompile事件类1
<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>