Deadlocks from SQL Monitor Queries After Upgrading to 10.2.9.1104

WScottWWScottW Posts: 7 Bronze 1
in Redgate Monitor
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
Tagged:
· Share on Twitter

Best Answer

Answers

  • DonFergusonDonFerguson Posts: 202 Silver 5
    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.
    · Share on Twitter
  • WScottWWScottW Posts: 7 Bronze 1
    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.
    · Share on Twitter
  • Martin_KMartin_K Posts: 51 Silver 1
    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.


    I will edit this once we have released a fix!
    · Share on Twitter
  • WScottWWScottW Posts: 7 Bronze 1
    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.
    · Share on Twitter
  • Alex BAlex B Posts: 1,157 Diamond 4
    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.
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • WScottWWScottW Posts: 7 Bronze 1
    Thanks for the update, Alex. Wednesday works for me.
    · Share on Twitter
  • WScottWWScottW Posts: 7 Bronze 1
    Excellent. Thanks, Geoff. I just installed it.
    · Share on Twitter
  • DonFergusonDonFerguson Posts: 202 Silver 5
    As have I.  The issue appears to be fixed.  Thanks.
    · Share on Twitter
  • WScottWWScottW Posts: 7 Bronze 1
    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.
    · Share on Twitter
Sign In or Register to comment.