Hi!
Will appreciate any help with this question - is there any way to avoid Sch-M deadlock except of not rebuilding indexes anymore? It happens about once a week between:
- Ola Hallengren’s index maintenance sp running online index rebuild with wait at low priority and self-abortion
- statistics auto update (at least I think that’s what UpdateQPStats mean)
SQL 2017 CU 27, database has auto update stats + update async enabled. Maintenance window time change isn’t possible for now, but I’m digging in that direction.
What seemed strange to me:
- I tried setting deadlock priority, but it was ignored
- Shouldn’t it WAIT_AT_LOW_PRIORITY like it was hinted instead of deadlocking?
Here is a graph:
Here is an edited xml:
xxxxxxxxxx
<deadlock>
<victim-list>
<victimProcess id="process1f861b69468" />
</victim-list>
<process-list>
<process id="process1f861b69468" taskpriority="20" logused="0" waitresource="METADATA: database_id = 5 STATS(object_id = 165575628, stats_id = 2), lockPartitionId = 10" waittime="2320" ownerId="1544446730" transactionname="UpdateQPStats" lasttranstarted="2021-12-15T02:02:05.220" XDES="0x1f7e4ef0420" lockMode="Sch-M" schedulerid="25" kpid="4864" status="background" spid="98" sbid="0" ecid="0" priority="0" trancount="1">
<executionStack />
<inputbuf>
</inputbuf>
</process>
<process id="process1f86106b848" taskpriority="5" logused="216" waitresource="OBJECT: 5:165575628:24 " waittime="77" ownerId="1544423169" transactionname="ALTER INDEX" lasttranstarted="2021-12-15T02:02:04.130" XDES="0x1f80ae60420" lockMode="Sch-M" schedulerid="11" kpid="16948" status="suspended" spid="107" sbid="0" ecid="0" priority="-5" trancount="1" lastbatchstarted="2021-12-15T02:00:00.873" lastbatchcompleted="2021-12-15T02:00:00.873" lastattention="1900-01-01T00:00:00.873" clientapp="SQLAgent - TSQL JobStep (Job 0x00000000000000000000000000000000 : Step 1)" hostname="SQLSRV" hostpid="18256" loginname="MOSCOW\sqlag" isolationlevel="read committed (2)" xactid="1544423169" currentdb="5" currentdbname="Sqldb" lockTimeout="4294967295" clientoption1="673253472" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtend="440" sqlhandle="0x0">
ALTER INDEX [nclidx] ON [dbo].[Table] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1, ABORT_AFTER_WAIT = SELF)), RESUMABLE = OFF </frame>
<frame procname="mssqlsystemresource.sys.sp_executesql" line="1" stmtstart="-1" sqlhandle="0x000">
sp_executesql </frame>
<frame procname="master.dbo.CommandExecute" line="227" stmtstart="17850" stmtend="17926" sqlhandle="0x000">
EXECUTE @sp_executesql @stmt = @Comman </frame>
<frame procname="master.dbo.IndexOptimize" line="2173" stmtstart="242248" stmtend="243456" sqlhandle="0x000">
EXECUTE @CurrentCommandOutput = dbo.CommandExecute @DatabaseContext = @CurrentDatabaseName, @Command = @CurrentCommand, @CommandType = @CurrentCommandType, @Mode = 2, @Comment = @CurrentComment, @DatabaseName = @CurrentDatabaseName, @SchemaName = @CurrentSchemaName, @ObjectName = @CurrentObjectName, @ObjectType = @CurrentObjectType, @IndexName = @CurrentIndexName, @IndexType = @CurrentIndexType, @PartitionNumber = @CurrentPartitionNumber, @ExtendedInfo = @CurrentExtendedInfo, @LockMessageSeverity = @LockMessageSeverity, @ExecuteAsUser = @ExecuteAsUser, @LogToTable = @LogToTable, @Execute = @Execut </frame>
<frame procname="adhoc" line="2" stmtstart="56" stmtend="1130" sqlhandle="0x000">
EXECUTE [dbo].[IndexOptimize]
@Databases = 'Sqldb',
@FragmentationLevel1 = 50,
@FragmentationLevel2 = 80,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@WaitAtLowPriorityMaxDuration = 1,
@WaitAtLowPriorityAbortAfterWait = 'SELF',
@UpdateStatistics = 'ALL',
@StatisticsSample = 100,
@OnlyModifiedStatistics = 'Y',
@SortInTempdb = 'Y',
@LOBCompaction = 'Y',
@LogToTable = 'Y',
@Execute = 'Y' </frame>
</executionStack>
<inputbuf>
SET DEADLOCK_PRIORITY LOW;
EXECUTE [dbo].[IndexOptimize]
@Databases = 'Sqldb',
@FragmentationLevel1 = 50,
@FragmentationLevel2 = 80,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@WaitAtLowPriorityMaxDuration = 1,
@WaitAtLowPriorityAbortAfterWait = 'SELF',
@UpdateStatistics = 'ALL',
@StatisticsSample = 100,
@OnlyModifiedStatistics = 'Y',
@SortInTempdb = 'Y',
@LOBCompaction = 'Y',
@LogToTable = 'Y',
@Execute = 'Y'
</inputbuf>
</process>
</process-list>
<resource-list>
<metadatalock subresource="STATS" classid="object_id = 165575628, stats_id = 2" dbid="5" lockPartition="10" id="lock1f7ee812080" mode="Sch-S">
<owner-list>
<owner id="process1f86106b848" mode="Sch-S" />
</owner-list>
<waiter-list>
<waiter id="process1f861b69468" mode="Sch-M" requestType="wait" />
</waiter-list>
</metadatalock>
<objectlock lockPartition="24" objid="165575628" subresource="FULL" dbid="5" objectname="Sqldb.dbo.Table" id="lock1f501606580" mode="Sch-S" associatedObjectId="165575628">
<owner-list>
<owner id="process1f861b69468" mode="Sch-S" />
<owner id="process1f861b69468" mode="Sch-S" />
</owner-list>
<waiter-list>
<waiter id="process1f86106b848" mode="Sch-M" requestType="low priority wait" />
</waiter-list>
</objectlock>
</resource-list>
</deadlock>
show all 78 lines
Thank you!