How To Monitor SQL Server Performance: Metrics, Tools And A Practical Setup

Most SQL Server performance problems announce themselves long before users complain. A query plan changes overnight, a disk starts answering reads in 40 milliseconds instead of 4, or a reporting job begins holding locks during business hours. The teams that catch these early are not using magic. They collect a small set of numbers on a schedule, know what normal looks like, and get alerted when something drifts.

This guide explains how to monitor SQL Server performance with the tools that ship in the box: dynamic management views (DMVs), Query Store, Extended Events and Performance Monitor. It covers which metrics to watch, how to build a baseline you can trust, how to wire up alerts, and the point at which a commercial monitoring product starts paying for itself.

Everything here applies to SQL Server 2019, 2022 and 2025 on Windows or Linux, and most of it carries over to Azure SQL Managed Instance. Where a feature behaves differently by version, we say so.

Start With Wait Statistics

If you only track one thing, track SQL Server wait stats. Every time a worker thread in SQL Server has to stop and wait for something (a page from disk, a lock, a log flush, CPU time), the engine records the wait type and duration. The cumulative totals live in sys.dm_os_wait_stats, and they tell you where the server spends its time instead of guessing from symptoms.

Two details matter when you read this view. First, the numbers are cumulative since the instance last started or since someone cleared them with DBCC SQLPERF (‘sys.dm_os_wait_stats’, CLEAR). A raw snapshot mixes last night’s maintenance with this morning’s workload, so the useful approach is to capture the view at intervals and compare deltas. Second, many wait types are benign background waits (sleep and queue waits used by system tasks). Filter them out before you rank anything, or they will dominate the list.

Wait Types Worth Knowing

• PAGEIOLATCH_SH and PAGEIOLATCH_EX: sessions waiting for data pages to be read from disk into memory. Persistent high values point to slow storage, too little memory, or queries scanning far more data than they need.
• WRITELOG: waiting for transaction log records to be hardened to disk. Usually a log volume latency problem or a workload committing in very small transactions.
• LCK_M_*: waiting to acquire a lock. This is blocking, and the suffix tells you the lock mode requested.
• CXPACKET and CXCONSUMER: parallelism waits. Some are normal on a server running parallel plans. A sudden jump often means a plan change or a poorly set cost threshold for parallelism.
• SOS_SCHEDULER_YIELD: a thread used its time slice and yielded. High values alongside high CPU suggest CPU pressure from expensive queries.
• RESOURCE_SEMAPHORE: queries waiting for a memory grant before they can start. A strong sign of memory pressure or oversized grants.

The ratio of signal_wait_time_ms to total wait_time_ms is also useful. Signal wait is the time between a resource becoming available and the thread actually getting CPU. When signal waits make up a large share of total waits, the server is short on CPU.

The Core Metrics To Track

Waits tell you what the server is waiting on. The metrics below tell you why, and they give you the context to decide whether a number is a problem or just a busy afternoon.

CPU

Track overall processor utilization and the share used by the SQL Server process. Sustained CPU above your normal band, combined with SOS_SCHEDULER_YIELD waits and high signal waits, points to CPU pressure. The next step is finding which queries consume it. Query Store and sys.dm_exec_query_stats both rank queries by total and average CPU time.

Memory and Page Life Expectancy

The buffer pool caches data pages so SQL Server does not have to read them from disk again. Page life expectancy (PLE), exposed in the SQL Server: Buffer Manager performance object, estimates how many seconds a page stays in the buffer pool. On servers with NUMA, the Buffer Node object reports it per node. A single fixed PLE threshold is a poor rule because the right value depends on how much memory the server has. Watch the trend instead: a PLE that repeatedly crashes and slowly recovers during the day is a sign that large scans are flushing the cache. Pair it with Memory Grants Pending (from the Memory Manager object), which should sit at or near zero.

Storage I/O Latency

sys.dm_io_virtual_file_stats returns read and write counts and cumulative stall times for every data and log file. Divide io_stall_read_ms by num_of_reads to get average read latency per file, and do the same for writes. Like wait stats, these values are cumulative since startup, so compare snapshots. Microsoft’s I/O troubleshooting guidance uses the Avg. Disk sec/Read and Avg. Disk sec/Write counters in Performance Monitor as the confirming signal. Log files in particular need low write latency because every commit waits on them.

