Recently we had a scenario where we had a handful of queries being blocked. Nothing unusual there but when I looked into sys.dm_exec_requests I could see all of the blocked requests, but could not find a request with a session_id matching the blocking_session_id. The session showed up in sys.dm_exec_sessions but it was “sleeping” and hadn’t performed a request in hours. So what was going on?

Well unfortunately sys.dm_exec_requests only pulls currently executing requests. A session that has an open transaction but isn’t actively doing anything isn’t considered an “executing” request and won’t show up in sys.dm_exec_requests. In order to get a list of sessions with an open transaction you can run a query off sys.dm_tran_session_transactions or if you are running SQL 2012 or higher sys.dm_exec_sessions has a new column open_transaction_count. However we need do something about our blocking transaction and as I see it we have a couple of options.

Easiest is to find and talk to the user. Sys.dm_exec_sessions has the login_name, nt_domain, nt_user_name to help you identify the user, but if they are using a generic SQL login then that won’t help much. Next we can look at host_name to find the users machine and program_name to tell what program they are connecting from. (Frequently it helps to tell the user what program is the problem if they are using Excel, Access, and SSMS to connect to the instance.) And if you want to you can look at sys.dm_exec_connections and get the IP(client_net_address) of the connecting machine.

But let’s say that it’s the middle of the night and now we need to decide if we are going to kill the process, wake up the user (if we have their number), or just let it run till morning. In order to make that decision it would help to know what exactly they are doing. If we had a row in sys.dm_exec_requests we could use sys.dm_exec_sql_text to get the actual query they are running. But as we said before there is no row in sys.dm_exec_requests. So now we again have a couple of options. Simplest is to look at sys.dm_exec_connections and use the most_recent_sql_handle column with sys.dm_exec_sql_text to get the last query run by the connection. Unfortunately it does not give us all of the SQL statements within the transaction. It only returns the set of statements in the last batch executed within the transaction. I’ll post a proof soon. So that may not give us enough information. If so we can go the somewhat more complicated route and take a look at the locks held by the session.

We can tie sys.dm_exec_sessions to sys.dm_tran_session_transactions to get a list of the transactions tied to the session.

SELECT *
FROM sys.dm_exec_sessions sessions
JOIN sys.dm_tran_session_transactions trans
ON sessions.session_id = trans.session_id

On the other hand sys.dm_tran_locks has session_id also and we can get a lot of additional information that will be very helpful.

SELECT request_session_id AS session_id,
request_owner_id AS transaction_id,
DB_NAME(resource_database_id) AS DatabaseName,
OBJECT_SCHEMA_NAME(resource_associated_entity_id,
resource_database_id) AS SchemaName,
OBJECT_NAME(resource_associated_entity_id,
resource_database_id) AS ObjectName,
request_mode, request_type, request_status,
COUNT_BIG(1) AS lock_count
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT'
GROUP BY request_session_id, request_owner_id,
resource_database_id, resource_associated_entity_id,
request_mode, request_type, request_status

I did run into a bit of a problem here. Sometimes (and I’m not sure when or why) I got blocked when trying to use OBJECT_NAME and OBJECT_SCHEMA_NAME in this query. You could join to sys.objects and sys.schemas instead but only one database at a time. If you want to use this query as it stands you can try running it, and if it gets blocked kill it. Then query for just the resource_database_id to figure out which database you need, then go there and join to sys.objects and sys.schemas. Cumbersome but I don’t know a better way I’m afraid.

Now I’m only looking at object locks in this query but there are a number of other types that you may want to look at (DATABASE, FILE, PAGE etc). In general I’ve found the OBJECT locks to be the most useful though. By combining object_name, request_mode, request_type and request_status we can get a fairly good idea of what the individual is doing. If they are only doing a series of selects there is no problem you should be fairly safe in killing the connection. If they are doing updates, inserts, deletes etc you can take the # of locks, your knowledge of the tables and their use and make an informed decision.

Transactions are a big subject which I’m going to explore over several posts. I am by no means going to cover the subject exhaustively but if you have any subjects you would like me to cover or think I’ve missed something feel free to comment or email me.

• Transactions: Rolling back a nested transaction.
• Transactions: Rolling back part of a transaction.
• Transactions: Rolling back a transaction inside a stored procedure.
• Transactions: What are they?
• Transactions: Creating a single restore point across multiple databases.
• Transactions: What commands aren’t allowed.

Filed under: Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication, System Functions and Stored Procedures, T-SQL, Transactions Tagged: code language, language sql, microsoft sql server, T-SQL, transactions