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. 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
- 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 :
- a. SQL SERVER: SQL Statistics : Batch Requests/Sec
- b. SQL SERVER: SQL Statistics : SQL Compilations/Sec
- 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.
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· AI与.NET技术实操系列:基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型,支持深度思考和联网搜索!
· 基于 Docker 搭建 FRP 内网穿透开源项目(很简单哒)
· ollama系列01:轻松3步本地部署deepseek,普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现