Lesson 1

As a system administrator, one of your core responsibilities is monitoring the health of SQL Server. Many tools can provide different pieces of this picture, but working through SQL Server Management Studio (SSMS) gives you consistent information no matter which client platform you are sitting at, since SSMS is native to the platform itself.

SQL Server remains one of the most widely deployed backend platforms for business-critical systems, and that popularity means more people than ever are affected when a database slows down or becomes unavailable. As a database administrator, it is your responsibility to know the health of your SQL Server instance so you can take proactive action before small problems become outages. The good news is that, with the right tools in place, your system will tell you how it feels well before it reaches an alarm phase. Performance behavior often reveals problems that are not yet visible to the end user. If, for example, the response time of a particular query slows from 100 milliseconds to 500 milliseconds for no obvious reason, the end user will not notice, but you should.

This principle applies whether you support a system with thousands of concurrent users or one with only a handful. A database that goes down with ten users connected will still generate urgent calls from people who expect it to be available. None of this is meant to create anxiety; being a SQL Server DBA is one of the more rewarding roles in IT, and that comfort comes from knowing what is actually happening inside the system you are responsible for. Reliable monitoring should be in place starting before a new system goes live, not bolted on after the first outage. Part of the job is making the case to management that investing in monitoring early in a project's lifecycle is not a luxury item; it is a basic cost of running the system responsibly.

As a SQL Server system administrator, you need to verify the health of your instance and troubleshoot problems as they arise. SQL Server offers multiple tools to support these tasks, and this module focuses on the one you will use most often: SQL Server Management Studio.

SSMS 21 and SSMS 22 provide full native support for SQL Server 2025 (internally versioned 17.x), and together they expose nearly every monitoring surface you will use on a day-to-day basis: real-time diagnostics, historical query performance, execution plan analysis, lightweight event tracing, and high-availability health dashboards. You do not need a separate license or a third-party agent installed on the server to get a meaningful picture of instance health; SSMS ships these capabilities out of the box, and they are kept current with each SSMS release. Because SSMS is now released on its own cadence, separate from the database engine itself, keeping SSMS updated matters just as much as keeping the instance patched: a newer SSMS build is often what unlocks visibility into a feature the engine has already shipped.

Activity Monitor is accessible from Object Explorer under Management, or by pressing Ctrl+Alt+A. It polls the instance through Dynamic Management Views (DMVs) to show live engine health across several expandable panes.

• Overview graphs: real-time Processor Time (%), Waiting Tasks, Database I/O (MB/sec), and Batch Requests/sec.
• Processes and Resource Waits: active session IDs (SPIDs), wait types, blocking chains, and login status. This pane is also where you can observe SQL Server 2025's newer Transaction ID (TID) locking and Lock-After-Qualification (LAQ) concurrency behavior under load.
• Data File I/O and Expensive Queries: physical read and write latency broken out per .mdf, .ndf, and .ldf file, alongside the queries currently consuming the most CPU, logical reads, and memory.

Activity Monitor is intentionally lightweight: it samples the same DMVs you could query directly, including sys.dm_exec_requests, sys.dm_exec_sessions, and sys.dm_os_waiting_tasks, and presents them in a refreshing graphical view rather than requiring you to write T-SQL every time you want a snapshot. For a quick manual check of blocking, for instance, a query such as this one covers much of the same ground as the Processes pane:

SELECT session_id, blocking_session_id, wait_type, wait_time, status
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

A similar query against sys.dm_os_wait_stats shows which wait types have consumed the most cumulative time since the last restart, a common starting point for diagnosing why an instance feels slow overall rather than chasing one specific session:

SELECT TOP 10 wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

Knowing both the GUI and the underlying DMVs matters: Activity Monitor is convenient for a quick look, but a script built on the DMVs is what you will eventually want for alerting or for building your own dashboards.

Query Store is expanded under each database node in Object Explorer, and it keeps a persistent history of execution plans and runtime statistics, data that survives instance restarts, unlike the plan cache alone. Query Store must be turned on per database; once enabled, SQL Server automatically begins capturing compile and runtime statistics for every query that executes against that database.

