How to get index name from sys.dm_tran_locks

Question

I'm working on a multi-tenanted solution. As such we have a lot of databases on our SQL Server. I'm looking at a locking issue and need to be able to see which locks are being waited on.

I have queried the sys.dm_tran_locks dynamic view, but would like to also include the, database, object and index names referred to by the resource_associated_entity_id column. This links through to the sys.partitions table, but that table only returns rows for the current database and the locks I'm looking at are distributed around several databases.

Is there a way to get this information without creating a cursor and using dynamically generated queries?

Answer 1

Technically "no". There is no meta-data function that will get index names outside of the current database context. This sad fact also adversely affects using the following when trying to use them in a similarly useful manner:

• sys.dm_db_index_physical_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_index_usage_stats

So you have two options:

• Dynamic SQL (as you have suspected)
• A SQLCLR function that accepts all required IDs, including database_id, and connects via "context connection = true" to query the intended system view (which is how I have solved this issue in terms of index names and sys.objects in SQL#, but the technique of taking in database_id as a parameter to a SQLCLR Function--Scalar or TVF--and having it connect locally via dynamically built SQL to said database would work the same for any other meta-data).
  EDIT
  To make it even easier, pass in the database name and concatenate that into the SqlCommand.CommandText and use the IDs as SqlParameters. So the signature would be, in terms of getting the index name:
  GetIndexName(@DatabaseName sysname, @object_id INT, @index_id INT)
  and used as follows:
  GetIndexName(DB_NAME(dmv.database_id), dmv.object_id, dmv.index_id)

Just FYI, here is a list of meta-data functions, grouped by whether or not you can specify the database or are current-database-only:

Can Specify Database

• INDEX_COL (via fully-qualified object name)
• OBJECT_ID (via fully-qualified object name)
• OBJECT_NAME (via database_id parameter)
• OBJECT_SCHEMA_NAME (via database_id parameter)

Current-Database-Only

• INDEXPROPERTY
• OBJECT_DEFINITION
• OBJECTPROPERTY
• OBJECTPROPERTYEX
• SCHEMA_ID
• SCHEMA_NAME
• TYPE_ID
• TYPE_NAME

Answer 2

Short answer: no, there's no way to avoid this. sys.partitions and other database-specific views are annoying that way. For entire objects, you can usually get around this with OBJECT_NAME(), since that takes a database ID. For allocation IDs, you're out of luck.

Technically speaking, you can avoid writing cursors yourself by using our old pal sp_msforeachdb, but since that's not documented and usually doesn't give the results in the format you want anyway, writing it yourself is a better idea.

Because sys.dm_tran_locks is highly dynamic, it won't be easy to get the locking information at just the time you need it. A possible alternative is to use a profiler trace or extended event session to get the exact sequence of locks acquired (obviously, since this is tons of data, you would only do this when troubleshooting specific queries). If all you need is to know who's holding up things at the time the proverbial shit hits the fan, sp_msforeachdb 'dbcc opentran(''?'')' is still surprisingly effective.