Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc...
With the introduction of DMV's in SQL Server 2005 getting this information is quite easy with this query:
Let's look at it one DMV at a time from top to bottom:
sys.dm_tran_locks:
Shows us all locks held on all resources for all transaction. We start from this view since we want to get information about locked resources.
sys.partitions:
Contains information about partitions in the database. We can join it to the sys.dm_tran_locks on the hobt_id only for resource_type values 'HOBT', 'Page', 'RID' and 'Key'. With this join we get the object_id of our locked table.
sys.objects:
Contains data for all schema scoped database objects. We join it to sys.partitions to get the Name of the locked object which is usually a table name.
sys.dm_exec_sessions:
Shows data about all active sessions on SQL server. We join it to sys.dm_tran_locks to get user login information for each held lock.
sys.dm_tran_session_transactions:
Shows data for all transactions for a session. By joining in to sys.dm_exec_sessions we get a link to all active transactions in the session.
sys.dm_tran_active_transactions:
Shows data for all active transactions in SQL Server. By joining it to sys.dm_tran_session_transactions we only get information for the session we want.
sys.dm_exec_connections:
Shows data for all connections in SQL Server. By joining it to sys.dm_exec_sessions we get connection info for our session.
sys.dm_exec_sql_text:
Returns SQL statement that is associated with the SQL handle input parameter. By cross applying it to the sys.dm_exec_connections we get the last executed statement for the connection, which in our case is the statement that is holding locks.
By applying the filter in the where clause you get the answers to questions like:
- What SQL Statement is causing the lock?
- Which user has executed the SQL statement that's holding the locks?
- What objects/tables are being locked?
- What kinds of locks are being held and on which pages, keys, RID's?
- etc...
With the introduction of DMV's in SQL Server 2005 getting this information is quite easy with this query:
SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P.object_id AS LockedObjectId, L.resource_type AS LockedResource, L.request_mode AS LockType, ST.text AS SqlStatementText, ES.login_name AS LoginName, ES.host_name AS HostName, TST.is_user_transaction as IsUserTransaction, AT.name as TransactionName, CN.auth_scheme as AuthenticationMethod FROM sys.dm_tran_locks L JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id JOIN sys.objects O ON O.object_id = P.object_id JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST WHERE resource_database_id = db_id() ORDER BY L.request_session_id
Let's look at it one DMV at a time from top to bottom:
sys.dm_tran_locks:
Shows us all locks held on all resources for all transaction. We start from this view since we want to get information about locked resources.
sys.partitions:
Contains information about partitions in the database. We can join it to the sys.dm_tran_locks on the hobt_id only for resource_type values 'HOBT', 'Page', 'RID' and 'Key'. With this join we get the object_id of our locked table.
sys.objects:
Contains data for all schema scoped database objects. We join it to sys.partitions to get the Name of the locked object which is usually a table name.
sys.dm_exec_sessions:
Shows data about all active sessions on SQL server. We join it to sys.dm_tran_locks to get user login information for each held lock.
sys.dm_tran_session_transactions:
Shows data for all transactions for a session. By joining in to sys.dm_exec_sessions we get a link to all active transactions in the session.
sys.dm_tran_active_transactions:
Shows data for all active transactions in SQL Server. By joining it to sys.dm_tran_session_transactions we only get information for the session we want.
sys.dm_exec_connections:
Shows data for all connections in SQL Server. By joining it to sys.dm_exec_sessions we get connection info for our session.
sys.dm_exec_sql_text:
Returns SQL statement that is associated with the SQL handle input parameter. By cross applying it to the sys.dm_exec_connections we get the last executed statement for the connection, which in our case is the statement that is holding locks.
By applying the filter in the where clause you get the answers to questions like:
- What SQL Statement is causing the lock?
- Which user has executed the SQL statement that's holding the locks?
- What objects/tables are being locked?
- What kinds of locks are being held and on which pages, keys, RID's?
- etc...
Posts
4 comments:
If above mentioned SQL does not returns anything what does it means ?
On the other hand we can see lock on the objects of any particular SQL Server database
I am Testing in Sql Server2005 ,It Will Work Fine Once Again Check It
Created a variant which can be used to collect more information and store it in a (tempdb) table.
This sql collects basic information about which queries are waiting and for what process.
Maybe it is useful for someone else...
-- use tempdb because we do not want the lockTable to be in any dev database
USE tempdb;
GO
-- create lockTable when not already there
IF object_id('lockTable') is null
BEGIN
CREATE TABLE lockTable(database_name nchar(128), victim_spid smallint, victim_status nchar(30), victim_query nvarchar(max), victim_query_start datetime, victim_query_duration bigint,
blocker_spid char(5), blocker_query nvarchar(max),--blocker_login_name nvarchar(128)
blocker_query_start datetime)
END
-- table variable to store the current locks
DECLARE @currentLocks TABLE(database_name nchar(128), victim_spid smallint, victim_status nchar(30), victim_query nvarchar(max), victim_query_start datetime, victim_query_duration bigint,
blocker_spid char(5), blocker_query nvarchar(max),--blocker_login_name nvarchar(128)
blocker_query_start datetime)
-- table for storing the result of the stored procedure sp_who2
DECLARE @whoTable TABLE(SPID smallint, Status nchar(30),
Login nchar(128), HostName nchar(128), BlkBy char(5), DBName nchar(128)
, Command nchar(64), CPUTime nchar(64), DiskIO nchar(64), LastBatch nchar(64), ProgramName nchar(64), SPID2 smallint, REQUESTID int)
-- Uncomment to make this script poll every 10 seconds...
--WHILE 1=1
--BEGIN
--DELETE FROM @currentLocks
--DELETE FROM @whoTable
-- execute sp_who2 and store results into the whoTable table-var
INSERT INTO @whoTable EXEC sp_who2
-- insert all information of the current locks into the lockTable
INSERT INTO @currentLocks
SELECT WT.DBName AS database_name,
WT.SPID AS victim_spid,
WT.Status AS victim_status,
VICTIM_STATEMENT.Text AS victim_query,
VICTIM_SESSION.last_request_start_time AS victim_query_start,
VICTIM_TASK.wait_duration_ms AS victim_query_duration,
WT.BlkBy AS blocker_spid,
BLKR_STATEMENT.text AS blocker_query,
--BLKR_SESSION.login_name AS blocker_login_name,
BLKR_SESSION.last_request_start_time AS blocker_query_start
FROM @whoTable WT
JOIN sys.dm_exec_sessions BLKR_SESSION ON BLKR_SESSION.session_id = BlkBy
JOIN sys.dm_tran_session_transactions BLKR_TRANSACTION ON BLKR_SESSION.session_id = BLKR_TRANSACTION.session_id
JOIN sys.dm_tran_active_transactions BLKR_ACTIVE_TRANS ON BLKR_TRANSACTION.transaction_id = BLKR_ACTIVE_TRANS.transaction_id
JOIN sys.dm_exec_connections BLKR_CONNECTION ON BLKR_CONNECTION.session_id = BLKR_SESSION.session_id
CROSS APPLY sys.dm_exec_sql_text(BLKR_CONNECTION.most_recent_sql_handle) AS BLKR_STATEMENT
JOIN sys.dm_exec_sessions VICTIM_SESSION ON VICTIM_SESSION.session_id = SPID
JOIN sys.dm_exec_connections VICTIM_CONNECTION ON VICTIM_CONNECTION.session_id = VICTIM_SESSION.session_id
JOIN sys.dm_os_waiting_tasks VICTIM_TASK ON VICTIM_TASK.session_id = VICTIM_CONNECTION.session_id
CROSS APPLY sys.dm_exec_sql_text(VICTIM_CONNECTION.most_recent_sql_handle) AS VICTIM_STATEMENT
WHERE BlkBy <> ' .' AND BlkBy <> CAST (SPID AS char(5))
-- remove lock info from the lockTable of locks which are still locked (they will be re-inserted with the new duration info
DELETE [rows]
FROM lockTable [rows]
WHERE EXISTS (
SELECT TOP 1 *
FROM @currentLocks cl
WHERE cl.victim_spid = [rows].victim_spid AND
cl.victim_query_start = [rows].victim_query_start AND
cl.blocker_spid = [rows].blocker_spid AND
cl.blocker_query_start = [rows].blocker_query_start
)
-- insert all current locks into the lockTable
INSERT INTO lockTable
SELECT * FROM @currentLocks
--WAITFOR DELAY '00:00:10'
--END
-- report the locks found up until now
SELECT *
FROM lockTable
-- uncomment (or run selected) to drop lockTable
-- DROP TABLE lockTable
Thank Q Rick For Your Valuble Information Sharing in My Blog
Post a Comment