SQL Monitor Causing Deadlocks

Aksh

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 

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.