MS SQL Server Monitoring: DMV, Extended Events, and Query Store

You launch a report at 14:32, and it hangs for 3 minutes. The query plan changed, statistics are outdated—no error log or standard counters show the cause. Without properly configured monitoring, you remain in the dark. Each hour of application downtime can cost tens of thousands of dollars. With our approach—specific query, plan, lock. In this article: the tool stack and setup methodology.

What Problems Do We Solve?

Blocks, deadlocks, query degradation, CPU or I/O shortages, suboptimal plans—all of these are diagnosed using DMVs, Extended Events, and Query Store. Proactive monitoring allows identifying issues before they impact users. Real cases: one client after setup reduced average response time by 40%, another cut deadlock counts from 50 to 2 per day.

How We Do It

What Are DMVs and How Do They Help?

Dynamic Management Views are built-in views for real-time diagnostics. They require no agent installation. We use them constantly to find heavy queries and bottlenecks. For setup, we use SSMS or scripts.

-- Current active queries with execution time and wait types
SELECT r.session_id, r.status, r.wait_type, r.wait_time / 1000.0 AS wait_seconds, r.cpu_time / 1000.0 AS cpu_seconds, r.total_elapsed_time / 1000.0 AS elapsed_seconds, r.logical_reads, DB_NAME(r.database_id) AS database_name, 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 query_text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t WHERE r.session_id > 50 ORDER BY r.total_elapsed_time DESC;

-- Top 10 most expensive queries by CPU over all time
SELECT TOP 10 qs.total_worker_time / qs.execution_count AS avg_cpu_us, qs.total_elapsed_time / qs.execution_count AS avg_elapsed_us, qs.execution_count, qs.total_logical_reads / qs.execution_count AS avg_logical_reads, SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS query_text FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt ORDER BY avg_cpu_us DESC;

-- Cumulative wait statistics since SQL Server startup
SELECT TOP 15 wait_type, waiting_tasks_count, wait_time_ms / 1000.0 AS wait_time_sec, max_wait_time_ms / 1000.0 AS max_wait_sec, signal_wait_time_ms / 1000.0 AS signal_wait_sec, CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER() AS DECIMAL(5,2)) AS pct FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ( 'SLEEP_TASK','BROKER_TO_FLUSH','BROKER_TASK_STOP','CLR_AUTO_EVENT', 'DISPATCHER_QUEUE_SEMAPHORE','FT_IFTS_SCHEDULER_IDLE_WAIT', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION','HADR_WORK_QUEUE', 'LAZYWRITER_SLEEP','LOGMGR_QUEUE','ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_MONITOR','RESOURCE_QUEUE','SERVER_IDLE_CHECK', 'SLEEP_DBSTARTUP','SLEEP_DCOMSTARTUP','SLEEP_MASTERDBREADY', 'SLEEP_MASTERMDREADY','SLEEP_MASTERUPGRADED','SLEEP_MSDBSTARTUP', 'SLEEP_SYSTEMTASK','SLEEP_TEMPDBSTARTUP','SNI_HTTP_ACCEPT', 'SP_SERVER_DIAGNOSTICS_SLEEP','SQLTRACE_BUFFER_FLUSH', 'WAITFOR','XE_DISPATCHER_WAIT','XE_TIMER_EVENT' ) ORDER BY wait_time_ms DESC;

Wait statistics — the first diagnostic tool. PAGEIOLATCH_SH/EX — SQL Server waits for disk page reads, I/O bottleneck. LCK_M_* — locks, check sys.dm_exec_requests for blocking_session_id. CXPACKET — parallel queries, can be normal or a sign of poor plans. The table below shows typical wait types and their interpretation:

Why Extended Events Are Better Than Profiler

SQL Profiler is outdated: it causes heavy locking and resource consumption. Extended Events is the modern replacement with overhead under 2%. Ninety percent of Profiler's functionality at 2% overhead. We configure a session to capture slow queries (longer than 5 seconds) to a file with 100 MB rotation:

CREATE EVENT SESSION [SlowQueries] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed ( 
    ACTION ( sqlserver.sql_text, sqlserver.database_name, sqlserver.username, sqlserver.client_app_name, sqlserver.plan_handle ) 
    WHERE ( [duration] > 5000000 AND [sqlserver].[database_name] <> N'master' ) 
), 
ADD EVENT sqlserver.rpc_completed ( 
    ACTION (sqlserver.sql_text, sqlserver.database_name) 
    WHERE [duration] > 5000000 
) 
ADD TARGET package0.ring_buffer (SET max_memory = 51200), 
ADD TARGET package0.asynchronous_file_target ( 
    SET filename = N'D:\SQLTrace\SlowQueries.xel', max_file_size = 100, max_rollover_files = 5 
) 
WITH ( MAX_DISPATCH_LATENCY = 5 SECONDS, STARTUP_STATE = ON ); 
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START;

For deadlocks, we use the built-in system_health—it already captures them automatically. We extract graphs via sys.dm_xe_session_targets.

How to Configure Query Store for Plan Control

Query Store is a built-in repository of plan history and metrics. It is enabled at the database level. Setup took 15 minutes for one client, resulting in a 40% reduction in average response time. Recommended parameters:

ALTER DATABASE AppDB SET QUERY_STORE = ON ( OPERATION_MODE = READ_WRITE, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30), DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_STORAGE_SIZE_MB = 1000, QUERY_CAPTURE_MODE = AUTO, SIZE_BASED_CLEANUP_MODE = AUTO, MAX_PLANS_PER_QUERY = 200 );

