SpDev Install free

Monitoring SQL Server performance with built-in tools

Monitor SQL Server performance without buying anything: what's running now, who is blocking whom, performance counters, Query Store regressions and Extended Events, with queries you can run today.

By Areeb Ahmer, . 4 minute read.

SQL Server ships with everything you need to monitor it: dynamic management views (DMVs) for what’s happening now, performance counters for trends, Query Store for query history, and Extended Events for tracing. Commercial monitoring tools are built on the same sources.

This article gives a working query for each question you’ll ask during an incident and afterwards. They need VIEW SERVER STATE (VIEW SERVER PERFORMANCE STATE on SQL Server 2022).

What’s running right now?

SELECT
    r.session_id,
    s.login_name,
    s.program_name,
    DB_NAME(r.database_id)                AS database_name,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time                           AS wait_ms,
    r.blocking_session_id,
    r.cpu_time                            AS cpu_ms,
    r.total_elapsed_time                  AS elapsed_ms,
    r.logical_reads,
    SUBSTRING(t.text, r.statement_start_offset / 2 + 1,
        (CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text) ELSE r.statement_end_offset END
         - r.statement_start_offset) / 2 + 1) AS current_statement
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

current_statement is the statement running now, even inside a long procedure. To see its live plan, with rows flowing through each operator, add CROSS APPLY sys.dm_exec_query_statistics_xml(r.session_id); lightweight profiling, which it needs, is on by default since SQL Server 2019.

This view is also how SpDev Pro measures each statement of a profiled procedure: it reads the cpu_time, total_elapsed_time and logical_reads of its own session between statements.

Who is blocking whom?

When users say “everything is stuck”, it’s often one session holding locks:

SELECT
    r.session_id               AS blocked_session,
    r.blocking_session_id      AS blocked_by,
    r.wait_type,
    r.wait_time / 1000         AS waiting_seconds,
    DB_NAME(r.database_id)     AS database_name,
    t.text                     AS blocked_sql
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;

Follow blocked_by until you reach a session that isn’t itself blocked: the head of the chain. Then look at that session. It’s often idle with an open transaction (an application that began a transaction and never committed):

SELECT s.session_id, s.login_name, s.program_name, s.status, s.last_request_end_time,
       s.open_transaction_count
FROM sys.dm_exec_sessions AS s
WHERE s.open_transaction_count > 0
  AND s.status = 'sleeping';

Performance counters, from T-SQL

The counters you see in Windows Performance Monitor are also in a DMV, which works on Linux and in containers too:

SELECT RTRIM(object_name) AS object_name, RTRIM(counter_name) AS counter_name, cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name IN (N'Batch Requests/sec', N'SQL Compilations/sec', N'SQL Re-Compilations/sec',
                       N'Page life expectancy', N'Memory Grants Pending', N'Processes blocked')
  AND (object_name LIKE N'%SQL Statistics%' OR object_name LIKE N'%Buffer Manager%'
       OR object_name LIKE N'%Memory Manager%' OR object_name LIKE N'%General Statistics%');

Counters whose name ends in /sec are cumulative: sample them twice, ten seconds apart, and divide the difference by ten. Worth tracking over time:

  • Batch Requests/sec: your workload. Compare everything else against it.
  • SQL Compilations/sec: above roughly 10% of batch requests, something isn’t reusing plans (ad hoc SQL with literals, or OPTION (RECOMPILE) everywhere).
  • Page life expectancy: how long a page stays in memory. Watch for sudden drops: a large scan just pushed everything else out.
  • Memory Grants Pending: anything above 0 for long means queries are waiting for memory to start.
  • Processes blocked: blocking, as it happens.

Which queries got slower?

Query Store tracks each query’s plans and their performance over time, so it can answer “what changed?”:

SELECT TOP (10)
    q.query_id,
    OBJECT_NAME(q.object_id)        AS procedure_name,
    COUNT(DISTINCT p.plan_id)       AS plans,
    MIN(rs.avg_duration) / 1000     AS best_avg_ms,
    MAX(rs.avg_duration) / 1000     AS worst_avg_ms,
    MAX(qt.query_sql_text)          AS query_text
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
GROUP BY q.query_id, q.object_id
HAVING COUNT(DISTINCT p.plan_id) > 1
ORDER BY MAX(rs.avg_duration) - MIN(rs.avg_duration) DESC;

A query with several plans and a big gap between the best and worst is a plan regression candidate. SSMS’s Regressed Queries report shows the same, with the plans side by side and a button to force the good one.

Waits over time

sys.dm_os_wait_stats counts since the last restart. To see what the server waited on during a specific hour, snapshot it into a table on a schedule and compare snapshots. Query Store also records waits per query, by category, in sys.query_store_wait_stats, which answers “what did this query wait on?” See performance tuning for how to read the wait types.

Tracing: Extended Events

For an event-by-event record (every call slower than one second, every deadlock, every error), use Extended Events. SQL Server Profiler still exists but is deprecated, and much heavier; the Profiler article has a ready-made session that replaces the usual Profiler trace.

Deadlocks are already captured for you: the system_health session, which runs by default, records every deadlock graph.

SELECT CAST(event_data AS xml) AS deadlock
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
WHERE object_name = N'xml_deadlock_report';

A minimal monitoring routine

  • Daily: the top 10 queries by total CPU and by total reads from Query Store. Anything new at the top deserves a look.
  • Weekly: regressed queries; waits compared with last week; unused and missing indexes.
  • During an incident: what’s running, who’s blocking, what are they waiting on. In that order.

Monitoring tells you which query is the problem. To understand why a stored procedure is slow, you need its statements one by one. That’s what SpDev Pro does on a development database: profile the procedure, see which line takes the time, and read the problem from its actual plan.