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:
-
Drop the corrupted statistics, this can be done through the following steps:
-
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]
-
Drop the statistics on the primary:
Drop Statistics table_name._WA_Sys_xxxxxxxxxx;
-
Run the user select query on the primary, which will auto-create the statistics and it will be replicated to the secondary replica.
-
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.