-- Find queries whose plan regressed (e.g., in the last 24 hours)
SELECT TOP 20 q.query_id, qt.query_sql_text, rs.avg_duration / 1000.0 AS avg_ms, rs.execution_count, rs.avg_logical_io_reads, p.plan_id
FROM sys.query_store_query q
JOIN sys.query_store_query_text qt ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON q.query_id = p.query_id
JOIN sys.query_store_runtime_stats rs ON p.plan_id = rs.plan_id
JOIN sys.query_store_runtime_stats_interval i ON rs.runtime_stats_interval_id = i.runtime_stats_interval_id
WHERE i.start_time > DATEADD(HOUR, -24, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

-- Force a good plan
EXEC sys.sp_query_store_force_plan @query_id = 42, @plan_id = 7;

Comparison of Monitoring Methods

Integration with External Systems

We export DMV data to Prometheus via sql_exporter or Telegraf. In Grafana, we build dashboards for wait stats, QDS, and activity. Alerts on critical situations: CPU > 90%, LCK_M_X wait > 30 seconds, log fill > 80%.

Engagement Process

1. Analytics — understand architecture, identify pain points.
2. Design — select Extended Events sessions, DMV scripts, Query Store parameters.
3. Implementation — deploy scripts, configure alerts via SQL Server Agent.
4. Integration — connect to Grafana/Prometheus (optional).
5. Testing — verify alert accuracy, measure overhead.
6. Documentation — deliver runbook and dashboards.

What's Included in the Deliverable

• DMV scripts for daily diagnostics.
• Ready-to-use Extended Events session for slow queries and deadlocks.
• Enabled and configured Query Store with retention policies.
• Alerts on critical wait types and log filling.
• Grafana dashboard (if integration selected).
• Team training: how to read wait stats and analyze Query Store.

Timeline and Cost

A typical project takes 2 to 4 business days depending on infrastructure complexity (clustering, Always On, number of databases). Cost is calculated individually after an audit of your current state. Savings from reduced downtime and query optimization can be substantial.

Our engineers hold Microsoft certifications and have over 10 years of experience in SQL Server administration. We have implemented monitoring for dozens of projects, including systems with thousands of transactions per second. Guaranteed overhead does not exceed 5%.

Contact us to discuss your project. Order a monitoring audit today.