SP getting recompiled with "Statistics changed" reason even after disabling auto_stats for all tables involved

The recompile isn't being caused by a change in statistics. The reason code is misleading*.

SQL Server doesn't maintain statistics on sort worktables.

Quoting from Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005:

If a table or an indexed view T has no statistic on it, or none of the existing statistics on T are considered "interesting" during a query compilation, the following threshold-crossing test, based purely on T's cardinality, is still performed:

ABS(card(current) – card(snapshot))) >= RT

card(current) denotes the number of rows in T at present, and card(snapshot) denotes the row count when the query plan was last compiled.

In other words, the statement in the procedure is recompiling because a referenced table has passed the Recompilation Threshold associated with the total number of rows in the table. No individual statistic contributed to the decision.

You can avoid recompilation by adding a KEEPFIXED PLAN query hint to the procedure statement(s).

Forces the Query Optimizer not to recompile a query because of changes in statistics. Specifying KEEPFIXED PLAN makes sure that a query recompiles only if the schema of the underlying tables changes, or if sp_recompile runs against those tables.

With that approach, you can directly target the statement(s) you don't want to recompile. Disabling automatic statistics updates is no longer needed, either via STATISTICS_NORECOMPUTE or sp_autostats.

Repro

This script reproduces the issue on SQL Server 2022 with only a single query window needed.

Setup

DROP PROCEDURE IF EXISTS dbo.sp1;
DROP TABLE IF EXISTS dbo.test;
GO
CREATE TABLE dbo.Test
(
    a integer IDENTITY NOT NULL,
    b integer NULL,
    c integer NULL,

    PRIMARY KEY CLUSTERED (a)
        WITH (STATISTICS_NORECOMPUTE = ON)
);
GO
CREATE NONCLUSTERED INDEX idx
ON dbo.test (c)
WITH (STATISTICS_NORECOMPUTE = ON);
GO
CREATE PROCEDURE dbo.sp1
    @c integer
AS
SELECT 
    T.* 
FROM dbo.test AS T
WHERE 
    T.c = @c
--OPTION (KEEPFIXED PLAN)
;
GO
EXECUTE sys.sp_autostats
    @tblname = N'dbo.test',
    @flagc = 'off'; 

Test

SET NOCOUNT ON;
GO
DBCC TRACEON (205, 3604) 
    WITH NO_INFOMSGS;

EXECUTE dbo.sp1 @c = 1;

INSERT dbo.Test (b, c) VALUES (1, 1);

-- Recompile due to going from zero rows to one
EXECUTE dbo.sp1 @c = 1;

INSERT dbo.Test (b, c)
SELECT
    SV.number,
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 500;

-- Recompile due to cardinality change >= 500
EXECUTE dbo.sp1 @c = 1;

INSERT dbo.Test (b, c)
SELECT
    SV.number,
    SV.number
FROM master.dbo.spt_values AS SV
WHERE
    SV.[type] = N'P'
    AND SV.number BETWEEN 1 AND 600;

-- Recompile due to cardinality change >= 500 + 20%
EXECUTE dbo.sp1 @c = 1;

DBCC TRACEOFF (205, 3604) 
    WITH NO_INFOMSGS;
GO
DROP PROCEDURE dbo.sp1;
DROP TABLE dbo.test;

Output

Sample output without the KEEPFIXED PLAN hint:

Data related recompile(card change): Tbl Dbid: 8, Objid: 1652200936, 
current rowcount: 1, compile time rowcount: 0, threshold: 500 (small table)

Data related recompile(card change): Tbl Dbid: 8, Objid: 1652200936, 
current rowcount: 501, compile time rowcount: 1, threshold: 500 (small table)

Data related recompile(card change): Tbl Dbid: 8, Objid: 1652200936, 
current rowcount: 1101, compile time rowcount: 501, threshold: 600 (sublinear heuristic)

With the query hint, there is no output as no recompilations occur.

_{* There has been a change in statistical information available to the query optimizer, but not a change in a statistics object. It's the closest of the available reasons to choose.}