Blocking

Short-lived blocking is normal in any system that writes data. Long blocking chains are not. sys.dm_exec_requests shows the blocking_session_id for each running request, which lets you trace a chain back to its head blocker. For historical capture, set the blocked process threshold server option (in seconds) and collect the blocked_process_report event with Extended Events. That gives you a record of every block that lasted longer than your threshold, including the statements involved.

Deadlocks

A deadlock happens when two sessions each hold a lock the other needs. SQL Server picks a victim and rolls it back, so the application sees an error rather than a hang. You do not need to set anything up to see them: the built-in system_health Extended Events session captures the xml_deadlock_report event by default. Count deadlocks per day and review the graphs when the count rises. Most are fixed with an index that shortens lock duration or by making two code paths access tables in the same order.

TempDB

TempDB serves temporary tables, table variables, sorts and hashes that spill out of memory, row versioning for snapshot isolation, and more. Monitor its free space with sys.dm_db_file_space_usage and track which sessions consume it with sys.dm_db_session_space_usage and sys.dm_db_task_space_usage. Watch for PAGELATCH waits on TempDB allocation pages, which indicate contention. Configuring multiple equally sized data files remains the standard mitigation, and SQL Server 2019 added memory-optimized TempDB metadata as an option for metadata contention.

Built-In Tools For Monitoring SQL Server

SQL Server ships with four monitoring tools that cover most needs. They overlap a little, but each has a clear job.

Dynamic Management Views

DMVs are system views that expose the engine’s internal state: waits, sessions, requests, cached plans, index usage, file I/O and much more. They are the raw material behind almost every monitoring product. Most hold either current state (what is running now) or cumulative counters since startup, which means you need to snapshot them into a table on a schedule to see trends. Viewing server-level DMVs requires the VIEW SERVER STATE permission (in SQL Server 2022 and later, VIEW SERVER PERFORMANCE STATE is the narrower option for many of them).

Query Store

Query Store records query text, execution plans and runtime statistics (duration, CPU, logical reads, waits) per database, and keeps them across restarts. It is the fastest way to answer “what changed?” when a query suddenly slows down, because you can see the old plan and the new plan side by side and force the better one. Query Store is enabled by default for new databases in SQL Server 2022 and later and in Azure SQL Database. On SQL Server 2016 through 2019, you turn it on per database with ALTER DATABASE. Review its size settings and capture mode so it does not fill up and flip to read-only.

Extended Events

Extended Events is the lightweight tracing framework that replaced SQL Trace and Profiler for most purposes. You define a session with the events you care about (long-running statements, blocked process reports, errors, deadlocks), optional filters, and a target such as an event file or ring buffer. The always-on system_health session already collects deadlocks, serious errors and long waits, so check it before building your own.

Performance Monitor

Windows Performance Monitor reads both operating system counters (processor, memory, physical disk) and SQL Server counters. A data collector set can log a chosen counter list to disk every 15 or 30 seconds, which makes it a cheap way to build a baseline. The same SQL Server counters are available from inside the engine through sys.dm_os_performance_counters, which is the practical route on Linux and in Managed Instance.

Tool	Best for	Keeps history?	Setup effort
DMVs	Current state, waits, I/O, sessions	No, unless you snapshot them	Low, but you build the collection
Query Store	Query regressions and plan changes	Yes, per database	Low, on by default in 2022 and later
Extended Events	Event capture: blocking, deadlocks, slow statements	Yes, in event files	Medium
Performance Monitor	OS and engine counters over time	Yes, in log files	Low

A Practical Monitoring Setup

The setup below works for a single instance or a small estate, and you can build it in an afternoon.

1. Create a DBA utility database on each instance to hold monitoring tables, separate from user databases.
2. Snapshot wait stats and file stats every 15 minutes with a SQL Server Agent job that inserts the output of sys.dm_os_wait_stats and sys.dm_io_virtual_file_stats with a timestamp. Calculate deltas at query time.
3. Log a short list of counters (processor time, page life expectancy, memory grants pending, batch requests per second, user connections) from sys.dm_os_performance_counters on the same schedule.
4. Confirm Query Store is on for every important user database, with a sensible maximum size and retention.
5. Add an Extended Events session for blocked process reports, and set the blocked process threshold to a value that matches your tolerance (many teams start between 10 and 30 seconds).
6. Purge old data on a schedule so the utility database does not grow forever. Thirty to ninety days of detail is enough for most baselines.

