Production operations
Monitoring and wait statistics
Measure the bottleneck behind the symptom.
Understand the concept
Monitor CPU, IO, memory, waits and latency together. Wait statistics identify resources being waited on, not a root cause by themselves. Time-window deltas can be more meaningful than cumulative totals since startup. Depending on SQL Server version, server-state or server-performance-state permissions may be needed.
Do not blindly attack the highest wait: idle waits are normal, and parallelism waits can reflect other query costs. Interpret baselines, p95/p99 latency, error rates and workload changes together. DMVs may reset after restart; persistent monitoring needs sampling and retention. These examples do not reset counters, change settings or terminate sessions.
Prioritise production issues using evidence.
Declaring one wait type the definitive root cause.
One topic. Three levels of depth.
SELECT SYSDATETIMEOFFSET() AS ObservedAt, @@SPID AS SessionId;Records observation time and session.
This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.
Try it yourself first.
Inspect request information for your own session.
Show the solution and explanation
SELECT session_id, status, cpu_time, logical_reads FROM sys.dm_exec_requests WHERE session_id = @@SPID;The query itself is an active request, so this is a live snapshot.