How can we help you today? How can we help you today?

SQL Monitor Causing Deadlocks

We are seeing deadlocks in our application when SQL Monitor is enabled at one of our client sites. I am waiting for client to confirm SQL Monitor version as suspect it may be an older version and this may be resolved in later version - I am hoping you can confirm this so we can ask client to upgrade safely. What we are seeing is the following:

  • Our stored procs are using #temp tables - when an application uses these it will cause Sch-M locks
  • RedGate SQL Monitor query is using NOLOCK option - this causes Sch-S locks which results in blocks

SELECT TOP 10
tb.name AS [temp_table_name],
stt.row_count AS [row_count],
stt.used_page_count * 8 AS [used_pages],
stt.reserved_page_count * 8 AS [reserved_pages]
FROM
tempdb.sys.partitions prt WITH (NOLOCK)
INNER JOIN
tempdb.sys.dm_db_partition_stats stt WITH (NOLOCK)
ON prt.partition_id = stt.partition_id AND prt.partition_number = stt.partition_number
INNER JOIN
tempdb.sys.tables tb WITH (NOLOCK)
ON stt.object_id = tb.object_id
ORDER BY
stt.reserved_page_count


Combination of Sch-S and Sch-M locks causes deadlocks. Why does SQL Monitor need to use NOLOCK on the system tables? And is this changed/fixed in latest version?

thanks
Aksh
0

Comments

1 comment

Sort by
  • Russell D
    Hi,

    Unfortunately, this is a case of the query causing the deadlock, and it's not something we can fix.

    Reading table metadata requires taking out a schema stability lock (Sch-S) so that you can't drop a table while its metadata is being read. Whereas dropping a temporary object (either explicitly or at the end of a query running) requires a schema modification lock (Sch-M); thus these two locking modes aren't compatible: (https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver15#lock_compatibility), which leads to deadlock since someone has to give. It's one of those things that's necessary for SQL Server to maintain internal consistency.

    We are working on lowering deadlock priority of Monitor queries, but there are downsides to doing this - such as decreased accuracy of monitoring which can lead to gaps on graphs - but we haven't finalised this at all yet.
    Russell D
    0

Add comment

Please sign in to leave a comment.