repro.sql

USE [master];
GO

CREATE DATABASE CompileTestDB;
GO
ALTER DATABASE CompileTestDB SET QUERY_STORE = ON;
GO
ALTER DATABASE CompileTestDB SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL);
GO

USE CompileTestDB;
GO

CREATE TABLE dbo.Joe_is_a_cool_guy (
    ID1 BIGINT
);
 
INSERT INTO dbo.Joe_is_a_cool_guy WITH (TABLOCK)
SELECT
  q.RN
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q;
GO

CREATE OR ALTER PROCEDURE usp_Repro
AS
SELECT
  MAX(CASE WHEN ID1 = 1 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 2 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 3 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 4 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 5 THEN 1 ELSE 0 END) 
, MAX(CASE WHEN ID1 = 6 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 7 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 8 THEN 1 ELSE 0 END) 
, MAX(CASE WHEN ID1 = 9 THEN 1 ELSE 0 END)
, MAX(CASE WHEN ID1 = 10 THEN 1 ELSE 0 END)
FROM dbo.Joe_is_a_cool_guy
OPTION (MAXDOP 1);
GO

EXEC usp_Repro;
GO
DBCC FREEPROCCACHE;
GO
-- Find the query in Query Store and force the plan

-- Set up and start an XE session with quickstage_useplan_statistics

ALTER DATABASE SCOPED CONFIGURATION SET ACCELERATED_PLAN_FORCING = OFF;
GO
EXEC usp_Repro;
GO
DBCC FREEPROCCACHE;
GO
ALTER DATABASE SCOPED CONFIGURATION SET ACCELERATED_PLAN_FORCING = ON;
GO
EXEC usp_Repro;
DBCC FREEPROCCACHE;
GO

-- Look at the XE output to see how they compare