Setting A SQL Server Performance Baseline

A baseline is a record of what normal looks like for your workload, and without one every alert threshold is a guess. Collect at least two to four weeks of data so you cover weekly cycles, month-end processing and maintenance windows. Then summarize each metric by hour of day and day of week. A CPU reading of 70 percent might be routine at 9 a.m. on a Monday and alarming at 3 a.m. on a Sunday.

Record a few reference points for each metric: the typical range, the 95th percentile, and the worst value you saw during a period when users reported no problems. Re-baseline after any major change, such as a hardware move, a version upgrade, a compatibility level change or a large application release.

Configuring Alerts That People Act On

Alerts fail when they fire too often. Start with a short list tied to real impact, then add more only when an incident shows you missed something.

SQL Server Agent Alerts

SQL Server Agent can raise alerts on error severity levels, specific error numbers and performance condition thresholds, and send them through Database Mail. At minimum, create alerts for severity 17 through 25 and for errors 823, 824 and 825, which indicate I/O and page integrity problems. These are cheap to set up and catch failures that no performance dashboard will.

Threshold Alerts From Your Baseline

Use your baseline to set alerts on sustained deviation rather than single spikes. Good starting points include:

• Blocking that lasts longer than your blocked process threshold
• Any deadlock count above your normal daily figure
• Average read or write latency on a data or log file above its baseline for more than a few consecutive samples
• TempDB or transaction log free space below a set percentage
• Memory grants pending above zero for more than a few minutes
• Failed SQL Server Agent jobs, including backups

Route warnings to a channel the team reads during working hours and reserve paging for conditions that affect users now.

When To Buy A Monitoring Tool

The built-in tools are capable, but the setup above is a collection system you own and maintain. Commercial and open-source monitoring platforms package the same DMV and Extended Events data with history, dashboards, anomaly detection and alert routing. The decision usually comes down to scale and time.

Buying a tool makes sense when:

• You run more than a handful of instances and need one view across all of them.
• Nobody on the team has time to maintain collection jobs, purge routines and reports.
• You need longer history and correlation (for example, linking a CPU spike to the exact query and plan that caused it) without writing the queries yourself.
• On-call staff are not SQL Server specialists and need guided diagnostics.
• You mix SQL Server with other engines and want one monitoring platform for all of them.

When you evaluate products, check how much overhead the collector adds, whether it reads Query Store data, how it handles Availability Groups and Azure SQL, and how alert thresholds are set. Our roundup of the top SQL Server monitoring tools compares the main options, and if you run several database engines, see the top database performance monitoring tools.

Frequently Asked Questions

What is the most important metric to monitor in SQL Server?

Wait statistics. They show where the engine spends its time and point you toward the right subsystem (storage, CPU, memory or locking) before you dig into individual queries.

Is Query Store enabled by default?

Yes for new databases in SQL Server 2022 and later and in Azure SQL Database. Databases upgraded from older versions keep their existing setting, so check each one.

What is a good page life expectancy value?

There is no single good value because it depends on how much memory the server has. Focus on the trend for your own server and investigate sharp, repeated drops.

How often should I collect monitoring data?

Every 15 minutes is a reasonable default for wait and file stats snapshots. Collect counters more often (every 15 to 60 seconds) if you are diagnosing a specific problem.

Can I monitor SQL Server without third-party software?

Yes. DMVs, Query Store, Extended Events, Performance Monitor and SQL Server Agent alerts together cover the essentials. Third-party tools mainly save time and add history and cross-server views.

Conclusion

To monitor SQL Server performance well, start with wait statistics, add the supporting metrics for CPU, memory, I/O, blocking, deadlocks and TempDB, and capture them on a schedule so you can see trends. Use Query Store to catch plan regressions and Extended Events to record blocking and deadlocks. Build a baseline over a few weeks, set alerts on sustained deviation from it, and prune alerts nobody acts on. When the number of instances or the maintenance burden outgrows a homegrown setup, a dedicated monitoring tool is worth the spend, and the knowledge you built along the way will help you choose and configure it.