Have you ever wondered exactly what is happening inside your SQL Server at this very moment? Whether you’re troubleshooting a performance spike or just curious about the current workload, having a reliable monitoring query is essential.

Below is a comprehensive query I use to see everything that’s currently running. It provides deep insights into session details, wait types, memory usage, and blocking information.

The Monitoring Query

SELECT 
    CAST(r.context_info AS VARCHAR(128)) AS context_info, 
    r.session_id, 
    r.start_time, 
    r.STATUS, 
    r.command, 
    DB_NAME(s.database_id) AS db_name, 
    s.host_name, 
    s.login_name, 
    r.blocking_session_id, 
    r.wait_type, 
    r.wait_time, 
    r.scheduler_id, 
    r.cpu_time, 
    r.total_elapsed_time, 
    mg.requested_memory_kb, 
    mg.granted_memory_kb, 
    mg.used_memory_kb, 
    mg.max_used_memory_kb, 
    r.reads, 
    r.writes, 
    r.logical_reads, 
    tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count AS writes_in_tempdb,
    r.row_count, 
    r.task_address, 
    s.program_name, 
    s.client_interface_name, 
    r.query_hash, 
    r.query_plan_hash, 
    r.sql_handle, 
    r.statement_start_offset, 
    r.statement_end_offset, 
    r.plan_handle, 
    r.database_id, 
    qp.query_plan, 
    st.text, 
    OBJECT_NAME(st.objectid, st.dbid) AS object_name, 
    bst.text AS blocking_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
JOIN sys.dm_db_task_space_usage AS tsu
    ON s.session_id = tsu.session_id
    AND r.task_address = tsu.task_address
LEFT JOIN sys.dm_exec_query_memory_grants AS mg
    ON s.session_id = mg.session_id
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
LEFT JOIN sys.dm_exec_requests AS br
    ON r.blocking_session_id = br.session_id
OUTER APPLY sys.dm_exec_sql_text(br.sql_handle) AS bst
WHERE
    r.session_id != @@spid
    AND r.STATUS IN
    (
        'running', 
        'runnable', 
        'suspended', 
        'rollback', 
        'sleeping'
    )
ORDER BY 
    start_time;

Key Metrics Explained

This query pulls data from several Dynamic Management Views (DMVs) to give you a holistic view:

• session_id: The unique identifier for the connection.
• blocking_session_id: If a query is being blocked, this tells you exactly who is holding the lock.
• wait_type & wait_time: Crucial for identifying bottlenecks (e.g., disk I/O, memory grants, or CPU pressure).
• cpu_time & total_elapsed_time: Helps identify long-running or resource-intensive queries.
• query_plan: Allows you to click directly into the graphical execution plan in SSMS to see how SQL Server is processing the request.
• text: The actual SQL statement being executed.

Conclusion

By running this query, you can quickly identify “rogue” processes, understand resource distribution, and pinpoint the root cause of performance issues. It’s a must-have in any SQL Server DBA or developer’s toolkit.