VA Active Process Auditor
HEALTHY (< 30s): Standard execution. Plan stability is maintaining throughput.
WARNING (31-60s): Inefficiency detected. Potential 'heavy' joins or unoptimized plans.
CRITICAL (> 60s): Severe bottleneck. High block risk; identify 'Long Runners' for intervention.
Surgical Action Plan: Live Workload Optimization
- Complexity Audit: Stored Procs with high joins and red elapsed time likely need Statistics updates or Index tuning.
- Immediate Fix: If a specific SPID is causing server-wide pressure, coordinate with the user or use
KILL [Spid];[cite: 699, 2210]. - Performance Pivot: Check Query Performance to find the historical cost of recurring ad-hoc statements[cite: 345, 1864].
Total Active
2
Warning Running
0
Long Runners
1
| Spid | User / Workstation | Type | Started | Elapsed | Joins | Sql Text |
|---|---|---|---|---|---|---|
| 53 | DESKTOP-LQEABPI\penny DESKTOP-LQEABPI | Ad-Hoc SQL | 05/14/2026 09:04:08 | 17069s | 0 | -- 1. Ensure a database has Read Committed Snapshot enabled (Safe for local TEST)
ALTER DATABASE AWL SET READ_COMMITTED_SNAPSHOT ON;
|
| 55 | DESKTOP-LQEABPI\penny DESKTOP-LQEABPI | Stored Proc | 05/14/2026 13:48:39 | 0s | 3 | SELECT r.session_id as [Spid], s.login_name as [User], s.host_name as [Workstation], r.start_time as [StartTime],
CASE WHEN st.text LIKE '%CREATE PROCEDURE%' OR st.text LIKE '%EXEC %' THEN 'Stored Proc' ELSE 'Ad-Hoc SQL' END as [ObjectType],
r.total_elapsed_time / 1000 as [ElapsedSec], (LEN(st.text) - LEN(REPLACE(UPPER(st.text), 'JOIN', ''))) / 4 as [Joins], st.text as [SqlText]
FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id WHERE r.session_id > 50; |
Reference: Raw Truth Verification Query
SELECT r.session_id, r.status, r.start_time, r.total_elapsed_time, st.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st WHERE r.session_id > 50;