SQL Server is a powerful relational database management system used by organizations to store and manage their data. As an administrator or developer, it’s crucial to have visibility into the active sessions and their execution details for effective monitoring and troubleshooting. In this blog post, we will explore a T-SQL query that allows us to monitor and analyze SQL Server sessions, providing valuable insights into the ongoing activities within the database.
The T-SQL query provided in this blog post leverages system dynamic management views (DMVs) to gather information about active sessions, their execution details, resource usage, and more. Let’s delve into each section of the query to understand its purpose:
- Gathering Session Information:
The query starts by joining the
sys.dm_exec_sessionsDMVs to retrieve information about active sessions. It selects relevant columns such as session ID, original login name, status, blocking session ID, command, and database name.
- Calculating Duration and Completion Percentage:
To provide insights into the duration and progress of each session, the query utilizes the
sys.dm_exec_requestsand calculates the elapsed time since the session started. It also calculates the percentage of completion for long-running tasks.
- Extracting Executing SQL Statement:
By using the
sys.dm_exec_sql_textDMV and applying the
SUBSTRINGfunction, the query extracts the executing SQL statement for each session. This allows administrators and developers to identify the specific queries being executed.
- Monitoring Wait Statistics:
To monitor potential resource contention, the query captures information related to wait resources, wait time, and the last wait type for each session. This data helps identify sessions that might be waiting for resources and causing delays.
- Capturing Additional Session Details:
The query includes various other details such as login time, last request start and end times, host name, program name (including SQL Agent job information), open transaction count, transaction isolation level, and I/O statistics (reads, writes, and logical reads).
- Displaying Total Rows:
To provide insights into the number of rows affected by a session’s execution, the query includes a column for row count. However, as “RowCount” is a reserved keyword, it is renamed as “TotalRows” in the query.
SELECT ss.session_id AS SessionID, -- Session ID original_login_name AS OriginalLoginName, -- Original login name sp.status AS Status, -- Session status blocking_session_id AS BlockedBySessionID, -- Blocking session ID command AS Command, -- Command being executed DB_NAME(sp.database_id) AS DatabaseName, -- Database name [objectid] AS ObjectID, -- Object ID sp.cpu_time AS CPUTime, -- CPU time consumed percent_complete AS PercentageComplete, -- Percentage of completion CASE WHEN DATEDIFF(mi, start_time, GETDATE()) > 60 THEN CONVERT(VARCHAR(4), (DATEDIFF(mi, start_time, GETDATE()) / 60)) + ' hr ' -- Calculating duration in hours if more than 60 minutes ELSE '' END + CASE WHEN DATEDIFF(ss, start_time, GETDATE()) > 60 THEN CONVERT(VARCHAR(4), (DATEDIFF(mi, start_time, GETDATE()) % 60)) + ' min ' -- Calculating duration in minutes if more than 60 seconds ELSE '' END + CONVERT(VARCHAR(4), (DATEDIFF(ss, start_time, GETDATE()) % 60)) + ' sec' AS Duration, -- Duration of the session estimated_completion_time / 60000 AS EstimatedCompletionTimeMin, -- Estimated completion time in minutes [text] AS InputStreamText, -- Input stream or text (SUBSTRING( [text], statement_start_offset / 2 + 1, ( (CASE WHEN statement_end_offset < 0 THEN (LEN(CONVERT(nvarchar(max), [text])) * 2) ELSE statement_end_offset END) - statement_start_offset ) / 2 + 1 )) AS ExecutingSQLStatement, -- Executing SQL statement wait_resource AS WaitResource, -- Resource being waited upon wait_time / 1000 AS WaitTimeSec, -- Wait time in seconds last_wait_type AS LastWaitType, -- Last wait type login_time AS LoginTime, -- Login time last_request_start_time AS LastRequestStartTime, -- Start time of the last request last_request_end_time AS LastRequestEndTime, -- End time of the last request host_name AS HostName, -- Host name CASE WHEN program_name LIKE 'SQLAgent%Job%' THEN ( SELECT TOP 1 '(SQLAgent Job - ' + name + ' - ' + RIGHT(program_name, LEN(program_name) - CHARINDEX(':', program_name)) FROM msdb.dbo.sysjobs SJ WHERE UPPER(master.dbo.fn_varbintohexstr(SJ.job_id)) = UPPER(SUBSTRING([program_name], 30, 34)) ) ELSE program_name END AS ProgramName, -- Program name, including SQL Agent job information sp.open_transaction_count AS OpenTransactionCount, -- Open transaction count CASE sp.transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncommitted' WHEN 2 THEN 'ReadCommitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END AS TransactionIsolationLevel, -- Transaction isolation level sp.reads AS Reads, -- Number of reads sp.writes AS Writes, -- Number of writes sp.logical_reads AS LogicalReads, -- Number of logical reads sp.lock_timeout AS LockTimeout, -- Lock timeout sp.row_count AS TotalRows -- Total number of rows affected FROM sys.dm_exec_requests AS sp OUTER APPLY sys.dm_exec_sql_text(sp.sql_handle) AS esql RIGHT OUTER JOIN sys.dm_exec_sessions ss ON ss.session_id = sp.session_id WHERE ss.status <> 'sleeping'
By utilizing the provided T-SQL query, administrators and developers gain a comprehensive understanding of the active sessions within a SQL Server instance. They can monitor the progress of ongoing tasks, identify blocking sessions, analyze resource usage, and gain insights into executing SQL statements. This information enables efficient troubleshooting, performance optimization, and better overall management of SQL Server environments.
Note: It’s essential to exercise caution when executing queries against production environments and ensure appropriate permissions are granted to access the required DMVs.
Remember, monitoring and analyzing SQL Server sessions is a crucial aspect of maintaining a healthy and performant database environment. By leveraging the power of T-SQL and system DMVs, administrators can effectively manage and optimize their SQL Server instances, ensuring optimal performance and user experience.
Stay tuned for more tips and insights on SQL Server administration and development!
We hope you find this blog post informative and useful in your SQL Server journey. Happy querying!
Leave a Reply