Right-clicking a database and opening Properties, Query Store gives you control over how aggressively it captures data: Capture Mode can be set to ALL (every query), AUTO (skips queries with negligible resource usage or execution count), or CUSTOM, and a Max Size setting controls how much space the data is allowed to consume before older statistics are aged out. For most OLTP databases, AUTO capture with a few gigabytes of allotted space is a reasonable starting point, large enough to retain several weeks of history without surprising you with disk growth.

In SQL Server 2025, Query Store reports in SSMS also let you track the engine's newer Intelligent Query Processing (IQP) behavior:

• Optional Parameter Plan Optimization (OPPO) and Cardinality Estimation (CE) feedback: multiple plan variants generated to counter parameter sniffing, plus CE adjustments for complex expressions.
• Regressed and high-variation queries: side-by-side comparison of historical plans, with the ability to manually force a stable plan when performance degrades.
• Secondary replica statistics: query performance across Always On readable secondaries; SQL Server 2025 preserves these statistics across restarts and failovers, so a replica that fails over no longer loses its own performance history.

The built-in Query Store reports, Top Resource Consuming Queries, Regressed Queries, and Overall Resource Consumption, are usually the fastest way to answer the question every DBA eventually hears: "why is the application slow today, when it was fine yesterday?" Because Query Store retains multiple plans per query, you can often answer that question and fix it, by forcing the earlier, better-performing plan, without waiting for a code deployment.

From the SSMS Query Editor, Ctrl+L generates an estimated execution plan, and Ctrl+M captures the actual plan once a query runs. Turning on Live Query Statistics streams real-time row counts, operator progress percentages, and elapsed time while the query is still executing, which is useful for spotting exactly where a long-running query is stalling rather than waiting for it to finish before you can tell.

In SSMS 22, execution plans and the accompanying tuning recommendations also cover SQL Server 2025-specific operations, including native VECTOR index seeks (DiskANN), JSON index scans, and calls to external AI models through sp_invoke_external_rest_endpoint. If your database has started adopting vector search or JSON-heavy workloads, these operators show up directly in the plan, with the same cost estimates and warnings you already rely on for traditional index seeks and scans.

Extended Events, commonly shortened to XEvents, is found under Management, Extended Events in Object Explorer. It is the lightweight, highly customizable tracing engine that replaced the deprecated SQL Server Profiler. SSMS surfaces two ways to work with it.

• XEvent Profiler: pre-configured Standard and TSQL sessions listed directly under Object Explorer, for immediate, low-overhead event streaming without building a session from scratch.
• Custom sessions: built to capture specific SQL Server 2025 events, such as deadlocks, adaptive memory grant feedback, Resource Governor workload-group throttling (now available in Standard Edition in SQL Server 2025), and REST endpoint latency.

Extended Events sessions are far cheaper to run than the old Profiler traces, which could noticeably slow down a busy instance. Because XEvents sessions can write to a file target asynchronously, you can leave a lightweight deadlock-capture session running continuously in production without the overhead that made many DBAs reluctant to run Profiler for more than a few minutes at a time. A session can also be configured to start automatically whenever the SQL Server service starts, so a long-running capture for something like deadlocks or long-running queries does not have to be manually restarted after every maintenance window or failover.

For environments running Always On, right-clicking an Availability Group opens a dashboard showing replica synchronization state and failover readiness, including SQL Server 2025 additions like configurable commit-wait times, measured in milliseconds, and asynchronous batched page requests for undo-of-redo.

Right-clicking a server or database node and choosing Reports, Standard Reports gives a quick visual breakdown of disk usage, transaction log space, blocking transactions, and Microsoft Fabric mirroring status. These canned reports are a good first stop before you reach for a custom query, since they cover the questions a DBA is asked most often: how full is the disk, how big is the log, and is anything currently blocked.

Finally, Management, SQL Server Logs, and the SQL Server Agent node track engine startup events, TLS 1.3 and Microsoft Entra ID authentication audits, failed logins, and the execution status of scheduled maintenance jobs. Checking these logs should be part of your daily routine, not something you only open after a user complains, since many problems announce themselves in the error log well before they become visible elsewhere.

