Read only replica corrupted statistics

We recently came across an issue where a select query against a read-only replica fails with one of the following errors, when the same query can be executed against the primary replica successfully.

Typical error messages reported are:

Error 1: Ms 9105, Level 16, State 27, Line 15 The provided statistics stream is corrupt.

Error 2: Msg 9122, Level 16, State 201, Line 10 The statistics ‘%.*ls' is corrupt.

Error 3: Msg 2767, Level 16, State 1, Procedure AspenDB-KPRDSB-PD.sys.sp_table_statistics2_rowset, Line 105 [batch Start Line 0] Could not locate statistics '_WA_Sys_xxxxxxxxxxx' in the system catalogs.

Since the error indicates a corruption, please make sure to double check the integrity of all the pages and structures that make up the table of the corrupted statistics:

DBCC CHECKTABLE ('Table_name');

Mitigation

There are two workarounds; you can go either of them:

1. 
   Drop the corrupted statistics, this can be done through the following steps:
    
   
   1. 
      
   2. 
      Identify the corrupted statistic(s) on the secondary replica (based on the table being fetched by the select query), the statistics should start with _WA_Sys since it was created by the auto create statistics:
       
      With StatsTableList As (
      -- Statistics on Tables
      SELECT
      [statistics] = stat.name,
      [schema] = SCHEMA_NAME(tab.schema_id),
      [Object] = OBJECT_NAME(stat.object_id),
      [Column] = COL_NAME(scol.object_id, scol.column_id)
      FROM
      sys.stats stat (NOLOCK)
      Join sys.stats_columns scol (NOLOCK) ON stat.stats_id = scol.stats_id AND stat.object_id =
      scol.object_id
      Join sys.tables tab (NOLOCK) ON tab.object_id = stat.object_id),
      StatsViewList As (
      -- Statistics on Views
      Select
      [statistics] = stat.name,
      [schema] = SCHEMA_NAME(tab.schema_id),
      [Object] = OBJECT_NAME(stat.object_id),
      [Column] = COL_NAME(scol.object_id, scol.column_id)
      From
      sys.stats stat (NOLOCK)
      Join sys.stats_columns scol (NOLOCK) ON stat.stats_id = scol.stats_id AND stat.object_id = scol.object_id
      Join sys.views tab (NOLOCK) ON tab.object_id = stat.object_id
      ),
      StatsWithStatement As (
      -- Statistics on Tables and Views with drop statement
      Select
      *, [statement]=Concat(N'Drop Statistics ', [schema], '.', [Object], '.', [statistics], ';')
      From
      StatsTableList
      Union All
      Select
      *, [statement]=Concat(N'Drop Statistics ', [schema], '.', [Object], '.', [statistics], ';')
      From
      StatsViewList)
      Select
      *
      From
      StatsWithStatement
      Where
      [Object] Like '%table name%' And
      [statistics] like '_WA%' -- System statistics
      ORDER BY
      [Object]
      
   3. 
      Drop the statistics on the primary:
       
      Drop Statistics table_name._WA_Sys_xxxxxxxxxx;
      
   4. 
      Run the user select query on the primary, which will auto-create the statistics and it will be replicated to the secondary replica.
       
       
      
   5. 
      Test the query on the secondary replica.
       
       
      

[*]

Failover the secondary DB replica, this can be done:

• 
  Using the Powershell cmdlet:
   
  Invoke-AzSqlDatabaseFailover -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -DatabaseName "Database01" -ReadableSecondary
   
  This command will failover the readable secondary replica of the database named "Database01" on the server named "Server01"
   
  

References

Invoke-AzSqlDatabaseFailover (Az.Sql) | Microsoft Learn

DBCC CHECKTABLE (Transact-SQL) - SQL Server | Microsoft Learn

Read queries on replicas - Azure SQL Database & SQL Managed Instance | Microsoft Learn

Disclaimer

Please note that the products and options presented in this article are subject to change. This article shows the mitigation of the read only corrupted statistics in January 2023.

I hope this article was helpful for you. please feel free to share your feedback in the comments section below.

Continue reading...