Comprehensive Guide to Monitoring and Diagnosing Running Queries in SQL Server

Introduction

In database management practice, identifying and monitoring currently running queries is a critical aspect of performance tuning and troubleshooting. When database servers experience performance degradation or response delays, database administrators need to quickly locate executing queries, analyze their execution status, and take appropriate intervention measures. SQL Server provides multiple system views and dynamic management views to support these requirements.

Basic Monitoring Methods

For SQL Server 2000 and 2005 versions, the sysprocesses system view can be used to obtain information about currently running processes. The following query displays session processes with the longest execution times:

SELECT
    P.spid,
    RIGHT(CONVERT(VARCHAR, 
        DATEADD(ms, DATEDIFF(ms, P.last_batch, GETDATE()), '1900-01-01'), 
        121), 12) AS 'batch_duration',
    P.program_name,
    P.hostname,
    P.loginame
FROM master.dbo.sysprocesses P
WHERE P.spid > 50
    AND P.status NOT IN ('background', 'sleeping')
    AND P.cmd NOT IN ('AWAITING COMMAND', 'MIRROR HANDLER', 'LAZY WRITER', 'CHECKPOINT SLEEP', 'RA MANAGER')
ORDER BY batch_duration DESC

This query filters out system processes and idle sessions, focusing on user activity sessions. The batch_duration field accurately reflects query execution time by calculating the difference between the current time and the last batch processing time.

Query Text Retrieval Techniques

After identifying problematic sessions, it's necessary to retrieve the SQL text they are executing. The following code extracts query content for specific session IDs:

DECLARE
    @spid INT,
    @stmt_start INT,
    @stmt_end INT,
    @sql_handle BINARY(20)

SET @spid = 123 -- Replace with actual session ID

SELECT TOP 1
    @sql_handle = sql_handle,
    @stmt_start = CASE stmt_start WHEN 0 THEN 0 ELSE stmt_start / 2 END,
    @stmt_end = CASE stmt_end WHEN -1 THEN -1 ELSE stmt_end / 2 END
FROM sys.sysprocesses
WHERE spid = @spid
ORDER BY ecid

SELECT
    SUBSTRING(text,
        COALESCE(NULLIF(@stmt_start, 0), 1),
        CASE @stmt_end
            WHEN -1 THEN DATALENGTH(text)
            ELSE (@stmt_end - @stmt_start)
        END
    ) AS query_text
FROM ::fn_get_sql(@sql_handle)

This method accurately extracts executing SQL statements, providing the foundation for subsequent performance analysis and optimization.

Modern Monitoring Approaches

For SQL Server 2005 and later versions, dynamic management views provide more powerful monitoring capabilities. The following query combines multiple DMVs to obtain comprehensive execution information:

SELECT
    der.session_id,
    der.status,
    der.start_time,
    der.command,
    DB_NAME(der.database_id) AS database_name,
    des.login_name,
    der.blocking_session_id,
    der.wait_type,
    der.wait_time,
    der.cpu_time,
    der.total_elapsed_time,
    der.reads,
    der.writes,
    der.logical_reads,
    dest.text AS query_text
FROM sys.dm_exec_requests der
JOIN sys.dm_exec_sessions des ON der.session_id = des.session_id
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
WHERE der.status IN ('running', 'suspended')

This query provides rich execution context information, including key metrics such as blocking relationships, wait types, and resource consumption.

Recommended Practical Tools

In addition to custom queries, the widely used sp_whoisactive stored procedure offers an out-of-the-box monitoring solution. Developed by Adam Machanic, this tool integrates multiple monitoring functions and can quickly generate detailed execution reports.

Problem Session Handling

After identifying problematic queries, different handling strategies can be applied based on specific situations. For sessions that require immediate termination, the KILL command can be used:

KILL 123 -- Replace with actual session ID

However, before executing termination operations, it's recommended to analyze the query's execution plan and resource usage to determine the necessity of termination.

Monitoring Best Practices

Establishing continuous monitoring mechanisms is crucial for preventing performance issues. It's advisable to regularly run monitoring queries, establish baseline performance metrics, and set alert thresholds. Simultaneously, combine SQL Server's performance counters and extended events to build a comprehensive monitoring system.

Conclusion

Effective query monitoring forms the foundation of database performance management. By appropriately utilizing system views, dynamic management views, and professional tools, database administrators can quickly identify performance bottlenecks, optimize query execution, and ensure efficient operation of database systems. The methods introduced in this article cover the complete process from basic monitoring to advanced diagnostics, providing practical technical references for real-world work.