Post reply

Running a Parameter-Sensitive Stored Procedure on a Secondary Replica

  • June 12, 2026 at 9:38 pm

    #4864369

    Hello , I would like to run a stored procedure on a secondary replica in an AlwaysOn Availability Group to offload read-only workload from the primary. However, this procedure currently suffers from parameter sniffing, and we sometimes need to recompile it to restore acceptable performance.

    I would like to know:

    What are the risks of running this stored procedure on the secondary replica, given that it may require recompilation?

    Is it possible to recompile the procedure on the secondary replica, considering that it is read-only?

    How can we detect performance regressions, especially since Query Store is read-only on secondary replicas in SQL Server 2022?

  • June 13, 2026 at 2:09 pm

    #4864663

    In SQL Server 2022 they added a bunch of features which theoretically could be employed, like PSP "Parameter Sensitive Plan" optimization which stores more than 1 plan for a proc.  Imo these features were a mistake and are even worse than syntax sugar, it's "perfume for a pig".  Very often the real issue is unstable parameters or internal branching is a bad design pattern.  So before looking into PSP and/or frequently recompiling the proc maybe consider splitting the one proc into multiple procs.  Why not something like this?

    CREATE OR ALTER PROCEDURE dbo.MainProc
        @SomeSwitch INT,
        @PayloadID INT
    AS
    BEGIN
        SET NOCOUNT ON;

        -- logic separates execution paths
        IF @SomeSwitch = 1
            EXEC dbo.SubProc_One @PayloadID;
        ELSE IF @SomeSwitch = 2
            EXEC dbo.SubProc_Two @PayloadID;
    END;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply