Monitoring SQL Server with DMVs, DMFs, Query Store, and Extended Events

DBCC PERFMON was a genuine, if undocumented, SQL Server command from the 6.5/7.0 era, surfacing I/O, cache, thread, and wait statistics directly through T-SQL. dbcc outputbuffer and dbcc inputbuffer are its contemporaries, showing the last network buffer sent to or from a client. None of the three belong in a current monitoring lesson. DBCC PERFMON's job is now split across the DMVs this module has already used, sys.dm_os_performance_counters and sys.dm_os_wait_stats among them, and Windows Performance Monitor, both covered in this module's first lesson. dbcc inputbuffer already has a documented modern replacement covered two lessons ago, sys.dm_exec_input_buffer(). dbcc outputbuffer has no meaningful modern equivalent, because inspecting raw network buffer bytes byte by byte is not something current monitoring practice does; nothing in SQL Server 2025's documented surface replaces it, because nothing needed to.

Rather than another historical footnote treatment, like the ones given to dbcc memusage, dbcc proccache, and dbcc buffer in the previous two lessons, this lesson retires all three commands outright and replaces the lesson's entire scope with four tools genuinely worth knowing for SQL Server 2025: Dynamic Management Views, Dynamic Management Functions, Query Store, and Extended Events.

It is worth being honest about why this particular lesson needed a full rewrite rather than another side-by-side correction. The earlier lessons each replaced one legacy command with one modern DMV, a reasonably clean swap. This lesson's legacy content instead tried to document three different undocumented commands at once, DBCC PERFMON's multi-section output, dbcc outputbuffer's raw memory dump, and dbcc inputbuffer's buffer contents, none of which share a single clean modern replacement. The honest modernization is not a one-for-one table; it is a different organizing idea entirely, built around the categories of tool SQL Server 2025 actually offers rather than the specific commands a much older version once had.

This module has used both kinds of Dynamic Management Object repeatedly without ever naming the difference between them. A Dynamic Management View returns a result set directly when queried, no arguments required: sys.dm_tran_locks, sys.dm_exec_sessions, sys.dm_os_buffer_descriptors, and sys.dm_exec_query_stats, all covered earlier in this module, are DMVs. A Dynamic Management Function takes one or more arguments and is invoked like a function, typically joined with CROSS APPLY against a row source that supplies those arguments: sys.dm_exec_input_buffer(session_id, request_id) and sys.dm_exec_sql_text(sql_handle), both also covered earlier, are DMFs.

SELECT s.session_id, ib.event_info
FROM sys.dm_exec_sessions AS s
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib;

The distinction matters practically rather than just taxonomically: a DMV can be queried on its own as a complete thought, while a DMF almost always needs to be driven from something else, usually a session or request list, to be useful at all. Trying to query sys.dm_exec_input_buffer without a session ID to feed it simply does not work; trying to query sys.dm_tran_locks without anything else does, and returns every lock currently held on the instance.

sys.dm_db_index_physical_stats, the modern, documented replacement for the deprecated DBCC SHOWCONTIG command, is a useful edge case here: it is actually a DMF rather than a DMV, even though its name does not telegraph that the way the function-call syntax of sys.dm_exec_input_buffer() does. It requires a database ID and takes several further optional arguments narrowing the scope to a specific object, index, or partition. Not every DMF announces itself with parentheses in casual conversation the way this lesson's other examples do, which is exactly why naming the underlying distinction is more useful than memorizing which specific names take arguments.

Query Store, enabled per database, automatically captures query text, execution plans, and runtime statistics over time, exactly the historical trend that a single DMV snapshot cannot provide on its own, no matter how many times it is queried in a row.

ALTER DATABASE CurrentDb SET QUERY_STORE = ON;

Its catalog views work together to answer the question "which queries got slower, and when": sys.query_store_query identifies each distinct query, sys.query_store_query_text holds the actual query text, sys.query_store_plan tracks every execution plan that query has used over time, and sys.query_store_runtime_stats records how each plan actually performed.

SELECT q.query_id, qt.query_sql_text, rs.avg_duration, rs.last_execution_time
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats AS rs ON p.plan_id = rs.plan_id
ORDER BY rs.avg_duration DESC;

