SQLSPIDAnalyzer.sql - My way of monitoring a particular request in SQL Server
Since a long time I wanted to leverage the details from the DMV dm_tran_database_transactions into my regular SPID monitoring script. I just tried it today and looks to be useful. Any feedbacks/bugs/thoughts, let me know.
I'm combining sys.dm_os_workers, sys.dm_os_threads, sys.dm_exec_requests optionally sys.dm_exec_query_plan, sys.dm_tran_locks to give a complete picture about a session currently running in SQL Server. To get details about a particular column, refere Books Online.
I'm also attaching .sql file for easier download.
/* ------------------------------------------------------------------------------
SQLSPIDAnalyzer.sql - Script to analyze the status of a particular SPID
Author: SQLSakthi
Input: Session ID or SPID
----------------------------------------------------------------------------------
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND
---------------------------------------------------------------------------------*/
DECLARE @spid int
SELECT @spid = NULL --<<<< Specify the Session ID to be tracked here
IF @spid IS NULL SELECT 'Please specify a Session ID (SPID) to track' AS 'Error'
SELECT f.session_id spid
,[state]
,f.start_time [req_start_time], f.cpu_time, f.logical_reads,f.writes
,f.command cmd, DB_NAME(f.database_id) db
,f.blocking_session_id blkng_spid
,f.wait_type ,f.wait_time ,f.wait_resource ,a.[last_wait_type]
,suspended_ms =
CASE a.wait_started_ms_ticks
WHEN 0 THEN 0
ELSE c.ms_ticks - a.wait_started_ms_ticks
END
,runnable_ms =
CASE a.wait_resumed_ms_ticks
WHEN 0 THEN 0
ELSE c.ms_ticks - a.wait_resumed_ms_ticks
END
--,g.resource_type,g.resource_description,request_mode,request_type,request_status,request_lifetime -- For Lock info
,database_transaction_begin_time xsn_begin
,CASE database_transaction_type
WHEN 1 THEN 'Read/Write'
WHEN 2 THEN 'Read-only'
WHEN 3 THEN 'System' END AS xsn_type,
CASE database_transaction_state
WHEN 1 THEN 'The transaction has not been initialized'
WHEN 3 THEN 'The transaction has been initialized but has not generated any log records'
WHEN 4 THEN 'The transaction has generated log records'
WHEN 5 THEN 'The transaction has been prepared'
WHEN 10 THEN 'The transaction has been committed'
WHEN 11 THEN 'The transaction has been rolled back'
WHEN 12 THEN 'The transaction is being committed. In this state the log record is being generated, but it has not been materialized or persisted' END AS xsn_state
,database_transaction_log_record_count LogRecordCount
,database_transaction_replicate_record_count LogRecCnt_for_Repl
,database_transaction_log_bytes_used/1024.0 log_kb_used
,database_transaction_log_bytes_used_system sys_LogBytesUsed
,database_transaction_log_bytes_reserved/1024.0 log_kb_reserved
,database_transaction_log_bytes_reserved_system sys_LogBytesReserved
--,query_plan --Enable to get Query plan XML ** Need to uncomment CROSS APPLY too
,f.open_transaction_count, f.executing_managed_code, f.granted_query_memory, f.transaction_isolation_level
,a.[affinity], is_preemptive, is_polling_IOCP = is_in_polling_io_completion_routine
,pending_io_count, pending_io_byte_count, pending_io_byte_average, context_switch_count
,ReturnCode =
CASE a.return_code
WHEN 0 THEN 'SUCCESS'
WHEN 3 THEN 'DEADLOCK'
WHEN 4 THEN 'PREMATURE_WAKEUP'
WHEN 258 THEN 'TIMEOUT'
ELSE CAST(a.return_code as CHAR(5))
END
,exception_num [Last Exception], exception_severity [Last Severity], is_in_cc_exception, is_sick [spnlck_stuck]
,is_fatal_exception, is_inside_catch, tasks_processed_count [Task_Processed_by_Worker]
,d.os_thread_id, quantum_used, max_quantum, d.kernel_time [Kernel_time_ms], d.usermode_time [User_Mode_ms]
,d.stack_bytes_committed/1024.0 [Stack_commit_KB], d.stack_bytes_used/1024.0 [Stack_bytes_used_KB], d.is_waiting_on_loader_lock
,e.load_factor [Sched_Load_factor]
FROM sys.dm_os_workers a
CROSS JOIN sys.dm_os_sys_info AS c
INNER JOIN sys.dm_os_threads d ON d.thread_address = a.thread_address
INNER JOIN sys.dm_os_schedulers e ON e.scheduler_address = a.scheduler_address
INNER JOIN sys.dm_exec_requests f ON a.task_address = f.task_address
INNER JOIN sys.dm_tran_session_transactions dtt ON dtt.session_id = f.session_id
INNER JOIN sys.dm_tran_database_transactions tdt ON dtt.transaction_id = tdt.transaction_id
--INNER JOIN sys.dm_tran_locks g ON g.request_session_id = f.session_id
--CROSS APPLY sys.dm_exec_query_plan(f.plan_handle)
WHERE a.worker_address = (SELECT worker_address FROM sys.dm_os_tasks WHERE session_id=@spid)
Warning: These dynamic management views (DMVs) are powerful but they require synchronized access to the internal data structures and lists. Frequent query activity against these DMVs can impact overall system performance and some queries can return large result sets. Access to the information is thread-safe but it is recommended that you query these system DMVs only when necessary.
Sakthivel Chidambaram, SQL Server Support