System Stats out of sync

Question

I have two databases that are in an availability group, one as the primary replica and the other is the secondary. I have a store procedure that returns the following error.

DBCC execution completed. If DBCC printed error messages, contact your system administrator. Msg 2767, Level 16, State 1, Procedure xxxx.sys.sp_table_statistics2_rowset, Line 105 [Batch Start Line 2] Could not locate statistics '_WA_Sys_00000026_0143D405' in the system catalogs. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

when I run the following query on my secondary replica(read-only):

SELECT OBJECT_NAME(s.object_id) AS object_name
    , COL_NAME(sc.object_id, sc.column_id) AS column_name
    , s.name AS statistics_name  
FROM sys.stats AS s 
INNER JOIN sys.stats_columns AS sc ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id  
WHERE s.name like  '%_WA_Sys_00000026_0143D405%'
ORDER BY s.name;

the stat appears:

STOJOU QTYPCU_0          _WA_Sys_00000026_0143D405
STOJOU QTYPCU_0          _WA_Sys_00000026_0143D405_readonly_database_statistics

when I run the query on the Primary replica it does not appear in the system tables but when you look under the table STOJOU it does appear.

How do I rebuild and/or resync the stats between my two systems? I am unable to drop/or create the Stat on my primary. Do I need to stop my synchronization between the primary and secondary to address this? Thanks for your assistance.

Answer 1

This is an outstanding bug in SQL Server which has been ignored for quite some time, unfortunately.

You can track down the offending statistic and drop/recreate it, or as some others in the above thread have suggested:

Clearing the userstore_dbmetadata related pools on ASYNC commit resolves the issue. DBCC FREESYSTEMCACHE (‘dbname’)

Or create a stored procedure to check if the instance is an ASYNC commit, then clear the cache using an agent job.

CREATE PROCEDURE [dbo].[sp_clear_dbmetadatacache]
AS
SET NOCOUNT ON
DECLARE @dbname SYSNAME
DECLARE @linebreak AS VARCHAR
SET @linebreak = CHAR + CHAR

CREATE TABLE #dbcc
(
command VARCHAR
)

DECLARE db_cursor CURSOR FOR
SELECT DISTINCT name
FROM sys.sysdatabases db
INNER JOIN sys.dm_hadr_database_replica_cluster_states dbcs
ON db.name = dbcs.database_name
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO dbname WHILE @FETCH_STATUS = 0
BEGIN
CREATE TABLE #avmode
(
availability_mode_desc VARCHAR
)
DECLARE value AS VARCHAR(20) DECLARE @sqlcmd2 AS NVARCHAR(150) SET @sqlcmd2 = 'SELECT DISTINCT availability_mode_desc FROM sys.availability_replicas WHERE replica_server_name = @SERVERNAME’
INSERT INTO #avmode
EXECUTE sp_executesql @sqlcmd2
SELECT @value = availability_mode_desc
FROM #avmode
BEGIN
IF @value = ‘ASYNCHRONOUS_COMMIT’
GOTO A
ELSE
GOTO B
END
A:
DECLARE @sqlcmd AS NVARCHAR
SET @sqlcmd = ‘DBCC FREESYSTEMCACHE (’‘’ + @dbname +’‘’) ’+ @linebreak + ’GO’
INSERT INTO #dbcc EXECUTE sp_executesql @sqlcmd
—PRINT @sqlcmd
B:
DROP TABLE #avmode
FETCH NEXT FROM db_cursor INTO @dbname;
END
CLOSE db_cursor;
DEALLOCATE db_cursor;

GO