?U7px-D051Testing软件测试网 ZI:X.w0n `7`

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

&P@SBZ"}Y3b&i0如果发现性能计数器Processor: % Processor Time的值很高，每一个CPU的% Processor Time都超过80%时，可视为出现CPU瓶颈。也可以通过视图sys.dm_os_schedulers监视SQL Server的进程调度（schedulers）来确认可执行的任务是否为非零值。非零值表示任务被迫等待时间片来运行，如果这个数值非常高，说明存在CPU瓶颈。
7J5v$x_ N6b.[}l7q051Testing软件测试网L0A'@+Xn%bK
Select scheduler_id,current_task_count,runnable_task_count from sys.dm_os_schedulers where scheduler_id<255
gWa^Mu051Testing软件测试网 u#b`'D8u)d
下面的查询将给出一个较高层的视图来说明当前被缓存的消耗CPU资源最多的批处理或者过程。查询通过相同查询句柄的所有语句合计CPU的消耗情况。51Testing软件测试网/@8nx-N;j1M:o?

(wy \ L)kclH0Select top 50 sum (qs_total_worker_time) as total_cpu_time,sum(qs.execution_count) as total_execution_count, count(*) as number_of_statements,qs.plan_handle from sys.dm_exec_query_stats qs group by qs.plan_handle order by sum(qs.total_worker_time) desc51Testing软件测试网s+Ka,c$J}(b/[ V
51Testing软件测试网)tR~sS
过多的compilation和recompilation51Testing软件测试网0} d H/U q2c/RrH

