Conclusion: Monitoring SQL Server 2025 with SSMS

This module opened with a simple premise: SQL Server will tell you how it feels well before it reaches an alarm phase, if you know where to look. Eleven lessons later, that "where to look" turned out to be a genuinely large surface, from a single bare SELECT @@VERSION; typed into a fresh connection, all the way to a persistent Query Store history or a continuously running Extended Events session. This conclusion ties those eleven lessons together rather than repeating them, with links back to each one for anything worth revisiting in full.

Lesson 1: Monitoring SQL Server with SSMS	Why proactive monitoring matters, and an overview of SSMS as the native hub for it: Activity Monitor, Query Store, execution plans, Extended Events, Always On dashboards, PerfMon, and SQL Server Agent alerting.
Lesson 2: Monitor SQL Server Connections	sp_who and the modern DMV alternative, sys.dm_exec_sessions/sys.dm_exec_connections/sys.dm_exec_requests, plus session status, connection pooling, and KILL.
Lesson 3: Monitoring Connections with SSMS	Activity Monitor's Processes pane as the graphical counterpart to sp_who, the permissions behind it, and Registered Servers for monitoring more than one instance at once.
Lesson 4: Monitor SQL Server Locks	Lock modes and resource types through sys.dm_tran_locks, the deprecated sp_lock it replaced, lock escalation, and SQL Server 2025's Optimized Locking (TID locking and Lock After Qualification).
Lesson 5: Monitoring Locks with SSMS	What replaced SQL-EM's retired lock screens: Activity Monitor, the Blocking Transactions standard report, and sys.dm_tran_locks queried directly, plus a custom Extended Events session for catching deadlocks after the fact.
Lesson 6: Overall System Performance for SQL Server	sp_monitor's quick, zero-setup instance snapshot, its cumulative-since-startup caveat, and sys.dm_os_performance_counters for deeper, named-counter detail.
Lesson 7: Monitoring SQL Server with DBCC	DBCC CHECKDB as the one tool in this module aimed at integrity rather than performance, plus the retirement of dbcc memusage and dbcc proccache in favor of sys.dm_os_memory_clerks and sys.dm_exec_cached_plans.
Lesson 8: Monitor the SQL Server Data Cache	The undocumented dbcc buffer command retired in favor of sys.dm_os_buffer_descriptors, used to find which databases, tables, and indexes are actually consuming buffer pool memory.
Lesson 9: DMVs, DMFs, Query Store, and Extended Events	A full rebuild around four current tools after retiring DBCC PERFMON, dbcc outputbuffer, and dbcc inputbuffer outright; the DMV/DMF distinction named explicitly for the first time.
Lesson 10: Trace Flags	Numbered switches that change behavior rather than report on it: deadlock logging flags 1204/1222, log-suppression flag 3226, and dbcc traceon/traceoff/tracestatus.
Lesson 11: Built-in Functions	The classic @@ scalar functions, the literal building blocks sp_monitor queries internally, and where a DMV or SERVERPROPERTY() gives a clearer, live answer instead.

Read end to end, this module's eleven lessons settle into four layers, each answering a different kind of question, and each with its own moment to be reached for.

The first layer is the near-instant scalar check: @@VERSION, @@SERVERNAME, and the rest of Lesson 11's functions, plus SERVERPROPERTY() alongside them. These answer "what is" questions, cost nothing, and need no setup, at the price of mostly reporting totals accumulated since startup rather than current state.

The second layer is the formatted snapshot-and-difference report: sp_monitor from Lesson 6 and Activity Monitor from Lessons 1 and 3. sp_monitor is, quite literally, several of Lesson 11's scalar functions queried twice and subtracted; Activity Monitor is the same underlying session and request data, refreshed automatically instead of snapshotted by hand. Both answer "how is the instance doing right now" at a coarser grain than the next layer down.

The third layer is structured, on-demand detail: the Dynamic Management Views and Functions that run through nearly every lesson in this module, sys.dm_exec_sessions, sys.dm_tran_locks, sys.dm_os_buffer_descriptors, sys.dm_exec_input_buffer(), and the rest. These answer precise questions about a specific session, lock, or resource, and Lesson 9 is where this module finally named the View-versus-Function distinction that had been in use, unlabeled, since Lesson 4.

The fourth layer is persistent, structured history: Query Store and Extended Events, both introduced in Lesson 1 and returned to repeatedly afterward, most thoroughly in Lesson 9. Nothing in the first three layers can answer "did this get worse over the last two weeks" or "catch the next occurrence of something intermittent even if nobody is watching"; that is this layer's entire reason to exist.

