T-SQL query takes 13 seconds to run in SQL Server Manager but 20 minutes as stored procedure

The first check is to run the stored procedure’s slow statement with OPTION (RECOMPILE) using the same parameter values; if runtime drops from 20 minutes to roughly 13 seconds, the procedure is reusing a parameter-sensitive cached plan.

Although the SQL text looks identical, the tests are not optimizer-equivalent. Procedure parameters are sniffed when the procedure compiles, while locally declared variables generally use density-based estimates because their values cannot be sniffed. Those different estimates can produce very different plans.^1^

1. Test for a parameter-sensitive cached plan

Add OPTION (RECOMPILE) temporarily to the slow statement inside a test copy of the procedure:

ALTER PROCEDURE dbo.YourProcedure
    @CustomerId int
AS
BEGIN
    SELECT  ...
    FROM    ...
    WHERE   CustomerId = @CustomerId
    OPTION (RECOMPILE);
END;

Execute it with exactly the same values that previously took 20 minutes. OPTION (RECOMPILE) builds a plan using the parameter values present at that execution, rather than reusing the existing statement plan.^1^

• Now fast: this strongly indicates a parameter-sensitive plan problem.
• Still slow: continue with the plan and session comparisons below.

Recompiling is also a possible permanent solution, but it adds compilation work on every execution. It is often suitable for an expensive statement whose parameter values produce substantially different row counts, but less attractive for a frequently executed lightweight query.

2. Compare the actual plans—not only the SQL text

In SSMS:

1. Select Query → Include Actual Execution Plan.
2. Execute the procedure with the slow parameter values.
3. Execute the local-variable batch.
4. Open the Execution Plan tab and save both plans.

Actual plans include runtime row counts, resource metrics, and warnings.^2^

Compare:

• Estimated rows versus actual rows, especially at the first large divergence.
• Index seek versus table/index scan.
• Join type and join order.
• Sort or hash spills.
• Memory grants.
• Implicit conversions.
• Parallel versus serial execution.

A large estimated/actual row discrepancy in the procedure plan is typical evidence of a plan compiled for an unrepresentative parameter.

Also measure server execution rather than result rendering:

SET STATISTICS IO, TIME ON;

EXEC dbo.YourProcedure @CustomerId = 123;

SET STATISTICS IO, TIME OFF;

3. Choose a targeted correction

If recompilation proves the issue, supported choices include:^3^

• OPTION (RECOMPILE) for the affected statement.
• OPTIMIZE FOR (@Parameter = value) when one representative value produces a good plan for most calls.
• OPTIMIZE FOR UNKNOWN when an average-density plan is preferable.
• Local variables inside the procedure, which also produce general density-based estimates.
• USE HINT('DISABLE_PARAMETER_SNIFFING').

Do not select OPTIMIZE FOR UNKNOWN or local-variable masking merely because the standalone local-variable test was fast once. Test representative small, medium, and large parameter sets; the generalized plan can perform poorly for skewed data.

If this is SQL Server 2022 and the database uses compatibility level 160, Parameter Sensitive Plan optimization can retain multiple plan variants for different parameter ranges. Query Store is recommended for visibility into those variants.^4^

4. Check connection settings when executions use different clients

If the 20-minute procedure is normally invoked by an application while the 13-second batch is run in SSMS, compare connection SET options. Plan-affecting options can create separate cached plans; the common difference is ARITHABORT, which defaults to ON in SSMS and often OFF in applications.^5^

Check the current SSMS setting with:

DECLARE @ARITHABORT varchar(3) = 'OFF';

IF (64 & @@OPTIONS) = 64
    SET @ARITHABORT = 'ON';

SELECT @ARITHABORT AS ARITHABORT;

ARITHABORT is a plan-cache key even where it has no functional effect, so match the application’s settings in SSMS before comparing plans.^6^

If both executions occur in the same SSMS window, prioritize the parameter-sensitive plan and actual-plan comparison rather than changing session settings.

References

1. Query processing architecture guide
2. Display an actual execution plan
3. Post-migration validation and optimization guide
4. Parameter Sensitive Plan optimization
5. Troubleshoot query performance difference between database application and SSMS
6. SET ARITHABORT (Transact-SQL)