Applies to: SQL Server, Azure SQL Database, Azure SQL Managed Instance
Summary
When you offload read-only workloads from a primary replica to a secondary replica of an Always On availability group, a query against the secondary replica can fail with error 2767, "Could not locate statistics". This article provides workarounds for SQL Server, Azure SQL Database, and Azure SQL Managed Instance.
Symptoms
When you offload read-only workloads to a secondary replica of an Always On availability group from a primary replica, you notice that a query against the secondary replica fails with the following error message:
Msg 2767, Level 16, State 1, Procedure sp_table_statistics2_rowset, Line <LineNumber> [Batch Start Line <LineNumber>]
Could not locate statistics '<StatisticsName>' in the system catalogs.
Note: The
<StatisticsName>is the statistics generated by a query (AUTO_CREATE_STATISTICSis set toON) that's running on the primary replica.
Cause
This issue occurs because an active transaction prevents the cache invalidation log record from accessing and refreshing the statistics on the secondary replica.
Workaround
To work around the issue, use one of the following methods as required:
-
SQL Server
If the issue occurs in SQL Server, run the following command against the secondary replica to synchronize the cached statistics with the statistics on disk:
DBCC FREESYSTEMCACHE('ALL') GO[!NOTE] Execution of the DBCC command must occur after the active transaction finishes.
-
Azure SQL Database
If the issue occurs in Azure SQL Database, run the Invoke-AzSqlDatabaseFailover cmdlet to fail over the readable secondary replica:
Invoke-AzSqlDatabaseFailover -ResourceGroupName "<ResourceGroupName>" -ServerName "<ServerName>" -DatabaseName "<DatabaseName>" -ReadableSecondaryThis cmdlet fails over the readable secondary replica of the database named
<DatabaseName>on the server named<ServerName>. -
Azure SQL Managed Instance
If the issue occurs in a Business Critical service tier Azure SQL Managed Instance, run the Invoke-AzSqlInstanceFailover cmdlet to fail over to a read-only secondary node:
Invoke-AzSqlInstanceFailover -ResourceGroupName "<ResourceGroupName>" -Name "<ManagedInstanceName>" -ReadableSecondaryThis cmdlet fails over the readable secondary replica of the Managed Instance named
<ManagedInstanceName>. -
SQL Server, Azure SQL Database, and Azure SQL Managed Instance
For a more permanent workaround, replace the query-generated statistics with the permanent statistics. Follow these steps:
-
To determine the table where the query-generated statistics are defined, run the following command against the primary replica by using the statistics name in the error 2767:
SELECT object_name([object_id]) FROM sys.stats WHERE name='<StatisticsName>'After running the command, the table name
<TableName>is returned. -
Run the DBCC SHOW_STATISTICS command by using the name of the table and statistics to determine the column name:
DBCC SHOW_STATISTICS('<TableName>', '<StatisticsName>') GOThe following results display the contents of the statistics
<StatisticsName>in the table<TableName>, and you can see that the statistics are created in the column<ColumnName>of the table<TableName>.Name Updated <StatisticsName><DateTime> All density Average Length Columns 0.5 4 <ColumnName> -
To drop the query-generated statistics in the table
<TableName>on the primary replica, run the following command:DROP STATISTICS <TableName>.<StatisticsName> GO -
To create the permanent statistics in the column
<ColumnName>of the table<TableName>on the primary replica, run the following command:CREATE STATISTICS <PermanentStatistics> ON <TableName>(<ColumnName>) GO
-
Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.