\i[ XnY7\0U,zj+A0在批处理或者远程过程调用（RPC）提交到服务器执行之前，系统会检查查询计划的有效性和正确性。如果在检查过程中出现了失败的情况，这些批处理可能会被再次编译来产生新的查询计划。这样的编译被称为重编译（recompilations）。这些重编译一般必须确定正确性且通常在服务器认定在潜在数据发生变化后存在可能被优厚的查询计划时执行。编译的特性是CPU敏感的操作，因此过分的重编译可以导致CPU性能问题。51Testing软件测试网0`7ig.u`D{5A!z
51Testing软件测试网4ZUBu'b/_2o
在SQL Server 2000中，当SQL Server重新编译一个存储过程时，整个存储过程都会被重编译，而不只是触发重编译的语句。SQL Server 2005引入了一种语句级别重编的存储过程。当SQL Server 2005重新编译存储过程时，只有引起重编译的语句才会被编译而不是整个过程。这就减少了CPU带宽并且减少了资源锁出现的可能，例如：COMPLIE locks. 重编译可以由于很多不同的原因造成，如：51Testing软件测试网#sy U't'S'e:I
51Testing软件测试网 O:[lqXNx @
l 架构变化51Testing软件测试网:@-\3Fp+xcck ^,l,[

8{8fl#o0~[/pM$m0l 统计变化51Testing软件测试网b*]&E_g.n*t
51Testing软件测试网ded(zP ?
l 延期编译
V:GV
v4oSZ[051Testing软件测试网W7o U3wtw+p
l SET选项变化
y7Y
o2Zz0x1d5]051Testing软件测试网/MonDf+oYkABl
l 临时表变化
J vr8Xt!Xw5S0
_'c7j%T~7j;@+C0l 存储过程以RECOMPLIE选项建立。
q:?3L*azq0
r!h+{ hUq%?/}0检测51Testing软件测试网W.s2w|{^W&`6S
51Testing软件测试网o*e!M)raBC5?)j
使用System Monitor 或者 SQL Server Profiler来检测过多的编译和重编译。
6fC
F$EA G051Testing软件测试网{2?Y1yc"_in
System Monitor
G$HB Gv-m4cr0`qr/?h6o051Testing软件测试网#h3tE3Vi4tK#g5_
SQL Statistics对象提供计数器来监视编译和发送到SQL Server实例的请求类型。必须通过监视查询编译和重编译的数量结合接收到的批处理数量来找出高CPU消耗是否是由编译引起。理想情况下，SQL Recompilations/sec和Batch Requests/sec的比率应该应该非常低，除非用户提交的是即席查询。
%r1hWm%kp4a0
&ns%C!NJLh*i0以下是关键数据计数器：51Testing软件测试网p&}&yY;S

T:Iduav#XE3W!k0l SQL Server: SQL Statistics: Batch Requests/sec51Testing软件测试网,Z}1q(JbFH

A e+z"]C~0l SQL Server: SQL Statistics: SQL Compilations/sec51Testing软件测试网$mv+e8a-Y#]

l[;K-f@c"AZ0l SQL Server: SQL Statistics: SQL Recompilations/sec51Testing软件测试网.DFK!T4J3lfV'g
51Testing软件测试网/kE)n\
j,n0?h
SQL Trace51Testing软件测试网1\2f
n5N@"d4S`*k4J

u f
JR,@$_~s:W)U0如果性能计数器显示非常大的重编译数量，重编译可能正在造成高CPU消耗。接下来需要需要利用SQL Profiler纪录的trace来找出当时被重新编译的存储过程。SQL Server Profiler trace可以给出这些信息连同重编译的原因。可以使用事件来获取这些信息。51Testing软件测试网9v'qy1r)L Q

w:n1y;ZZ0SP: Recompile / SQL: StmtRecompile. The SP:Recompile and the SQL:StmtRecompile事件类显示哪些存储过程和语句曾经被重新编译过。当编译一个存储过程时，为存储过程和每一个被编译的语句生成事件。然而，当一个存储过程被重新编译时，只有引起重新编译的语句才会被生成一个事件（不同于SQL Server 2000中的整体存储过程编译）。51Testing软件测试网#oH^yZjh-R8bf

4~.JQ4Ue h-us0SP:Recompile事件类中的重要的数据列如下所示：51Testing软件测试网.}f'jI2s(G

"ZX0~+Z5t6?1p3V
@6X0l Event Class
r|3B"bMP/[L*H%L0
D K4ia:C x_B0l EventSubClass51Testing软件测试网vp9t7~0\j
51Testing软件测试网7e8Q3p)A o-eB
l ObjectID(表示包含这个语句的存储过程)
P8p'hG x:{1y0
sHE)BA&SO XZM0l SPID51Testing软件测试网?;Z4K2QNcA/TN

4Js[VK.G#J,mUC/a0l Start Time51Testing软件测试网/J h$IXm^M[

f.p@:D%Znj0l SqlHandle
N
GA QZ[051Testing软件测试网X3`Fy(E5A L&n8i$C
l TextData51Testing软件测试网,?N+q!n.k&g5J*T

{Y4d{&u+JpW0EventSubClass数据列对于确定重编译原因来说非常重要。一旦过程或者触发器被重新编译，SP:Recompile就会被触发，但是有可能被重编译的即席批处理不会引发这个事件。 在SQL Server 2005中，监视SQL:StmtRecompiles时非常有用的，任何类型的批处理，即席查询，存储过程或者触发器被重编译时，这个事件类都会被触发。
dGA@/j,eX051Testing软件测试网 ^ A_8E]s3r|'a
保存trace文件，使用下面的查询来查看所有的重编译事件。51Testing软件测试网vK7\z$s,~$TF? M
51Testing软件测试网d9r:OF6ll&U%uy7|
Select spid,starttime,textdata,eventsubclass,objected,databaseid,sqlhandle from fn_trace_gettable (‘filepath.trc’,1) where EventClass in(37,75,166)
L#m;u8c&k3Ok0
@2FacvD0U*G0EventClass 37是SP:Recompile, 75是CursorRecompile, 166是SQL:StmtRecompile.
KEnG$e7P c0
R)^p6rj(q }0也可以进一步对这些查询结果根据Sqlhandle和ObjectID列进行分组来查看是否有某个存储过程存在大量的重编译或者由于其他原因导致的重编译（如Set选项变化）。
?\%}!L7P`9q0k8x051Testing软件测试网Oh.AkTC/n
Showplan XML For Query Compile. 这个事件类在Microsoft SQL Server编译或者重新编译SQL语句时发生。这个事件中有关于被编译或者重编译的语句的信息。这些信息包括查询计划和存在问题的过程的Object ID。如果发现SQL Compilations/sec计数器数值很高，应该监视这个事件类。通过这些信息可以发现哪些语句被频繁的重编译。可以使用这些信息改变那些语句的参数。这应该会降低重新编译的次数。51Testing软件测试网-J-}I_6j\

'D{G \&|&Y}7Vm8W0DMVs51Testing软件测试网3vOtz|-q
51Testing软件测试网vvw0W|@ ]&|%U
当使用sys.dn_exec_query_optimizer_info DMV时，可以得到SQL Server花费在优化上的时间。
} L"| J;|$@2|O8l*f0
%@-p.gZP#V0Select * from sys.dn_exec_query_optimizer_info
5{}V-W;Hg1\"F1oU"[0
*F.Uq&{lwC Y0Counter occurrence value51Testing软件测试网@i6Y |+[R(t+o
51Testing软件测试网HJI7Ha:w
Optimizations XX XX
x8c5O?1Vic0
N[;Ns _4Y!n&r)tO b0Elaspsed time XX XX
B/M8|e]t@^2b0
r0} Q+v0Zm-pu0Elaspsed time是消耗在优化上的时间。这个事件一般接近于消耗在优化上的CPU时间。
yCqO w U2pD051Testing软件测试网$qE:c|MK`s
另外一个用来捕获这些信息的DMV是 sys.dm_exec_query_stats51Testing软件测试网9b/I4[ q!QX
51Testing软件测试网RY*adG%sRAU:_
下列是需要查询的数据列：51Testing软件测试网mF&DUGe[E

b&q+}zC4g0l Sql_handle51Testing软件测试网5E2tt@ oC

#OJ8w2c0Vl0l Total worker time
/s+JGG$h a A M+A0
2h9p2\6qE,d(?0l Plan generation number
~(z4q
M$pt0
[0N @is"qs f}0l Statement Start Offset51Testing软件测试网7O z%PC\+Ny {,z6J

D1k q_9av0l0Plan_generation_num表示查询被编译的次数。下列语句给出前25个被编译的存储过程。
(B0j6D+CI[/CpL&e(Ld.|051Testing软件测试网aYs#~&`
Select top 25 sql_text.test,sqlhandle,plan_geration_num,execution_count,dbid,objectid from sys.dm_exec_query_stats across apply sys.dm_exec_sql_text(sql_handle) as sql_text where plan_generation_num>1 order by plan_generation_num desc

l%U0Z;J i3V'Ao051Testing软件测试网
p
m Q&f1Af[
解决方法51Testing软件测试网dU5r&g.j%H
51Testing软件测试网l'w&LV#A4sg
如果检测到过多的编译/重编译，考虑以下解决方法：
7k/u7O%EJq a3D3b:L051Testing软件测试网.Q4U.~sx sr"M
l 如果重编译是因为SET选项引起，使用SQL Profiler确定是哪一个SET发生了变化。尽量避免在存储过程内部修改SET选项。可以选择在连接级别上设置，并确保SET选项在连接的生命周期中不会发生变化。
"O5O$bke(x051Testing软件测试网!Y%Z'^#m#H&sc
l 临时表的重编译极值比一般表要低。如果由于统计信息变化导致重新编译临时表时，可以考虑把临时表替换为一个table变量，同样的变化不会影响table变量。这种方法的缺点是查询优化器不能跟踪table变量的信息，因为系统不会为table变量建立和维护统计信息。这可能导致不能优化对于表变量的查询。51Testing软件测试网'S6@hJ{-B
51Testing软件测试网tP,X6ojX]P
另外一个选择是使用KEEP PLAN查询提示。它设置临时表的极限值与永久表一致。EventSubClass列将显示临时表上发生了”Statistics Changed” 操作。51Testing软件测试网,nfv'gq%N
51Testing软件测试网E3x#o+lJ3[y
l 避免由于统计信息发生变化而导致的重编译（例如，当查询计划因为改变统计信息而不能被达到最优时），指定KEEPFIXED PLAN查询提示。通过这个选项的作用，重编译仅当出现正确性相关的变化时才会发生（例如，当底层表结构发生变化时才会重新编译查询）而不是由于统计数据。如果一个表的架构发生变化，或者表被sp_recompile存储过程标记，重编译将会发生。
` z [7O }$BD0V'u051Testing软件测试网/Yiz1PE$f)z
l 关闭被定义在一个表上的或者被索引的视图上的index & statistics的statistics自动更新防止由于在对象上的statistics的改变引起的重编译。注意，无论如何，关闭”auto-stats” 功能不是很好的选择。这是因为查询优化器不在对数据变化产生作，可能会导致非最优查询计划被执行。51Testing软件测试网.]j*i)`
DD*o
51Testing软件测试网:NI+KSl4{ h
l 批处理中应该使用具属对象名（如：dbo.table1）来避免重编译和对象之间的二义性。51Testing软件测试网0ts@Uh#zph;r
51Testing软件测试网_;Q!@&jm|N
l 避免由于延迟编译导致的重编译，不要使用条件结构（如IF）来插入DML和DDL或者建立DDL。
Z'pH3RUb$G(g0
rXl8O9F0l 运行DTA查看是否有可以改善编译时间和查询执行时间。
4qVT-C-zH7O-?){0
Y?9N(uwn3K0l 检查是否存储过程使用WITH RECOMPILE选项建立或者查询是否使用了RECOMPILE。如果存储过程使用WITH RECOMPILE选项建立，在SQL Server 2005中，考虑利用语句级别的RECOMPILE如果存储过程中的某个语句需要被重新编译。这可以避免每次执行存储过程时的强制编译，同时允许单独的语句重编译。51Testing软件测试网c!vYhyQ}'ik
51Testing软件测试网'I.~fLj.P
性能测试应用51Testing软件测试网5t#YF5G F

1us,?^CRT5|er0从性能测试的角度出发，可以在负载测试过程中收集有关的性能计数器，同时利用SQL Profiler收集负载测试期间有关重编译的事件类。一般情况下负载测试都会产生较高的CPU利用率，特别是压力测试。在测试结束后收集性能计数器确定是否存在过多的编译和重编译情况。51Testing软件测试网w[A\*W3SxOt

2P.Ek-n9s8Jc7NS0在确定系统出现过多的编译和重编译后，对trace和DMV结果进行分析找出产生大量编译和重编译的存储过程或者语句。根据不同的原因提出相应的解决方案。51Testing软件测试网6]UrqTZx?