Deadlocks from SQL Monitor Queries After Upgrading to 10.2.9.1104

I upgraded by SQL Monitor dev instance from 10.0.4.26995 to 10.2.9.1104 today. Soon after, I started getting deadlock alerts caused by SQL Monitor queries against tempdb. Should I expect to see this on a daily basis?

Type	Object	Unread	Status	Last Updated
Deadlock (extended event) (2)	my-server10\(local) > tempdb	2	Event	14:05
Deadlock (extended event) (2)	my-server20\(local) > tempdb	2	Event	14:02
Deadlock (extended event)	my-server16\(local) > tempdb	1	Event	13:38
Deadlock (extended event) (3)	my-server04\(local) > tempdb	3	Event	13:07
Deadlock (extended event) (3)	my-server12\(local) > tempdb	3	Event	12:50
Deadlock (extended event) (3)	my-server18\(local) > tempdb	3	Event	12:21
Deadlock (extended event)	my-server14\(local) > tempdb	1	Event	12:19
Deadlock (extended event)	my-server08\(local) > tempdb	1	Event	12:18

This is one of the non-victim queries involved.

--RedGateIgnore
SET NOCOUNT ON;
DECLARE @proc TABLE(session_id SMALLINT PRIMARY KEY, blocking_session_id SMALLINT, wait_time BIGINT);
INSERT INTO @proc SELECT s.session_id, ISNULL(MAX(r.blocking_session_id),0), ISNULL(SUM(CAST(wait_time AS BIGINT)),0) FROM
sys.dm_exec_sessions s LEFT JOIN sys.dm_exec_requests r ON s.session_id = r.session_id GROUP BY s.session_id;

DECLARE @start_time AS DATETIME;
SELECT @start_time = login_time FROM sys.dm_exec_sessions WHERE session_id = 1;

;WITH block_cte AS
(
    SELECT session_id, CAST(0 AS BIGINT) [wait_time], session_id [root_spid]
    FROM @proc
    WHERE blocking_session_id = 0
    UNION ALL
    SELECT blocked.session_id, blocked.wait_time, block_cte.[root_spid]
    FROM @proc AS blocked
    INNER JOIN block_cte ON blocked.blocking_session_id = block_cte.session_id AND blocked.blocking_session_id <> blocked.session_id
),

block_aggregate_cte AS (
    SELECT [root_spid], SUM(wait_time) [total_blocking_time]
    FROM block_cte
    GROUP BY [roo

This is the victim related to that deadlock.

--RedGateIgnore
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
INNER JOIN
    tempdb.sys.dm_db_partition_stats stt
    ON prt.partition_id = stt.partition_id AND prt.partition_number = stt.partition_number
INNER JOIN
    tempdb.sys.tables tb
    ON stt.object_id = tb.object_id
ORDER BY
    stt.reserved_page_count;

Thanks,
Scott

Scott Walls September 24, 2020 19:30

Comments

10 comments

Me too. Since upgrading from 10.2.8 to 10.2.9 yesterday, I am getting an abundance of Red Monitoring Error boxes on the Global Overview page. When tracing the cause it's due to the same deadlock condition being created by SQL Monitor as described by WScottW above.

DonFerguson September 25, 2020 15:59

0
Glad to know I'm not alone. I have a total of 165 deadlock alerts from SQL Monitor now. I opened a support case with them yesterday and sent some more information this morning.

WScottW September 25, 2020 16:08

0
Posting for clarity's that this is indeed a problem with the latest release. We have logged an issue for this and are looking into it.

Currently the only way around it would be to downgrade back to version 10.0.4.26995. It is very important to note, however, that you would need a backup of your data repository from version 10.0.4.26995 in order to do this.

The installer can be downloaded from here

I will edit this once we have released a fix!

Martin_K September 25, 2020 20:24

0
Thanks, Martin_K. That's what I gathered from the response to my case today. I have a backup, but I'll just leave it as is until the new release is out. It seems like a new one comes out about every two weeks.

WScottW September 25, 2020 20:31

0
Further to this, the team have found and corrected the issue and it will be available in 10.2.10 which should be available Wednesday, though I am seeing if this can be done sooner.

The problem looks more severe than it is as it shows the card as red with a monitoring error (depending on your settings) and generates the deadlock alerts, but it is only affecting the new tempdb sampler which is being deadlocked (victim) by the query wait stats sampler so no previous samplers or information should be being affected.

I will update further when the release is available.

Alex B September 28, 2020 14:01

0
Thanks for the update, Alex. Wednesday works for me.

WScottW September 28, 2020 14:07

0
A fix for this issue has been released in 10.2.10. You can download it here

GeoffSimons September 29, 2020 15:26

0
Excellent. Thanks, Geoff. I just installed it.

WScottW September 29, 2020 18:24

0
As have I. The issue appears to be fixed. Thanks.

DonFerguson September 29, 2020 20:01

0
It has been a little over 2-hours for me and no more tempdb deadlocks. Impressive turn-around time on the new build! Please extend my thanks to the team.

WScottW September 29, 2020 20:44

0

Add comment

Please sign in to leave a comment.

How can we help you today?

Deadlocks from SQL Monitor Queries After Upgrading to 10.2.9.1104

Comments

Add comment