-- Just a few example of simple queries that can be used to view sessions, blocked sessions etc.. -- Usable for SQL 2005 / 2008 -- By Albert - v. 0.2 -- ----------------------- -- 1. Some nice queries: -- ----------------------- -- 1.1. show all active sessions, including blocked ones, as well as some other characteristics. actually, this one works on 2000/2005/2008. SELECT spid, cpu, physical_io, blocked, cmd, waittime, substring(convert(varchar(20),last_batch), 1, 20) as "LASTBATCH", substring(nt_username, 1, 15) AS "USERNAME", substring(loginame, 1, 20) AS "LOGINNAME", substring(hostname, 1, 15) AS "HOSTNAME", substring(program_name, 1, 40) AS "PROGRAM" FROM master.dbo.sysprocesses WHERE cmd NOT LIKE 'AWAIT%' -- Also usable for 2005/2008: SELECT s.login_name, s.nt_user_name, r.session_id AS SESSION_ID, s.program_name, s.host_name, db_name(r.database_id) AS [DATABASE], r.command from sys.dm_exec_sessions s, sys.dm_exec_requests r where s.session_id=r.session_id -- 1.2. show all (longer) blocked sessions, as well as the the blocker, and some other characteristics SELECT s.login_name, s.nt_user_name, r.session_id AS BLOCKED_SESSION_ID, r.blocking_session_id AS BLOCKING_SESSION_ID, s.program_name, r.start_time,r.status,r.command,database_id, r.wait_type,r.open_transaction_count,r.percent_complete,r.cpu_time,r.reads,r.writes,r.deadlock_priority from sys.dm_exec_sessions s, sys.dm_exec_requests r where s.session_id=r.session_id AND blocking_session_id > 0 -- 1.3. show the SQL text of the Blocked and Blocking sessions SELECT a.session_id AS blocked_session_id, b.session_id AS blocking_session_id, c.text AS blocking_text, e.wait_type AS blocking_resource, e.wait_duration_ms, d.text AS blocked_text FROM sys.dm_exec_requests a INNER JOIN sys.dm_exec_requests b ON a.blocking_session_id = b.session_id CROSS APPLY sys.dm_exec_sql_text(b.sql_handle) c CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) d INNER JOIN sys.dm_os_waiting_tasks e ON e.session_id = b.session_id -- ------------------------------------------------ -- 2. Some other stuff (maybe you like one or two): -- ------------------------------------------------ SELECT transaction_id, SUBSTRING(DB_NAME(database_id),1,15) AS DATABASE_NAME, database_transaction_begin_time, database_transaction_type, database_transaction_status, database_transaction_log_record_count FROM sys.dm_tran_database_transactions WHERE database_transaction_log_record_count > 0 SELECT d.session_id AS SPID, d.user_id AS DB_USERID, d.blocking_session_id AS DB_BLOCKING_SPID, substring(t.resource_type,1,15) AS RESOURCE_TYPE, t.resource_database_id AS DATABASE_ID, substring(t.resource_description,1,15) AS RESOURCE_DESC, substring(t.request_mode,1,15) AS REQUEST_MODE, substring(t.request_type,1,15) AS REQUEST_TYPE, substring(t.request_status,1,15) AS REQUEST_STATUS, t.request_session_id AS REQUESTING_SPID FROM sys.dm_exec_requests d, sys.dm_tran_locks t WHERE d.session_id=t.request_session_id select substring(resource_type,1,15) AS RESOURCE_TYPE, resource_database_id, substring(resource_description,1,15) AS RESOURCE_DESC, request_mode, substring(request_type,1,15) AS REQUEST_TYPE, request_status, request_session_id from sys.dm_tran_locks SELECT t1.resource_type AS [lock type],DB_NAME(resource_database_id) AS [database], t1.resource_associated_entity_id AS [blk object],t1.request_mode AS [lock req], t1.request_session_id AS [waiter sid], t2.wait_duration_ms AS [wait time], (SELECT [text] FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) WHERE r.session_id = t1.request_session_id) AS [waiter_batch], (SELECT SUBSTRING(qt.[text],r.statement_start_offset/2, (CASE WHEN r.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max), qt.[text])) * 2 ELSE r.statement_end_offset END - r.statement_start_offset)/2) FROM sys.dm_exec_requests AS r CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS qt WHERE r.session_id = t1.request_session_id) AS [waiter_stmt], t2.blocking_session_id AS [blocker sid], (SELECT [text] FROM sys.sysprocesses AS p CROSS APPLY sys.dm_exec_sql_text(p.[sql_handle]) WHERE p.spid = t2.blocking_session_id) AS [blocker_stmt] FROM sys.dm_tran_locks AS t1 INNER JOIN sys.dm_os_waiting_tasks AS t2 ON t1.lock_owner_address = t2.resource_address SELECT session_id, substring(convert(varchar(20),login_time),1,17) AS LOGINTIME, substring(host_name,1,10) AS HOSTNAME, substring(program_name,1,30) AS PROGRAM, substring(login_name,1,10) AS LOGINNAME, substring(nt_user_name,1,10) AS NT_USER, substring(status,1,10) AS STATUS, lock_timeout, row_count FROM sys.dm_exec_sessions select t1.resource_type, 'database'=db_name(resource_database_id), 'blk object' = t1.resource_associated_entity_id, t1.request_mode, t1.request_session_id, t2.blocking_session_id from sys.dm_tran_locks as t1, sys.dm_os_waiting_tasks as t2 where t1.lock_owner_address = t2.resource_address -- just a standard stored procedure to view locks exec sp_lock -- view oldest transactions in the database DBCC OPENTRAN('databasename') -- like e.g. DBCC OPENTRAN('sales') -- try to get the latest SQL that a session (spid) send to the database engine. DBCC INPUTBUFFER(spid) -- like e.g. dbcc inputbuffer(92)