Options
Version 5.0.8.2656 Deadlock
crimdon
Posts: 54 Bronze 3
Started getting this from the Redgate database:
deadlock-list
deadlock victim=process32236b0c8
process-list
process id=process32236b0c8 taskpriority=0 logused=220 waitresource=KEY: 61:72057594096910336 (08226f03141a) waittime=7395 ownerId=57084541 transactionname=user_transaction lasttranstarted=2016-02-24T10:22:36.527 XDES=0x374afb6a8 lockMode=RangeS-U schedulerid=4 kpid=9400 status=suspended spid=151 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-02-24T10:22:36.527 lastbatchcompleted=2016-02-24T10:22:36.527 lastattention=1900-01-01T00:00:00.527 clientapp=SQL Monitor - Repository hostname=xxx hostpid=4664 loginname=xxx isolationlevel=read committed (2) xactid=57084541 currentdb=61 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 sqlhandle=0x02000000b54bc92c1cbffa916d3131c082a19adffe00f8f50000000000000000000000000000000000000000
insert bulk [data].[Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings] ([Id] BigInt, [SightingDate] BigInt) with (CHECK_CONSTRAINTS)
inputbuf
insert bulk [data].[Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings] ([Id] BigInt, [SightingDate] BigInt) with (CHECK_CONSTRAINTS)
process id=process2948cd498 taskpriority=0 logused=192600 waitresource=KEY: 61:72057594096910336 (913c11fc1262) waittime=3947 ownerId=57084307 transactionname=user_transaction lasttranstarted=2016-02-24T10:22:35.887 XDES=0x293c2cbc0 lockMode=X schedulerid=3 kpid=1200 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-02-24T10:22:35.887 lastbatchcompleted=2016-02-24T10:22:35.887 lastattention=2016-02-24T09:49:10.967 clientapp=SQL Monitor - Repository hostname=xxx hostpid=4664 loginname=xxx isolationlevel=snapshot (5) xactid=57084307 currentdb=61 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=RedGateMonitor.data.Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings_DeleteChunk line=18 stmtstart=1080 stmtend=2214 sqlhandle=0x03003d00cf4ee43875a10c01a6a4000001000000000000000000000000000000000000000000000000000000
DELETE [data].[Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings]
FROM @Chunk c
INNER JOIN [data].[Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings] d WITH (ROWLOCK) ON c.[SightingDate] = d.[SightingDate] AND c.[Id] = d.[Id]
-- FORCE ORDER: The order above is carefully chosen, and this prevents pathological orders.
-- LOOP JOIN: Profiling shows that this tends to improve performance, as long as the number of rows to be deleted is small
-- compared to the number of rows in the table, by encouraging index seeks.
OPTION (FORCE ORDER, LOOP JOIN, MAXDOP 1)
inputbuf
Proc [Database Id = 61 Object Id = 954486479]
resource-list
keylock hobtid=72057594096910336 dbid=61 objectname=RedGateMonitor.data.Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings indexname=Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings_Id_SightingDate id=lock37f07ec80 mode=X associatedObjectId=72057594096910336
owner-list
owner id=process2948cd498 mode=X
waiter-list
waiter id=process32236b0c8 mode=RangeS-U requestType=wait
keylock hobtid=72057594096910336 dbid=61 objectname=RedGateMonitor.data.Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings indexname=Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings_Id_SightingDate id=lock33503c600 mode=RangeS-U associatedObjectId=72057594096910336
owner-list
owner id=process32236b0c8 mode=RangeS-U
waiter-list
waiter id=process2948cd498 mode=X requestType=wait
deadlock-list
deadlock victim=process32236b0c8
process-list
process id=process32236b0c8 taskpriority=0 logused=220 waitresource=KEY: 61:72057594096910336 (08226f03141a) waittime=7395 ownerId=57084541 transactionname=user_transaction lasttranstarted=2016-02-24T10:22:36.527 XDES=0x374afb6a8 lockMode=RangeS-U schedulerid=4 kpid=9400 status=suspended spid=151 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-02-24T10:22:36.527 lastbatchcompleted=2016-02-24T10:22:36.527 lastattention=1900-01-01T00:00:00.527 clientapp=SQL Monitor - Repository hostname=xxx hostpid=4664 loginname=xxx isolationlevel=read committed (2) xactid=57084541 currentdb=61 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 sqlhandle=0x02000000b54bc92c1cbffa916d3131c082a19adffe00f8f50000000000000000000000000000000000000000
insert bulk [data].[Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings] ([Id] BigInt, [SightingDate] BigInt) with (CHECK_CONSTRAINTS)
inputbuf
insert bulk [data].[Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings] ([Id] BigInt, [SightingDate] BigInt) with (CHECK_CONSTRAINTS)
process id=process2948cd498 taskpriority=0 logused=192600 waitresource=KEY: 61:72057594096910336 (913c11fc1262) waittime=3947 ownerId=57084307 transactionname=user_transaction lasttranstarted=2016-02-24T10:22:35.887 XDES=0x293c2cbc0 lockMode=X schedulerid=3 kpid=1200 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2016-02-24T10:22:35.887 lastbatchcompleted=2016-02-24T10:22:35.887 lastattention=2016-02-24T09:49:10.967 clientapp=SQL Monitor - Repository hostname=xxx hostpid=4664 loginname=xxx isolationlevel=snapshot (5) xactid=57084307 currentdb=61 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=RedGateMonitor.data.Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings_DeleteChunk line=18 stmtstart=1080 stmtend=2214 sqlhandle=0x03003d00cf4ee43875a10c01a6a4000001000000000000000000000000000000000000000000000000000000
DELETE [data].[Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings]
FROM @Chunk c
INNER JOIN [data].[Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings] d WITH (ROWLOCK) ON c.[SightingDate] = d.[SightingDate] AND c.[Id] = d.[Id]
-- FORCE ORDER: The order above is carefully chosen, and this prevents pathological orders.
-- LOOP JOIN: Profiling shows that this tends to improve performance, as long as the number of rows to be deleted is small
-- compared to the number of rows in the table, by encouraging index seeks.
OPTION (FORCE ORDER, LOOP JOIN, MAXDOP 1)
inputbuf
Proc [Database Id = 61 Object Id = 954486479]
resource-list
keylock hobtid=72057594096910336 dbid=61 objectname=RedGateMonitor.data.Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings indexname=Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings_Id_SightingDate id=lock37f07ec80 mode=X associatedObjectId=72057594096910336
owner-list
owner id=process2948cd498 mode=X
waiter-list
waiter id=process32236b0c8 mode=RangeS-U requestType=wait
keylock hobtid=72057594096910336 dbid=61 objectname=RedGateMonitor.data.Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings indexname=Cluster_SqlServer_TopQueries_QueryWaitStats_Sightings_Id_SightingDate id=lock33503c600 mode=RangeS-U associatedObjectId=72057594096910336
owner-list
owner id=process32236b0c8 mode=RangeS-U
waiter-list
waiter id=process2948cd498 mode=X requestType=wait
Comments
This just looks like SQL Monitor deadlocking with itself on collecting / purging data from one of the Top Queries tables - SQL Monitor is able to handle situations like this and it is a known occurrence with internal reference SRP-9636. There should be steps to prevent this from happening in work scheduled for version 5.1.
Are you getting a large number of these deadlocks or just random occurrences?
Kind regards,
Alex
Have you visited our Help Center?
If they are sporadic then it's nothing to worry about. We'll see what 5.1 brings, hopefully it will be prevented from occurring at all!
Kind regards,
Alex
Have you visited our Help Center?
I have the same issue, only for me the deadlocks are more frequent and the purge process was left behind leaving me with a fast growing database. Is there a cleanup script that I can use on the DB while I have the Base Monitor and Web Server turned off. I have the the MSSQL RedGate Monitor 5.0.8, backend runs on a SQL Ent 2012 SP3. Thanks!
If you could send in an email to support@red-gate.com referencing this forum post we can get a script to force the purge sent to you.
Kind regards,
Alex
Have you visited our Help Center?