What Does “Slow” Look Like Right Now?
“The database is slow” is four different incidents wearing the same coat, and they are told apart in seconds rather than hours. Find the row that matches what people are actually reporting, then start at that step.
| What you are seeing | What it usually is |
|---|---|
| One screen or one report is slow, everything else is fine | A query, a plan or a statistic, not the server. Step 1: what is running, then Step 7: what changed |
| Everything is slow, and sessions are piling up | Blocking, until proved otherwise. Step 2: blocking |
| Everything is slow, nothing is blocked | A resource is short: CPU, memory or disk. Step 3: waits |
| It was fine yesterday and nothing was deployed | A plan change, stale statistics or a parameter it did not expect. Step 7: what changed |
| Slow only from the application, fast in SSMS | The client, the network or the connection settings, not the engine. Step 1: what is running, then the FAQ |
| Slow at the same time every day | A job, a backup or an index rebuild overlapping the workload. Step 6: disk, then Step 7: what changed |
If nobody can connect at all, this is not the page you want. That is Cannot Connect to SQL Server, a different fault with a different route.
Step 1: Look at What Is Running, Before Anything Else
One query answers the two questions that decide everything after it. Run it on the instance people are complaining about, while they are complaining.
Read blocker first. A non-zero value means that session is waiting on another session, not on the server, and you are in a blocking incident: go to Step 2 and ignore every counter until it is cleared. A blocker of 0 means the session is waiting on something the server itself cannot supply yet.
Read status second. running means it is on a CPU and getting work done, so the cost is in the plan. suspended means it is waiting, and wait_type names what for, which is the whole of Steps 3 to 6. runnable is neither: the query is ready and queueing for a CPU to run on, and several rows sitting there at once is the CPU pressure Step 4 investigates. A row with high cpu_time and a similar elapsed_ms burned the time; a row with low cpu_time and a large elapsed_ms spent it waiting, and those two need opposite fixes.
Empty result set, and users still complaining? Then nothing is slow on the server at the moment you looked, so the problem is intermittent, in the application, or on the way in. Keep the query handy and run it while somebody reproduces the complaint. For a fuller view of the same thing, Get Active Sessions and Requests is the production script with query text and plans attached, and sp_who vs sp_who2 vs sp_whoisactive explains why the three tools people reach for first disagree with each other.
SELECT r.session_id, r.status, r.wait_type, r.wait_time,
r.blocking_session_id AS blocker, r.cpu_time,
r.total_elapsed_time AS elapsed_ms
FROM sys.dm_exec_requests r
WHERE r.session_id > 50
AND r.session_id <> @@SPID
AND r.status NOT IN ('background','sleeping')
ORDER BY r.blocking_session_id DESC, r.total_elapsed_time DESC;Step 2: Blocking, Because It Looks Like Everything Else
Blocking comes first because it is the one cause that makes a perfectly healthy server look overloaded. CPU is flat, disk is quiet, memory is fine, and 200 sessions are stacked behind one transaction somebody left open in an application that is now at lunch. If Step 1 showed any non-zero blocker, stop reading counters.
The job is to find the head of the chain, the session blocking others and blocked by nobody, and work out what it is doing. Troubleshoot SQL Server Blocking, Step by Step is the full walkthrough from one blocked session to the root, including what to do when the head is idle with an open transaction. Get Blocking Chains gives you the chain as a tree in one run, and Get Open Transactions finds the transaction that has been open for hours with nothing executing, which is the most common head of chain there is.
Killing the head clears the symptom and teaches you nothing. Read what it was running first, because it will be back tomorrow.
Step 3: Waits, Over an Interval and Not Since Restart
Nothing blocked, everything slow. Now ask the server what it is waiting for, and get this part right because almost everybody gets it wrong the first time. sys.dm_os_wait_stats is cumulative since the instance started, so a top 5 taken straight from it describes the last 90 days, not the last 90 seconds, and the incident you are standing in contributes almost nothing to it. Take a snapshot, wait, take another, subtract.
The version below is deliberately unfiltered, so your first run will be full of background waits the engine does nothing but sleep on. Cumulative Wait Stats Lie: Measuring Waits Over an Interval explains why the cumulative reading sends people to fix the wrong thing, and how long an interval is worth taking. Get Wait Statistics is the production script, with the idle and background wait types already filtered out, and the Wait Types Library is where you look up whatever name comes back, because the name is the answer and there are several hundred of them.
Three families cover most real incidents and they are Steps 4, 5 and 6 below. Lock waits send you back to Step 2. If the top wait is something you have never seen, look it up before you act on it: several of the loudest wait types are idle background tasks that are supposed to be at the top.
SELECT wait_type, wait_time_ms, waiting_tasks_count
INTO #w1 FROM sys.dm_os_wait_stats;
WAITFOR DELAY '00:00:30';
SELECT TOP 5 w2.wait_type,
w2.wait_time_ms - w1.wait_time_ms AS wait_ms,
w2.waiting_tasks_count - w1.waiting_tasks_count AS waits
FROM sys.dm_os_wait_stats w2
JOIN #w1 w1 ON w1.wait_type = w2.wait_type
WHERE w2.wait_time_ms - w1.wait_time_ms > 0
ORDER BY wait_ms DESC;Step 4: CPU
You are here because the waits pointed at the scheduler, or because somebody is looking at a graph pinned near 100 percent. The useful question is not how high it is, it is which queries put it there, and whether the server is genuinely short of CPU or just running one bad plan very enthusiastically on every core it has.
SQL Server High CPU: What to Check First, in Order is the ordered checklist for exactly this branch, and it is the page to open if CPU is the headline. Get Top CPU Queries ranks what actually consumed it rather than what happens to be running this second. If the top wait was SOS_SCHEDULER_YIELD the schedulers are genuinely saturated; if it was CXPACKET the work is going parallel and waiting on itself, which is a reason to look rather than a verdict, and a large CXCONSUMER on its own is normal. MAXDOP and Cost Threshold for Parallelism is the configuration side of that rather than a query problem.
Step 5: Memory
Memory pressure rarely announces itself. It shows up as queries that used to run instantly now queueing for a grant before they are allowed to start, or as a buffer pool so small that every read goes to disk and the symptom looks like a storage problem. Two counters settle it.
Memory Grants Pending is the one that matters and it is close to binary. Any value above 0, sustained, means queries are queueing for workspace memory before they are allowed to start, and every one of them gets reported by a user as “it just hangs”. That queue appears in waits as RESOURCE_SEMAPHORE, and Troubleshoot RESOURCE_SEMAPHORE Waits is the branch for it.
Page life expectancy is a trend, not a threshold. The number people quote at you comes from an article written when a large server had 4 GB of RAM, so do not act on a single reading, act on a reading that fell and stayed down. If a grant fails rather than just queueing you get errors 701 and 8645, which are two different failures: 701 could not allocate the memory at all, 8645 queued for it and timed out. Get Memory Configuration and Usage shows what the instance is configured to use against what it has actually taken.
SELECT RTRIM(counter_name) AS counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE RTRIM(counter_name) IN
('Page life expectancy','Memory Grants Pending','Memory Grants Outstanding')
AND RTRIM(object_name) LIKE '%Manager%';Step 6: Disk
Storage is where the blame usually lands and where it is least often deserved, because a plan scanning 40 million rows it did not need to read produces exactly the same disk symptom as a slow array. Check file latency first, and check it per database, because one database on one volume is a far more common finding than the whole array being sick.
Get Database IO Usage gives you read and write latency per database, rolled up from the per-file DMV, which is the measurement a storage team will ask for before they look at anything. In waits, PAGEIOLATCH_SH means the engine is sitting waiting for a data page to arrive from disk, and WRITELOG means commits are waiting on the log, which is a different volume and usually a different conversation. If the latency is bad only during a window, go to Step 7 and find out what runs in that window.
Step 7: What Changed
This is the step for the complaint with no resource behind it at all: it was fine on Friday, nothing was released, and today one report takes 11 minutes. Nothing is blocked, no counter is interesting, and the server is bored. Something changed that was not a deployment.
The usual suspects, in the order they are worth checking. A plan changed: Get Query Store Regressions and Forced Plans names queries whose plan regressed and when, which is the fastest answer to “it was fast yesterday” there is. The plan was built for the wrong value: Parameter Sniffing in SQL Server is why the same query is fast for one customer and slow for another, with a worked example. Statistics went stale after a large load, so the optimiser is costing a table it still thinks is small: Get Statistics Health. Somebody changed the schema or a setting: Get Schema Change History covers the first, and it is worth confirming the build did not move under you with Get Patch Level.
If Query Store is not on, turn it on before the next incident rather than during this one. How to Check if Query Store Is Enabled is a 10 second check, and Query Store is the single most useful thing you can enable on a database you have to answer for.
Frequently Asked Questions
SQL Server is slow all of a sudden. Where do I start?
The query was fast yesterday and slow today, and nothing was deployed.
It is slow only from the application. The same query is instant in SSMS.
How do I know if it is the server or the network?
CPU is at 100 percent. Does that mean I need more cores?
Everything is slow but no single query looks bad.
Should I just restart SQL Server?
Related
- DBA Scripts: Performance and Troubleshooting, every performance and troubleshooting script on this site, grouped by what it answers
- SQL Server Wait Types Library, the lookup for whatever Step 3 returns
- DBA Scripts: Collect Performance Baselines, so the next incident has a before to compare against
- Cannot Connect to SQL Server, the other half of this page: nobody can get on at all
Leave a Reply