A server rarely needs all four layers running at once, and no lesson in this module argued that a newer layer makes an older one obsolete. sp_monitor did not stop being useful once DMVs existed, and trace flags did not stop being useful once Extended Events existed. Each layer earns its place by matching a specific kind of question, not by being the newest option available.

A second pattern ran underneath the four layers, visible mostly in hindsight: roughly half of this module's lessons started from legacy content that needed to be checked against current documentation before being taught as current practice, and the outcome of that check was different almost every time.

sp_lock (Lesson 4) turned out to be formally deprecated, documented, supported, scheduled for removal, and was replaced with sys.dm_tran_locks. dbcc memusage and dbcc proccache (Lesson 7) and dbcc buffer (Lesson 8) turned out to be undocumented, never officially supported at all, and were replaced with sys.dm_os_memory_clerks, sys.dm_exec_cached_plans, and sys.dm_os_buffer_descriptors respectively. DBCC PERFMON, dbcc outputbuffer, and dbcc inputbuffer (Lesson 9) were undocumented 1990s-era commands with no modern equivalent at all for one of the three, which is why that lesson was rebuilt from the ground up around DMVs, Query Store, and Extended Events rather than given another side-by-side correction.

Trace flags (Lesson 10) and the built-in scalar functions (Lesson 11) went the other way: both checked out as current and documented on review, needing only cleanup, corrected descriptions, and a couple of filled-in gaps rather than retirement. The lesson here is not "everything old is deprecated" any more than it is "everything old still works." It is that the two conclusions require the same step, checking the specific claim against what SQL Server 2025 actually documents today, rather than assuming either outcome from how old a command looks or how long it has been in a course.

Several lessons in this module pointed at features that are new, or newly relevant, in SQL Server 2025 itself rather than simply at a tool this course happened to modernize. Worth collecting in one place:

• Optimized Locking (Lesson 4): Transaction ID locking and Lock After Qualification, together reducing how long and how broadly a modifying transaction holds row and page locks. Off by default, and requiring Accelerated Database Recovery already enabled on the database.
• Intelligent Query Processing in Query Store (Lesson 1): Optional Parameter Plan Optimization and Cardinality Estimation feedback surfaced directly in Query Store reports, alongside secondary-replica statistics that now survive an Always On failover rather than resetting.
• Native VECTOR and JSON operators in execution plans (Lesson 1): DiskANN vector index seeks and JSON index scans appear in SSMS 22's plan viewer with the same cost estimates and warnings as any traditional seek or scan, for workloads that have started adopting either feature.
• Resource Governor workload-group throttling (Lesson 1): now available in Standard Edition, not just Enterprise, which changes who this feature is realistically available to.
• The built-in system_health Extended Events session (Lessons 9, 10): not new to SQL Server 2025 specifically, but repeatedly the better first stop in this module than either a trace flag or a custom session, since it runs by default with nothing to configure.

None of these replace the fundamentals covered everywhere else in this module, session monitoring, lock inspection, integrity checking, and the rest work the same way they always have. They are the specific places where SQL Server 2025's own engine changes show up directly in the tools this module already teaches, rather than requiring a separate, version-specific course of their own.

Permissions came up often enough across this module to be worth stating once, plainly, rather than leaving scattered across eleven lessons. VIEW SERVER STATE, a server-level permission, is what Activity Monitor and nearly every DMV query in this module actually depend on; without it, a login can query its own session's activity but sees nothing about anyone else's, and Activity Monitor itself may open empty or refuse to open at all. Membership in the sysadmin fixed server role includes it implicitly; anyone else needs it granted explicitly.

sp_monitor, notably, does not require it; its more permissive execution requirements are part of why it remains a reasonable very-first check even for a login that has not yet been granted broader monitoring access. Terminating someone else's session with KILL requires a step beyond visibility too, either membership in sysadmin or processadmin, or the ALTER ANY CONNECTION permission specifically, since seeing a problem and being allowed to act on it are deliberately two separate grants.

Collected into one place, the "choosing between" guidance scattered across this module's individual lessons comes down to a short set of questions worth asking before reaching for anything:

If the question is…	…reach for
What version, server name, or build is this?	@@VERSION/@@SERVERNAME or SERVERPROPERTY() (Lesson 11)
Is the instance under any pressure right now, with zero setup?	sp_monitor (Lesson 6)
Who is connected, and is anyone blocked?	sp_who or Activity Monitor's Processes pane (Lessons 2, 3)
Exactly what is locked, by whom, and in what mode?	sys.dm_tran_locks (Lessons 4, 5)
Is the data itself structurally sound?	DBCC CHECKDB (Lesson 7), and only this; nothing else in the module checks integrity
Which tables or indexes are consuming buffer pool memory?	sys.dm_os_buffer_descriptors (Lesson 8)
Did a query get slower, and when?	Query Store (Lessons 1, 9)
Catch an intermittent event even when nobody is watching?	A custom Extended Events session, or the built-in system_health session (Lessons 5, 9, 10)
Suppress routine log noise, or route DBCC output somewhere specific?	A trace flag (Lesson 10)

No row in this table is a permanent assignment. A real investigation typically starts broad, sp_monitor or Activity Monitor, and narrows only as far as the question actually requires, sys.dm_tran_locks for a specific lock, sys.dm_exec_sql_text for the statement behind it, Query Store if the question turns out to be about a regression rather than a single moment.

Each lesson in this module proposed its own small routine; stitched together, they form one coherent practice rather than eleven separate habits.

A daily pass starts broad and cheap: sp_monitor or a glance at Activity Monitor's Overview and Processes panes, a check of the SQL Server error log for anything new, and confirmation that overnight Agent jobs and alerts fired as expected. Most days, this is where the routine ends, since a clean result on all three is a genuine signal that nothing urgent needs attention.

When something does stand out, the routine narrows rather than restarts: a blocked session found in Activity Monitor gets a sys.dm_tran_locks query to see exactly what is held, then sys.dm_exec_sql_text to see what statement is responsible, before any decision about KILL gets made. A Query Store report flagging a regressed query gets compared against its prior plan before sp_query_store_force_plan is used, and only as a stabilizing measure rather than a first response.

A weekly or scheduled pass covers what a daily glance cannot: DBCC CHECKDB during a maintenance window, a review of Query Store's Regressed Queries report across the full week rather than a single moment, and a check of whatever custom Extended Events sessions are running, deadlock capture chief among them, for anything caught since the last review. SQL Server Agent and Database Mail, covered in this module's first lesson, tie the whole routine together: alerts cover the hours nobody is actively watching, and a scheduled job can turn even sp_monitor's single snapshot into a logged trend line, as Lesson 11 demonstrated.

None of this requires every tool in this module running simultaneously on every instance. It requires knowing which of the four layers a given moment's question belongs to, and reaching for that layer first rather than whichever tool happens to be most familiar.

A short, composite scenario shows several of this module's lessons working together rather than in isolation. A daily sp_monitor check (Lesson 6) comes back showing io_busy at an unusually high percentage for the interval, a signal worth following rather than dismissing. Switching to Activity Monitor's Processes pane (Lesson 3) shows one session, a reporting query, sitting in a suspended state with several other sessions showing non-zero values in Blocked By, all pointing at it.

A sys.dm_tran_locks query (Lesson 4) against that session's spid shows a single OBJECT-type lock in IX mode rather than the many KEY-type row locks that session's query would normally take, a sign of lock escalation on a large table rather than anything unusual about the query's logic. sys.dm_exec_sql_text (Lesson 2), joined against that session's current request, confirms it is a large, unfiltered report query someone scheduled to run during business hours by mistake.

Rather than killing the session outright, which would roll back a large transaction and cost as much time as it already spent, the better move is checking Query Store (Lesson 9) for this query's history, confirming it normally completes quickly against a filtered date range, and then addressing the actual cause, the missing filter, in the report itself, while a trace flag or a quick DBCC CHECKDB finding (Lessons 10, 7) play no part in this particular investigation at all. The point of walking through it is not that every tool gets used every time, it is that knowing which tool answers which part of the question, in which order, is what turns a vague "something feels slow" into a specific, resolvable finding within minutes rather than hours.

SSMS and the DMV, Query Store, and Extended Events surfaces covered in this module handle the overwhelming majority of day-to-day monitoring for a single instance or a small fleet, at no additional licensing cost. As Lesson 1 noted, larger environments sometimes layer a dedicated third-party monitoring product on top of this foundation rather than instead of it, for cross-instance dashboards and longer retention than a homegrown logging table can comfortably provide. Understanding what SSMS already shows directly, which is what this module has focused on, makes any such product easier to configure and interpret later, rather than becoming a black box standing between you and the instance it is watching.

Each lesson linked above remains available individually for anything worth revisiting in full, with its own worked examples, query syntax, and, where this module found one, the specific documented replacement for whatever older command it retired along the way.

Click the Exercise link below to see how well you've mastered the concepts in this module.
Sql-em - Exercise