A query that shows multiple plans in this history, with a clear jump in avg_duration coinciding with a plan change, is a regressed query: one that got slower not because the data or the query text changed, but because the optimizer chose a different, worse plan. SSMS's Query Store reports surface this visually, and once identified, a known-good earlier plan can be forced:

EXEC sp_query_store_force_plan @query_id = 42, @plan_id = 17;

This is something no DMV snapshot, however cleverly queried, could ever offer: DMVs report the present, Query Store remembers the past, and a plan regression is, by definition, only visible by comparing the two.

Query Store's history is not unlimited, and its retention is configurable rather than fixed. MAX_STORAGE_SIZE_MB caps how much space its captured data can occupy, STALE_QUERY_THRESHOLD_DAYS controls how long a query's history is kept before being cleaned up, and SIZE_BASED_CLEANUP_MODE determines whether older data is automatically purged once the size cap is reached. A Query Store configured with too short a retention window can quietly lose exactly the history needed to catch a regression that took two weeks to become obvious, which makes these settings worth checking rather than leaving at whatever default a database happened to inherit.

Query Store's history is not limited to execution duration either. sys.query_store_wait_stats records, for each captured query and plan, how much time was spent waiting on each wait category, locking, I/O, memory grants, and so on, giving the same kind of wait breakdown sys.dm_os_wait_stats provides instance-wide, but scoped down to one specific query over time. A query whose average duration crept up without any visible plan change in sys.query_store_plan is exactly the case this view is for: the plan stayed the same, but the query started waiting longer on something, locking contention from a new blocking session, or I/O pressure from a growing table, and sys.query_store_wait_stats is often the fastest way to see which.

SQL Server's Automatic Plan Correction feature, built directly on top of Query Store, takes this one step further: once enabled, it can detect a regression itself and force the last known-good plan automatically, without waiting for a human to notice the Query Store report. It is still worth reviewing what it forces after the fact rather than treating it as fully hands-off, since an automatically forced plan is a response to a symptom, not a guarantee that the underlying cause, stale statistics, a parameter sniffing issue, or a genuine data change, has actually been addressed.

Extended Events, introduced in this module as the replacement for deprecated SQL Server Profiler and used again for lock monitoring, is built from a small set of concepts worth naming explicitly rather than treating as magic syntax to copy and paste:

• Events are something that happened: a lock acquired, a statement completed, a deadlock occurred.
• Actions are extra data collected automatically whenever a matching event fires, such as the SQL text or the session ID responsible.
• Predicates filter which occurrences of an event are actually captured, so a session can watch for deadlocks on one specific database rather than the whole instance.
• Targets are where the captured data actually goes, typically a file, though an in-memory ring buffer target also exists for short-lived, lightweight sessions.

A session is simply a named combination of these four pieces. The LockMonitoring session built earlier in this module, capturing lock_acquired, lock_timeout, and lock_deadlock events into a file target, is a complete, working example of all four concepts assembled together, even though it was introduced at the time purely as a Profiler replacement rather than explained piece by piece. Looking at it again with the vocabulary now in hand:

CREATE EVENT SESSION LockMonitoring ON SERVER
ADD EVENT sqlserver.lock_acquired,
ADD EVENT sqlserver.lock_timeout,
ADD EVENT sqlserver.lock_deadlock
ADD TARGET package0.event_file(SET filename = N'LockMonitoring');

The three ADD EVENT lines are the events being watched for. No predicate narrows them in this particular session, so every occurrence on the whole instance is captured; a predicate could have been added to limit capture to one database, using a clause like WHERE sqlserver.database_name = N'CurrentDb'. The single ADD TARGET line names the file target everything gets written to. No explicit actions were added in this example, though they could be, to automatically attach extra context, the client application name or the SQL text, to every captured event without needing a separate join afterward.

This same four-part structure applies to any Extended Events session, not just a lock-focused one. A session capturing long-running queries, for instance, would swap the events for something like sqlserver.sql_statement_completed, add a predicate filtering to statements over a duration threshold, and otherwise follow exactly the same shape.

