如何确定CPU瓶颈-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

如何确定CPU瓶颈

简介:

Detect CPU Bottleneck in SQL Server

By : Kasim Wirama, MCDBA

 

When you have your database server experiencing a problem, there are many possibilities here, such as CPU, memory, hard disk or database configuration itself. There should be a systematic way to trouble shooting performance problem in SQL Server. This article, I would like to point out how to detect bottleneck in CPU with SQL Server 2005.

 

The straight forward way to detect CPU problem is to look at performance counter,  with object : Processor, and counter name : %Processor Time. If it shows high percentage value, let’s say 80% or over during 15 to 20 minutes, you definitely have CPU bottleneck. Anyway, you need to establish your baseline for CPU threshold above. Another counter name that is useful is System:Processor Queue Length. This counter gives information how long a queue for each processor. If you see 2 or more value for most of the time,  your processors are under pressure. When your server box has some applications running besides SQL Server, probably one of the application takes up significant CPU resource. To prove your suspicious thought, get information from Process:%Processor Time counter.

 

If you have your CPU bottleneck caused by SQL Server, you need to find out how many processes that are running, runnable, and suspended. An amount of runnable processes indicate that the CPU is busy serving other request, and an amount of suspended processes indicate that there is blocking issue. Here is the query to get the information.

 

SELECT COUNT(*) , t2.scheduler_id
From sys.dm_os_workers as t2, sys.dm_os_schedulers as t2
Where t1.state = ‘runnable/running/suspended’ and t1.scheduler_address = t2.scheduler_address and t2.scheduler_id < 255
Group by t2.scheduler_id

 

In general, there are 2 things that causes CPU bottleneck, they are :

 

  1. 1.       Inefficient query plan.

If you want to associate the query with CPU bottleneck, you query it from DMV sys.dm_exec_query_stats  and extract query text from sys.dm_exec_sql_text with parameter sql_handle. You sort the result based on most expensive average CPU cost that consists of division between total_worker_time and execution_count

 

  1. 2.       Excessive compilation and recompilation.

If SQL Server needs some time to compile/recompile the query, it shows that your execution plan is not reusable. If your query is very complex, try to rewrite/adding some index that will make the compilation time run faster.

These are 3 performance counter relating to excessive compilation/recompilation issue :

  1. a.       SQL SERVER: SQL Statistics : Batch Requests/Sec
  2. b.      SQL SERVER: SQL Statistics : SQL Compilations/Sec
  3. c.       SQL SERVER: SQL Statistics : SQL Recompilations /Sec

 

With wealth information from DMV and performance monitor, you have a useful tool for troubleshooting CPU bottleneck right away.





    本文转自 Fanr_Zh 博客园博客,原文链接:http://www.cnblogs.com/Amaranthus/archive/2011/03/28/1997684.html,如需转载请自行联系原作者



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章