对于CPU利用的分析,着重在考察CPU瓶颈,编译和反编译。
1.CPU瓶颈
可以通观察PERFMON中的Processor:% Processor Time计数器,来确定是否存在硬性的瓶颈。如果值一值偏高(大于80%),则可以认为需要提升CPU性能了。
也可以通查询DMV:sys.dm_os_schedulers来查看。scheduler_id=255是的DAC调度,scheduler_id>255的是SQL Server内部使用调度。如果runnable_tasks_count不为0,
则说明有任务需要等待时间切片来执行,则可以认为需要提升CPU性能了。
select scheduler_id,current_tasks_count,runnable_tasks_count
from sys.dm_os_schedulers
where scheduler_id <255
--当前最耗时的查询和处理
selecttop20sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements, qs.plan_handle,st.text
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as st
groupby qs.plan_handle ,st.text
orderbysum(qs.total_worker_time) des
当然CPU不是说加就加的,价格贵,申请难,安装时可能还需要停机时间。
2. 编译和反编译
编译和反编译也是一个非常耗CPU的处理,现有系统会因为一些变动(schema,statistics,set属性,临时表等等的变化),也出现大量的编译和反编译,导致CPU使用上升。
可以通观察PERFMON中的
SQL Server: SQL Statistics: Batch Requests/sec,
SQL Server: SQL Statistics: SQL Compilations/sec,
SQL Server: SQL Statistics: SQL Recompilations/sec,
顾名思义,后面两个计数指标的值越低越好。如果不幸后两者的数值很高,我们就需要使用Profiler来跟踪,找出导致大量的编译和反编译的语句和对象。
在定义跟踪事件时,SQL Server 2005,个人认为只要跟踪SQL:StmtRecompile即可。
将trace文件保存下来,读取其中数据分析出其中频繁编译和反编译对象。分析的方法可以多种多样,重点和难点是按Textdata进行数据聚合,得到符合实际的分析结果。
因为同一个语句可能因为参数不一样就会被sqlServer识别为不同语句。
select spid,StartTime,Textdata,EventSubclass,ObjectID,
DatabaseID,SQLHandle ,CPU
from fn_trace_gettable(N'D:\workload_20110707\workload_20110707.trc',1)
orderby CPU desc
但是对于一般情况下(不推荐做法),看来看去就那么几条语句会排在前面(要是很多语句都有严重的编译和反编译情况,那么就topic应该是:如何处理cpu 100%之 类),可以用Substring来截取前20或者80个字符来Group By,应该就能知道最严重的那几条语句。