Blog Post

Azure Database Support Blog
2 MIN READ

Read only replica corrupted statistics

Laith_Ayesh's avatar
Laith_Ayesh
Icon for Microsoft rankMicrosoft
Jan 18, 2023

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. 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] 
    2. Drop the statistics on the primary:

      Drop Statistics table_name._WA_Sys_xxxxxxxxxx;
    3. Run the user select query on the primary, which will auto-create the statistics and it will be replicated to the secondary replica.

    4. Test the query on the secondary replica.

  2. 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. 

Updated Apr 17, 2023
Version 2.0
No CommentsBe the first to comment