0

We have an Always On architecture of 4 servers. 2 servers are in availability mode synchronous commit. let's call them A and B. and 2 servers are in availability mode asynchronous commit. let's call them C and D.

In our database, AUTO_UPDATE_STATISTICS is on as well as AUTO_UPDATE_STATISTICS_ASYNC is on. We have a daily job using ola hallengren's script to update statistics.

We have a table that gets updated frequently. It is not big. Data is deleted and added all the time.

Our issue is that we get throughout the day several deadlocks on this table on updating statistics on a non-index column on the C/D server. Seems the primary is updating statistics before or during the async secondary is just doing the first update stats.

Our isolation level is read committed due to the fact that money transactions is our core business.

Would love any assistance to solve this.

deadlock graph

<deadlock>
   <victim-list>
      <victimProcess id="process251f88cd088" />
   </victim-list>
   <process-list>
      <process id="process251f88cd088" taskpriority="20" logused="0" waitresource="METADATA: database_id = 9 STATS(object_id = 1244635577, stats_id = 5), lockPartitionId = 0" waittime="1" ownerId="7702177286" transactionname="UpdateQPStats" lasttranstarted="2023-03-19T17:09:24.460" XDES="0x2d0bc918460" lockMode="Sch-M" schedulerid="10" kpid="5388" status="background" spid="56" sbid="0" ecid="0" priority="0" trancount="1">
         <executionStack />
         <inputbuf />
      </process>
      <process id="process24f4fd95848" taskpriority="-20" logused="0" waitresource="METADATA: database_id = 9 STATS(object_id = 1244635577, stats_id = 5), lockPartitionId = 9" waittime="18" XDES="0x25328670040" lockMode="Sch-M" schedulerid="3" kpid="6952" status="background" spid="93" sbid="0" ecid="0" priority="0" trancount="0">
         <executionStack />
         <inputbuf />
      </process>
   </process-list>
   <resource-list>
      <metadatalock subresource="STATS" classid="object_id = 1244635577, stats_id = 5" dbid="9" lockPartition="0" id="lock27adc589c80" mode="Sch-M">
         <owner-list>
            <owner id="process24f4fd95848" mode="Sch-M" />
         </owner-list>
         <waiter-list>
            <waiter id="process251f88cd088" mode="Sch-M" requestType="wait" />
         </waiter-list>
      </metadatalock>
      <metadatalock subresource="STATS" classid="object_id = 1244635577, stats_id = 5" dbid="9" lockPartition="9" id="lock26b98caf480" mode="Sch-S">
         <owner-list>
            <owner id="process251f88cd088" mode="Sch-S" />
         </owner-list>
         <waiter-list>
            <waiter id="process24f4fd95848" mode="Sch-M" requestType="wait" />
         </waiter-list>
      </metadatalock>
   </resource-list>
</deadlock>

We tried turning AUTO_UPDATE_STATISTICS_ASYNC off. We tried to cancel update statistics job as well. None of the above solved our issue.
We didn't try to move C,D replica servers to Sync mode because it is our report servers.

Hogan
  • 69,564
  • 10
  • 76
  • 117
Tom
  • 3
  • 3
  • I used this to format your XML https://www.freeformatter.com/xml-formatter.html#before-output – Hogan Mar 22 '23 at 19:55
  • I would try precluding statistics for this table from being updated via autostats with `sp_autostats` and if it matters to the performance of your application, update them explicitly on the primary (after which the updated histogram will be available at all secondaries). – Ben Thul Mar 22 '23 at 22:09
  • @BenThul Thank you, but we can't do this as there are several tables with this deadlock. It comes in different stats_id(column) and different tables, but always about statistics in the secondary. Do you maybe have another idea? – Tom Apr 16 '23 at 07:41
  • The "big hammer" would be to turn off AUTO_UPDATE_STATISTICS option for the database. That's the same suggestion that I made earlier but at a wider scope (database vs stat/table level). – Ben Thul Apr 17 '23 at 15:08

0 Answers0