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

[Yochanan_Rachamim]

Scenario:

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

Full discussion about this error and possible workaround is available here



A suggested mitigation is to convert auto created statistics to user created statistics.

as it should be done for each, and every auto created stats it would only make sense that we would like to make it automated.

in case we keep the auto create statistics active for the database we might face the issue again once new auto created statistics will be created in the database and will be attempted to use on the secondary.



Solution:

To help with automating the process I am sharing here a script that will do what was suggested in the original article for every auto created statistics.

you will also be able to run the script (as stored procedure in your database) to monitor for newly auto created stats and to automate their conversion to user created stats.



I hope you will find it useful.



Please make sure to set the [@Whatif] parameter to 0 to trigger the action, otherwise it will only be reporting the suggested steps.



here is the stored procedure code:

CREATE OR ALTER PROCEDURE MigrateAutoCreatedStatsToUserCreated (@WhatIf bit =1) as 
BAGIN
	/*
	Date: 2024-06-03 | V1.0
	Authored by: Yochanan Rachamim
	
	Purpose: 
	This procedure created to help with mitigation of error 2767 as described here:
	https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/availability-groups/error-2767-query-secondary-replica-fails

	this will drop auto created statistics and recreate them as user created statistics. 
	this script can be used as automated process that run on regular basis to prevent reoccurances of exception 2767

	DISCLAIMER : THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
	
	*/
	SET NOCOUNT ON;
	DECLARE @iPrefix varchar(5) = 'User_';

	SELECT
	--ss.object_id
	--,table_name = st.name
	--, stats_name = ss.name
	--,ss.stats_id
	--,sc.stats_column_id
	--,sc.column_id
	--,scol.name column_name 
	DROP_COMMAND = 'DROP STATISTICS ['+ sschem.name +'].[' + st.name + '].['+ ss.name +']',
	CREATE_COMMAND = 'CREATE STATISTICS ['+ @iPrefix + ss.name +'] ON ['+ sschem.name +'].[' + st.name + ']('+ scol.name +')'
	INTO #StatsTemp
	FROM sys.stats ss JOIN sys.stats_columns sc ON ss.object_id=sc.object_id and ss.stats_id = sc.stats_id
	JOIN sys.tables st ON st.object_id = ss.object_id
	JOIN sys.schemas sschem ON st.schema_id = sschem.schema_id
	JOIN sys.columns scol ON st.object_id = scol.object_id and sc.column_id = scol.column_id
	WHERE ss.auto_created=1;

	DECLARE @t TABLE(DROP_COMMAND NVARCHAR(max), CREATE_COMMAND NVARCHAR(max));
	DECLARE @cmd NVARCHAR(max);
	
	IF @WhatIf=1 RAISERROR('WhatIf command activated, no actual command will be executed',0,1) WITH NOWAIT;
	IF @WhatIf=1 RAISERROR('WhatIf mode is defaut, use @whatif=0 for actual execution',0,1) WITH NOWAIT;

	WHILE EXISTS(SELECT * FROM #StatsTemp)
	BEGIN
		DELETE TOP (1) FROM #StatsTemp OUTPUT deleted.* INTO @t;
		SELECT @cmd = DROP_COMMAND + '; '+ CREATE_COMMAND FROM @t;
		IF @WhatIf=0 exec(@cmd);
		RAISERROR(@cmd,0,1) WITH NOWAIT;
	END
	RAISERROR('Done',0,1) WITH NOWAIT;
END
GO

Continue reading...