Error 2767 "Could not locate statistics" when query against the secondary replica fails
Applies to: SQL Server, Azure SQL Database, Azure SQL Managed Instance
This article provides workarounds for the issue that a query against the secondary replica fails with error 2767 "Could not locate statistics".
Symptom
When offloading 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_STATISTICS
is set to ON
) 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
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>" -ReadableSecondary
This 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>" -ReadableSecondary
This 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>') GO
The 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
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for