Edit

Share via

Error 2767 "Could not locate statistics" when query against the secondary replica fails

  • Article

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:

    SQL 
    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:

    PowerShell 
    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:

    PowerShell 
    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:

    1. 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:

      SQL 
      SELECT object_name([object_id]) FROM sys.stats WHERE name='<StatisticsName>'

      After running the command, the table name <TableName> is returned.

    2. Run the DBCC SHOW_STATISTICS command by using the name of the table and statistics to determine the column name:

      SQL 
      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>

    3. To drop the query-generated statistics in the table <TableName> on the primary replica, run the following command:

      SQL 
      DROP STATISTICS <TableName>.<StatisticsName>
GO

    4. To create the permanent statistics in the column <ColumnName> of the table <TableName> on the primary replica, run the following command:

      SQL 
      CREATE STATISTICS <PermanentStatistics> ON <TableName>(<ColumnName>)
GO