A custom session is not the only Extended Events session on a SQL Server 2025 instance. A built-in session named system_health starts automatically with the service and runs continuously, with no setup required, capturing deadlocks, long-running queries above an internal threshold, and a variety of error conditions into a small ring-buffer and file target combination. It is worth knowing this session exists and checking it first, before building a brand-new custom session from scratch, since the deadlock or timeout just reported by a user may already be sitting in system_health's captured data, queryable through its target without having to reproduce the problem again to catch it.

DMVs answer "what is happening right now," queried directly with no setup required. DMFs answer a narrower version of the same question, requiring a specific argument, usually a session or statement identifier obtained from a DMV first. Query Store answers "what happened over time, and did it get worse," a question DMVs structurally cannot answer because they have no memory between queries. Extended Events answers "capture this specific kind of event whenever it happens, even if nobody is watching," useful for anything intermittent enough that a live DMV query is likely to miss it entirely.

In practice, these four are rarely used in isolation. A regressed query gets spotted through Query Store, investigated further with a DMV query against sys.dm_exec_query_stats, and if the regression turns out to be intermittent rather than constant, an Extended Events session captures it the next time it happens rather than waiting for someone to notice live.

It is also worth placing these four alongside everything else this module has already covered, rather than treating them as a separate, final category. sp_monitor and Activity Monitor answer instance-wide and session-wide questions respectively, without needing any of this lesson's four tools at all. sys.dm_tran_locks and sys.dm_os_buffer_descriptors, both DMVs, were already examples of this lesson's first category well before this lesson named the category they belonged to. Nothing covered earlier in this module becomes obsolete now that DMVs and DMFs have been named explicitly; the naming simply makes it clearer which of the two categories a given tool, old or new, actually falls into the next time one is encountered.

Overhead is also worth weighing when choosing between them, rather than assuming all four are equally free to leave running. A single DMV or DMF query is cheap, a snapshot taken and discarded the moment it is read, so there is little reason to hesitate before running one. Query Store carries a small, ongoing overhead by design, since it is persisting execution plans and statistics to disk on every database where it is enabled, though this overhead is deliberately kept light enough that Microsoft recommends leaving it on in production rather than treating it as a diagnostic tool to switch on only when trouble starts. Extended Events sessions were built from the ground up to replace Profiler specifically because Profiler's overhead made it unsafe to leave running continuously on a busy production instance; a well-scoped Extended Events session, filtered with a tight predicate and writing to an efficient target, avoids that problem, but a broad session with no predicate capturing a high-frequency event across the whole instance can still add meaningful overhead of its own. Scoping a session tightly is not just good practice for readability; it is what keeps Extended Events living up to its reputation as the lightweight choice.

Of the four, DMVs and DMFs are the reach-for-it-now tools, used the same way sp_who and sys.dm_tran_locks have been throughout this module: on demand, during a live investigation. Query Store, once enabled, runs continuously in the background with no ongoing effort required beyond occasionally reviewing its reports, making it the closest thing in this lesson to a true set-and-forget monitoring tool. Extended Events sessions sit in between: set up once for a specific, named concern, lock waits, a particular error, then left running until that concern is resolved or the session is no longer needed, rather than kept on indefinitely by default.

A worked scenario pulls the whole lesson together. Suppose a weekly review of Query Store reports in SSMS flags a stored procedure whose average duration tripled three days ago, with a clear plan change visible in sys.query_store_plan right at that point. The next step is not immediately forcing the old plan; it is understanding why the plan changed in the first place, which means pulling up the current plan with sys.dm_exec_query_stats and comparing it against what Query Store recorded for the earlier, faster plan. If the cause turns out to be a parameter value that only occasionally causes the optimizer to choose badly, rather than something that happens on every execution, an Extended Events session filtered to that specific procedure, capturing sqlserver.sql_statement_completed with a duration predicate, is the right way to catch the next occurrence with full context rather than waiting for the next weekly review to notice the pattern again.

Only after that investigation is sp_query_store_force_plan actually the right call, and only as a stabilizing measure while the underlying parameter sensitivity issue gets addressed properly, not as the first or only response to a regression.

The next lesson covers DBCC trace flags^[1], switches that change SQL Server's default behavior for diagnostic or compatibility purposes, still a documented and current part of DBCC despite this lesson's wholesale retirement of the performance-monitoring commands that used to share the DBCC name.

[1]Trace flags: a SQL Server option that changes the engine's default behavior, typically for diagnostics or backward compatibility.