1

I have seen this a couple times. There is a job running IndexOptimize that completes successfully, but running sp_BlitzLock will show a deadlock for one specific table. The deadlock command is identical with slightly different start times. I don't see a record in the CommandLog table for this, the job finishes just fine, and I don't see this happening to any other tables but this one particular one.

Command:

ALTER INDEX [PK_TableName] ON [dbo].[TableName] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON, MAXDOP = 0)

Job Code:

EXECUTE [dbo].[IndexOptimize] 
@Databases = 'DB1, DB2, DB3',
@MinNumberOfPages = 100, 
@FragmentationLow = NULL, 
@FragmentationMedium = NULL, 
@FragmentationHigh='INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', 
@PartitionLevel = 'N', 
@UpdateStatistics = 'ALL', 
@StatisticsModificationLevel= '1',
@StatisticsResample = 'Y', 
@SortInTempdb = 'Y', 
@MaxDOP = 0, 
@LogToTable = 'Y',
@TimeLimit = 3600

Deadlock Info:

enter image description here

Basically my question is: given this isn't a "real deadlock" but continues to happen only on this one particular table, is there something I can do to get this to stop showing up? Rebuild the table outside of the IndexOptimize sp? Maybe something else? I would prefer not to just ignore this oddity.

Thanks.

Improve this question
3
  • I don't remember the whole output from sp_BlitzLock, does it tell you the name of the Table?...how about the query itself that was the deadlock victim?
    – J.D.
    Feb 26, 2021 at 17:46
  • Yeah it's the same table, same command .A copy is above. Not sure how two queries were spawned from the job and only seemed to happen to this one index on this one table.
    – BCM
    Feb 26, 2021 at 17:56
  • Ok cool, just wanted to make sure because your screenshot cuts off those 2 pieces of information. I was wondering if it's possible sp_BlitzIndex ran into a deadlock itself when trying to access that table, for example, and maybe was false reporting. I've definitely seen the sp_Blitz* tools run into their own deadlocks on rare occasions.
    – J.D.
    Feb 26, 2021 at 19:02

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Browse other questions tagged or ask your own question.