Dashboards and reports are only useful if someone is looking at them at the right moment. For the rest of the time, SQL Server Agent Alerts close that gap by watching specific conditions and notifying you automatically. An alert can fire on a SQL Server error with a given severity, errors at severity 19 and above generally indicate a serious problem, on a specific error number, or on a performance condition such as a transaction log that crosses a configured percent-full threshold.

Before an alert can actually reach you, Database Mail has to be configured and an Operator defined in SSMS under SQL Server Agent, Operators, with a valid email address or pager schedule. Once that plumbing is in place, creating an alert is a short wizard: pick the condition, pick the operator to notify, and optionally attach a response job that runs automatically, for example a job that shrinks a log file or kicks off a trace, before a human even opens their inbox.

A small, well-chosen set of alerts, severity 19 to 25 errors and a log-full condition are a reasonable starting point, catches the overwhelming majority of problems a DBA actually needs to know about immediately, without flooding your inbox with noise from routine, self-correcting events.

SSMS is not the only native tool in the picture. When SQL Server runs on Windows, it registers a set of performance object counters, for example SQLServer:Buffer Manager, SQLServer:SQL Statistics, and SQLServer:Locks, that are visible to Windows's own Performance Monitor (PerfMon). These counters track engine-level metrics such as Page Life Expectancy, Batch Requests/sec, and Lock Waits/sec continuously, and PerfMon can log them to a file for days or weeks at a time, which is useful for spotting a slow trend, like gradually rising memory pressure, that a point-in-time look at Activity Monitor would never surface.

The two tools complement each other rather than compete. PerfMon is the better choice for long-running, low-overhead baseline capture across the whole server, including CPU, memory, and disk counters outside SQL Server's own scope, while SSMS's Activity Monitor and DMVs are the better choice once you already suspect a SQL Server-specific issue and need to see exactly which session, query, or wait type is responsible. A common troubleshooting pattern is to notice a PerfMon counter trending the wrong way over several days, then switch to SSMS to find the specific query or process behind it.

SSMS covers the great majority of day-to-day monitoring needs for a single instance or a small fleet, and everything described above is included at no extra licensing cost. Larger environments, or shops managing dozens of instances across SQL Server, Oracle, and cloud platforms from one pane of glass, often layer a dedicated monitoring product on top of SSMS rather than instead of it. Tools such as SolarWinds Database Performance Analyzer, Redgate SQL Monitor, and Idera SQL Diagnostic Manager add cross-instance dashboards, longer-term historical retention, and automated alerting that would otherwise need to be built by hand on top of the DMVs and Extended Events sessions covered in this module.

None of that makes SSMS obsolete. Even in shops that run a third-party platform, SSMS remains the tool you reach for to drill into a specific query's execution plan or to inspect a session that a dashboard alert just flagged. This course focuses on the native SSMS toolset because it is available in every SQL Server environment, and because understanding what SSMS shows you directly makes any third-party dashboard easier to interpret later.

None of these tools matter if they are only opened after something has already broken. A short daily routine, even five or ten minutes, catches most problems while they are still small: a quick look at Activity Monitor for anything blocked, a glance at the Query Store Regressed Queries report, a check of the SQL Server error log for anything new, and confirmation that your Agent jobs and alerts fired the way they were supposed to overnight. Building that habit, together with the alerts described above to cover the hours you are not looking, is what turns monitoring from a reactive chore into the proactive practice this module opened with.

By the time you complete this module, you should be able to:

1. Use SSMS Activity Monitor to observe real-time connections, resource waits, and blocking.
2. Read Query Store reports to identify regressed queries and force a stable execution plan.
3. Capture and interpret estimated and actual execution plans, including Live Query Statistics.
4. Set up and interpret Extended Events sessions, including the built-in XEvent Profiler.
5. Monitor Always On Availability Group health and Microsoft Fabric mirroring status.
6. Review SQL Server Logs and SQL Server Agent job history to audit authentication and scheduled jobs.

Older techniques such as DBCC commands and trace flags still exist in SQL Server 2025 and occasionally come up in legacy scripts, but Microsoft now steers most new monitoring work toward the DMV, Query Store, and Extended Events surfaces covered in this module, since they are more complete, lower overhead, and easier to automate. The lessons that follow build on these six areas, moving from live connection and lock monitoring toward the historical, event-driven, and high-availability tooling built into modern SSMS.