"The application is slow — is it the database?" The CPU on the SQL Server box is at 95%, users are waiting, and you need to know what is running right now, and what's blocking what. Here's the triage sequence DBAs use, all with built-in views.
1. What's running right now?
SELECT r.session_id, r.status, r.command, r.wait_type, r.wait_time,
r.blocking_session_id, r.cpu_time, r.total_elapsed_time /1000AS elapsed_s,
r.logical_reads, DB_NAME(r.database_id) AS db,
s.login_name, s.host_name, s.program_name,
SUBSTRING(t.text, r.statement_start_offset /2+1,
(CASE r.statement_end_offset WHEN-1THEN DATALENGTH(t.text)
ELSE r.statement_end_offset END- r.statement_start_offset) /2+1) AS running_statement,
p.query_plan
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
WHERE s.is_user_process =1AND r.session_id <> @@SPID
r.total_elapsed_time ;
Error 8152 never said which column. Message 2628 on SQL Server 2019+ (or trace flag 460 on 2016/2017) names it. Then fix the data or the column, and watch for silent truncation.
This shows the exact statement each session is executing (not just the batch), how long it's been running, what it's waiting on, and its plan.
Many DBAs install the free community procedure sp_WhoIsActive (by Adam Machanic), which wraps all of this, including blocking chains and the waiting statement, in one call. It's well worth having on every server.
2. Is it blocking?
A non-zero blocking_session_id means the session is waiting for a lock. Follow the chain to the head blocker, the session that blocks others but isn't blocked itself:
SELECT s.session_id, s.login_name, s.host_name, s.program_name, s.status,
s.last_request_end_time, t.text AS last_sql
FROM sys.dm_exec_sessions s
LEFTJOIN sys.dm_exec_connections c ON c.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) t
WHERE s.session_id IN (SELECT blocking_session_id FROM sys.dm_exec_requests WHERE blocking_session_id <>0)
AND s.session_id NOTIN (SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id <>0);
A head blocker with status sleeping and an old last_request_end_time is the classic case: a transaction left open by an application or an SSMS window. Ending it:
KILL 87; -- rolls back that session's open transaction
A rollback can take as long as the work did. KILL 87 WITH STATUSONLY shows its progress. Then fix the code that left the transaction open.
3. What's been expensive recently?
For "slow all morning" rather than "slow right now", look at aggregated statistics:
SELECT TOP (20)
qs.total_worker_time /1000AS total_cpu_ms,
qs.execution_count,
qs.total_worker_time / qs.execution_count /1000AS avg_cpu_ms,
qs.total_logical_reads / qs.execution_count AS avg_reads,
SUBSTRING(t.text, qs.statement_start_offset /2+1, 200) AS statement
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t
ORDERBY qs.total_worker_time DESC;
These stats reset when plans leave the cache or the server restarts. Query Store keeps history per query and plan. In SSMS, open the database → Query Store → Top Resource Consuming Queries, and use Regressed Queries for "it was fast yesterday" (often a plan change you can fix by forcing the previous plan).
CXPACKET / CXCONSUMER: parallelism, often large scans;
PAGEIOLATCH_*: reading from disk (missing indexes, not enough memory);
LCK_M_*: blocking;
WRITELOG: transaction log disk latency;
SOS_SCHEDULER_YIELD: CPU pressure;
RESOURCE_SEMAPHORE: waiting for memory grants.
These numbers accumulate from startup. Take two snapshots a few minutes apart and compare them to see what's happening now.
5. Then fix the actual query
Open the plan of the worst statement and look for scans on big tables, key lookups repeated thousands of times, estimated vs. actual row mismatches (stale statistics or parameter sniffing), and implicit conversion warnings. Fixing the top few queries usually fixes the "slow server".
Checklist
sys.dm_exec_requests (or sp_WhoIsActive) shows what's running and what it's waiting on.
Follow blocking_session_id to the head blocker; a sleeping one is an open transaction, so KILL it and fix the code.
Use dm_exec_query_stats and Query Store for the most expensive and regressed queries over time.
Compare wait-stat snapshots to find the bottleneck, then tune the top offenders' plans.
A query-first method: find the expensive queries, read the plan for signs the missing index DMVs never report, build the index online safely, then verify and roll back.