Selecting from sys.dm_tran_locks takes 100+ ms

Question

Our application uses SQL Server application locks and we have cases where we need to see whether a given lock exists or not. To do this, we retrieve and filter information from sys.dm_tran_locks.

This works, but even the simplest query (SELECT * FROM sys.dm_tran_locks) against this DMV in our system takes over 100 ms (CPU and duration both), and can sometimes range above 500 ms. This is when running either as the superadmin user or not, from SSMS or not.

There are approximately 10,000 rows returned at maximum from a full SELECT, and we're running on a VM with plentiful CPU and RAM, and a fast disk subsystem. It doesn't seem like this query should take nearly this long.

Has anyone seen this before or know what might be going on here? I'm happy to provide more information if that would be helpful.

Answer 1

You should use APPLOCK_MODE and APPLOCK_TEST instead of scanning the locks DMV.

Background

The sys.dm_tran_locks DMV itself employs the widely used streaming table-valued function (sTVF) interface, exposed via T-SQL using the internal-only OPENROWSET(TABLE ... syntax. The interface contains the usual Open, GetRow, and Close methods used for all execution engine iterators.

The sTVF pattern is used by very many system functions and views. How each one initialises and provides row-at-a-time access to the underlying data varies according to the data source.

Some example call stacks for this particular DMV below:

_open

_{get row}

_{describe lock owner}

_{logical stability lock}

_{lock resource}

_{associated object id}

_{output column data type}

There is also variation around which (if any) parameters supplied to the sTVF can be pushed down into the internal data access. Where pushdown is supported, rows are filtered before being surfaced to the query processor.

Most sTVFs support passing parameters as outer references (e.g. column values provided by an APPLY) but not all. Support for this aspect has improved gradually over several major product versions.

The documentation summarises quite well:

Because sys.dm_tran_locks is populated from internal lock manager data structures, maintaining this information does not add extra overhead to regular processing.

Materializing the view does require access to the lock manager internal data structures. This can have minor effects on the regular processing in the server. These effects should be unnoticeable and should only affect heavily used resources.

Because the data in this view corresponds to live lock manager state, the data can change at any time, and rows are added and removed as locks are acquired and released.

Applications querying this view might experience unpredictable performance due to the nature of protecting the integrity of lock manager structures.

This view has no historical information.