August 26, 2011 at 2:30 pm
I am getting a lot of NULL values for object name ([Parent Object] column in SQL below) when querying sys.dm_tran_locks and the resource_type column is OBJECT, PAGE, KEY or RID.
Here is the SQL I am running:
SELECT
DTL.[resource_type] AS [resource type] ,
DTL.resource_associated_entity_id,
CASE
WHEN DTL.[resource_type] IN ( 'DATABASE', 'FILE', 'METADATA' )
THEN DTL.[resource_type]
WHEN DTL.[resource_type] = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.[resource_type] IN ( 'KEY', 'PAGE', 'RID' )
THEN (SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.[hobt_id] =
DTL.[resource_associated_entity_id]
)
ELSE 'Unidentified'
END AS [Parent Object]
FROM
sys.dm_tran_locks DTL
Here are some links discussing variations of the query above:
http://jmkehayias.blogspot.com/2008/12/troubleshooting-locking-in-database.html
http://www.insidesqlserver.com/companion/QTO_Chapter6code.txt
I have seen this issue reported here as well:
Anyone know how to reliably get object names from sys.dm_tran_locks?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 26, 2011 at 2:55 pm
Mario the object_name() function has a second parameter for the database_id;;
i suspect that's what you need to add;
try this and confirm it fixes the issue...you'll see all i did was add OBJECT_NAME([object_id],DTL.resource_database_id)
in my sample, i didn't have any null objects to prove it on my side for testing.
SELECT
DTL.[resource_type] AS [resource type] ,
DTL.resource_associated_entity_id,
CASE
WHEN DTL.[resource_type] IN ( 'DATABASE', 'FILE', 'METADATA' )
THEN DTL.[resource_type]
WHEN DTL.[resource_type] = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.[resource_type] IN ( 'KEY', 'PAGE', 'RID' )
THEN ( SELECT OBJECT_NAME([object_id],DTL.resource_database_id)
FROM sys.partitions
WHERE sys.partitions.[hobt_id] =
DTL.[resource_associated_entity_id]
)
ELSE 'Unidentified'
END AS [Parent Object]
FROM
sys.dm_tran_locks DTL
Lowell
August 26, 2011 at 3:00 pm
The partition_id and object_id are database-specific, they're not server-wide values. sys.partitions is per-database.
I get the correct names only if I run that in the context of the DB that the locks are in.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 3:08 pm
Thanks, your suggestion is working.
Still getting NULLs, but not as many as before:
SELECT
DTL.[resource_type] AS [resource type] ,
DTL.resource_associated_entity_id,
CASE
WHEN DTL.[resource_type] IN ( 'DATABASE', 'FILE', 'METADATA' )
THEN DTL.[resource_type]
WHEN DTL.[resource_type] = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id,DTL.resource_database_id)
--THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.[resource_type] IN ( 'KEY', 'PAGE', 'RID' )
THEN ( SELECT OBJECT_NAME([object_id],DTL.resource_database_id)
FROM sys.partitions
WHERE sys.partitions.[hobt_id] =
DTL.[resource_associated_entity_id]
)
ELSE 'Unidentified'
END AS [Parent Object]
FROM
sys.dm_tran_locks DTL
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 26, 2011 at 3:12 pm
sys.partitions is a per-database view. Not per-server. That join is only a valid one if you're running that in the DB that has the locks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 26, 2011 at 3:30 pm
GilaMonster (8/26/2011)
sys.partitions is a per-database view. Not per-server. That join is only a valid one if you're running that in the DB that has the locks.
So is there a way to extract object-name info from sys.dm_tran_locks.resource_associated_entity_id?
Because sys.dm_tran_locks is instance-wide, not database-specific and therefore so is resource_associated_entity_id.
This part of the query should still work though:
...
WHEN DTL.[resource_type] = 'OBJECT'
THEN OBJECT_NAME(DTL.resource_associated_entity_id,DTL.resource_database_id)
...
It looks like this query, which can be found over the place, is actually not entirely correct...
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 27, 2011 at 6:10 am
Only way I can think of is to loop through all databases in the SQL instance and issue the sys.dm_tran_locks query filtering by resource_database_id.
Store the intermediate results in a temp table and retrieve the results from the temp table in the end.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 27, 2011 at 6:14 am
Or loop through and create a temp table with all of the sys.partitions (partition_id, database_id, object_id, index_id) in it and join to that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 27, 2011 at 8:04 am
GilaMonster (8/27/2011)
Or loop through and create a temp table with all of the sys.partitions (partition_id, database_id, object_id, index_id) in it and join to that.
Good idea, I'll try it both ways and see what works best.
Thanks!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 29, 2011 at 2:45 pm
Here is a script that cycles though all databases running the DMV query on each one separately and storing the results into a temp table for final retrieval;
this is the full DMV query I was working with
SET NOCOUNT ON;
DECLARE @cnt SMALLINT;
DECLARE @loopCnt SMALLINT;
DECLARE @dbName SYSNAME;
DECLARE @database_id SMALLINT;
DECLARE @sql NVARCHAR(MAX);
CREATE TABLE #blocks
(
resource_type NVARCHAR(300)
, parent_object NVARCHAR(300)
, lock_type NVARCHAR(300)
, request_status NVARCHAR(300)
, wait_duration_ms BIGINT
, wait_type NVARCHAR(300)
, blocked_session_id SMALLINT
, blocked_user NVARCHAR(300)
, blocked_command NVARCHAR(500)
, blocking_session_id SMALLINT
, blocking_user NVARCHAR(300)
, blocking_command NVARCHAR(500)
, blocking_resource_detail NVARCHAR(1024)
);
CREATE TABLE #databases
(
database_id SMALLINT
, dbName SYSNAME
);
INSERT INTO #databases
(
database_id
, dbName
)
SELECT
d.database_id
, d.name
FROM
sys.databases d;
SELECT @cnt = COUNT(*) FROM #databases;
SET @loopCnt = 1;
WHILE ( @loopCnt <= @cnt )
BEGIN
SELECT
@database_id = [database_id]
, @dbName = [dbName]
FROM #databases
WHERE [database_id] = @loopCnt;
SET @sql = '
USE [' + @dbName + ']; ' + '
SELECT
DTL.[resource_type] AS [resource type] ,
CASE
WHEN DTL.[resource_type] IN ( ''DATABASE'', ''FILE'', ''METADATA'' )
THEN DTL.[resource_type]
WHEN DTL.[resource_type] = ''OBJECT''
THEN OBJECT_NAME(DTL.resource_associated_entity_id)
WHEN DTL.[resource_type] IN ( ''KEY'', ''PAGE'', ''RID'')
THEN ( SELECT OBJECT_NAME([object_id])
FROM sys.partitions
WHERE sys.partitions.[hobt_id] =
DTL.[resource_associated_entity_id]
)
ELSE ''Unidentified''
END AS [Parent Object] ,
DTL.[request_mode] AS [Lock Type] ,
DTL.[request_status] AS [Request Status] ,
DOWT.[wait_duration_ms] AS [wait duration ms] ,
DOWT.[wait_type] AS [wait type] ,
DOWT.[session_id] AS [blocked session id] ,
DES_blocked.[login_name] AS [blocked_user] ,
LEFT (
SUBSTRING(dest_blocked.text, DER.statement_start_offset / 2,
( CASE WHEN DER.statement_end_offset = -1
THEN DATALENGTH(dest_blocked.text)
ELSE DER.statement_end_offset
END - DER.statement_start_offset ) / 2 ) , 500 )
AS [blocked_command] ,
DOWT.[blocking_session_id] AS [blocking session id] ,
DES_blocking.[login_name] AS [blocking user] ,
LEFT (DEST_blocking.[text], 500) AS [blocking command] ,
DOWT.resource_description AS [blocking resource detail]
FROM
sys.dm_tran_locks DTL WITH ( NOLOCK )
INNER JOIN
sys.dm_os_waiting_tasks DOWT WITH ( NOLOCK )
ON
DTL.lock_owner_address = DOWT.resource_address
INNER JOIN
sys.[dm_exec_requests] DER WITH ( NOLOCK )
ON
DOWT.[session_id] = DER.[session_id]
INNER JOIN
sys.dm_exec_sessions DES_blocked WITH ( NOLOCK )
ON
DOWT.[session_id] = DES_Blocked.[session_id]
INNER JOIN
sys.dm_exec_sessions DES_blocking WITH ( NOLOCK )
ON
DOWT.[blocking_session_id] = DES_Blocking.[session_id]
INNER JOIN
sys.dm_exec_connections DEC WITH ( NOLOCK )
ON
DTL.[request_session_id] = DEC.[most_recent_session_id]
CROSS APPLY
sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST_Blocking
CROSS APPLY
sys.dm_exec_sql_text(DER.sql_handle) AS DEST_Blocked
WHERE DTL.[resource_database_id] = ' + CAST ( @database_id AS NVARCHAR );
INSERT INTO #blocks
(
resource_type
, parent_object
, lock_type
, request_status
, wait_duration_ms
, wait_type
, blocked_session_id
, blocked_user
, blocked_command
, blocking_session_id
, blocking_user
, blocking_command
, blocking_resource_detail
)
EXEC ( @sql );
SET @loopCnt = @loopCnt + 1;
END
SELECT
resource_type
, parent_object
, lock_type
, request_status
, wait_duration_ms
, wait_type
, blocked_session_id
, blocked_user
, blocked_command
, blocking_session_id
, blocking_user
, blocking_command
, blocking_resource_detail
FROM
#blocks;
DROP TABLE #databases;
DROP TABLE #blocks;
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 31, 2011 at 8:32 am
here I wrote an article about that: http://aiellodba.blogspot.com/2011/08/entendiendo-el-waitresource.html
August 31, 2011 at 8:36 am
Aiello DBA (8/31/2011)
Thanks, I'll take a look.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 1, 2011 at 8:49 am
Marios Philippopoulos (8/29/2011)
Here is a script that cycles though all databases running the DMV query on each one separately and storing the results into a temp table for final retrieval;...
The above script, cycling through all databases to get the Parent Object, is too slow to be useful for reporting purposes.
I think I will dump it and focus instead on parsing the information of the sys.dm_os_waiting_tasks.resource_description column (returned as blocking_resource_detail in the above script).
I'm currently having a problem with this, posted here:
http://qa.sqlservercentral.com/Forums/Topic1168711-391-1.aspx
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 1, 2011 at 9:32 am
you well come 🙂
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply