The Server is Hot. Now What?
“CPU is at 95%. We don’t know why.”
Before you start killing processes, you need to understand what is actually happening:
- Is SQL Server the cause, or is something else on the box eating CPU?
- Is SQL Server under memory pressure or is there plenty of buffer pool left?
- Which queries are burning the most CPU right now?
These three queries answer all of that.
What This Query Does
Part 1 — CPU History from Ring Buffer
SQL Server samples CPU usage every 60 seconds into sys.dm_os_ring_buffers.
This gives you the last 20 minutes of CPU data broken down into:
- SQL Server’s own CPU %
- System idle %
- Everything else on the OS
If SQL is taking 80% and the rest is 5%, SQL is the problem. If SQL is taking 10% and everything else is 80%, look at the OS.
Part 2 — Memory State
sys.dm_os_process_memory shows what SQL Server itself is holding.
sys.dm_os_sys_memory shows the OS-level picture — how much physical memory is left.
Watch for memory_utilization_percentage dropping and system_memory_state_desc moving away from Available physical memory is high.
Part 3 — Top Queries by Memory Grant
Large memory grants are a common cause of both CPU pressure (compiling huge plans) and memory pressure (grant waits). This surfaces the top 20 queries eating grant memory since the last plan cache clear.
CPU and Memory Pressure Script
Loading…
Gareth Winterman