Why One Query Has Two Cached Plans: SET Options and plan_attributes

The same procedure can behave differently in SSMS and an application. Two cached plans can preserve different compilation contexts even when the procedure text matches.

Separate Context From Cause

SQL Server uses more than statement text when identifying reusable plans. Database identity, execution context, and certain SET options affect cache matching. A text match therefore does not establish a shared compiled plan.

I compare cache attributes before concluding that SSMS reproduces an application call. I also compare parameter values and compiled estimates. Matching context makes a test relevant, while the plan explains the execution behavior.

ARITHABORT receives attention because client defaults differ. Its cache attribute is a useful clue, but changing it does not diagnose a bad estimate. A new cache entry can appear to fix a problem through different compilation.

The fast query window has not earned a special exemption from parameter sensitivity. It can compile a poor plan under a different input too. Investigate the input distribution rather than trusting the client label.

Use a disposable database for this demonstration. Avoid clearing the whole server cache to manufacture a comparison. The example creates its own table and procedure so the inspection can remain narrowly scoped.

Create a Skewed Fixture

The fixture intentionally gives one customer substantially more orders than another. Its values are generated inputs, not observed production counts. A nonclustered customer index permits different lookup and scan choices.

CREATE TABLE dbo.PlanContextDemo
(
    OrderID int IDENTITY(1,1) NOT NULL
        CONSTRAINT PK_PlanContextDemo PRIMARY KEY,
    CustomerID int NOT NULL,
    Amount decimal(12,2) NOT NULL,
    Padding char(200) NOT NULL
);
WITH Digits AS
(
    SELECT n FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS v(n)
)
INSERT dbo.PlanContextDemo(CustomerID, Amount, Padding)
SELECT CASE WHEN a.n = 0 AND b.n = 0 AND c.n = 0 THEN 2 ELSE 1 END,
       CONVERT(decimal(12,2), 10), REPLICATE('x', 200)
FROM Digits AS a CROSS JOIN Digits AS b CROSS JOIN Digits AS c;
CREATE INDEX IX_PlanContextDemo_Customer
    ON dbo.PlanContextDemo(CustomerID);
UPDATE STATISTICS dbo.PlanContextDemo WITH FULLSCAN;
GO
CREATE PROCEDURE dbo.PlanContextDemo_GetOrders
    @CustomerID int
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderID, Amount, Padding
    FROM dbo.PlanContextDemo
    WHERE CustomerID = @CustomerID;
END;
GO

The procedure is created in a separate batch, followed by GO before test calls. Its returned columns require information outside the customer index. The optimizer still chooses the plan according to its estimates and available alternatives.

Run the calls with actual plan collection enabled in SSMS. A particular plan shape is not guaranteed by this small fixture. Inspect your returned plan rather than claiming the demonstration must produce a scan or seek.

On compatibility levels with parameter sensitive plan optimization, additional dispatcher or variant plans can complicate the comparison. Identify those features in the XML if present. Do not mistake every extra plan for a SET option split.

Compile Two Cached Plans Under Different Options

Execute under ARITHABORT ON with one value and OFF with another. Save the original session setting so the demonstration can restore it. Keep the actual input values alongside the captured plans.

DECLARE @WasArithAbortOn bit =
    CASE WHEN @@OPTIONS & 64 = 64 THEN 1 ELSE 0 END;
SET ARITHABORT ON;
EXEC dbo.PlanContextDemo_GetOrders @CustomerID = 2;
SET ARITHABORT OFF;
EXEC dbo.PlanContextDemo_GetOrders @CustomerID = 1;
IF @WasArithAbortOn = 1 SET ARITHABORT ON;
ELSE SET ARITHABORT OFF;

The bit in @@OPTIONS is a session-options encoding. It is different from the bit used by the plan attribute set_options. Reusing one bit table for both values produces an incorrect diagnosis.

With ANSI_WARNINGS enabled at supported compatibility levels, arithmetic behavior has additional documented interactions. The cache attribute alone is not a complete arithmetic semantics report. Preserve the full client settings when reproducing a call.

A cancellation before restoration leaves the query window's setting changed. Check its current options afterward if the demonstration is interrupted. Session settings affect later work on that connection.

Find and Decode the Two Cached Plans

Inspect cached procedure plans and their set_options attribute. The SQL text function supplies the procedure identity, while the attributes supply the context. Filter both database and object identity to avoid matching another database's object.

SELECT cp.plan_handle, cp.usecounts, cp.size_in_bytes,
       CONVERT(bigint, pa.value) AS PlanSetOptions,
       st.[text] AS ProcedureText
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
WHERE st.dbid = DB_ID()
  AND st.objectid = OBJECT_ID(N'dbo.PlanContextDemo_GetOrders')
  AND pa.attribute = N'set_options';

SELECT cp.plan_handle, bits.OptionName, bits.BitValue,
       CASE WHEN CONVERT(bigint, pa.value) & bits.BitValue <> 0
            THEN 1 ELSE 0 END AS IsSet
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
CROSS JOIN (VALUES
    (N'ANSI_PADDING', CONVERT(bigint, 1)),
    (N'CONCAT_NULL_YIELDS_NULL', CONVERT(bigint, 8)),
    (N'ANSI_WARNINGS', CONVERT(bigint, 16)),
    (N'ANSI_NULLS', CONVERT(bigint, 32)),
    (N'QUOTED_IDENTIFIER', CONVERT(bigint, 64)),
    (N'ARITH_ABORT', CONVERT(bigint, 4096)),
    (N'NUMERIC_ROUNDABORT', CONVERT(bigint, 8192))
) AS bits(OptionName, BitValue)
WHERE st.dbid = DB_ID()
  AND st.objectid = OBJECT_ID(N'dbo.PlanContextDemo_GetOrders')
  AND pa.attribute = N'set_options'
ORDER BY cp.plan_handle, bits.BitValue;

Use the version-appropriate server diagnostic permission to inspect these views. The decoder lists common relevant bits rather than every possible attribute bit. Read the complete documented set_options list when other differences remain.

On my SQL Server 2025 test instance, the first query returned two rows for the procedure. The decoder showed ARITH_ABORT set on one handle and clear on the other. Every other decoded flag matched.

Compare which flags differ between handles rather than staring at two decimal totals. Two cached plans with an ARITH_ABORT difference have different recorded compilation contexts. That observation still does not establish why one execution runs slower.

Reproduce the Application Call

Capture the application's relevant SET options, parameter types, and actual parameter value. Match them in a dedicated SSMS window. Also match database context and the procedure entry point used by the application.

Parameter types matter because implicit conversions affect estimates and access paths. A text value sent with the wrong type creates a different problem from cache context. Do not merge those explanations into one ARITHABORT story.

Which value compiled the slow plan, and which value now reuses it? Read the plan's parameter information and compare estimated rows with actual rows. A shared procedure name does not answer that question.

Compare Two Cached Plans on More Than One Attribute

Two cached plans can differ because of context attributes beyond the decoded flags. Inspect dbid, user_id, language_id, date_format, and date_first when the first comparison is incomplete. Keep is_cache_key beside each attribute during that inspection.

SELECT cp.plan_handle, pa.attribute, pa.value, pa.is_cache_key
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
WHERE st.dbid = DB_ID()
  AND st.objectid = OBJECT_ID(N'dbo.PlanContextDemo_GetOrders')
  AND pa.is_cache_key = 1
ORDER BY cp.plan_handle, pa.attribute;

A plan handle is transient and disappears when its cached entry leaves. Save the attributes with the captured plan while the issue is present. Querying tomorrow's cache cannot reliably reconstruct yesterday's context split.

Usecounts also needs a careful reading. It reports cache lookups for the cached object rather than a complete application transaction history. Do not use it alone to decide which client performed every execution.

If both contexts now look fast, check whether either entry was recompiled. A refreshed plan can erase the condition you intended to investigate. Preserve Query Store history when available and align it with the application report.

Do not force a production cache reset to recover a convenient demonstration. Reproduce the relevant input and settings on a suitable test copy. That lets you explore skew while keeping the live workload's compilation behavior intact.

Fix the Evidence-Based Problem

Parameter sniffing lets compilation use the current parameter value. Skew makes a plan suitable for one value unsuitable for another. Review indexing, query shape, and supported parameter sensitivity features against that evidence.

Changing an application SET option creates another context but does not remove the data skew. The apparent improvement can disappear after another compilation. Select a remedy for the estimate or access-path problem you identified.

Keep the context comparison as part of the investigation record. Record the matching settings, inputs, and observed plans together. A reproducible slow call provides a useful starting point for a deliberate fix.

Related reading on this blog: Same Result Same Query Plan: Different Entry in Cache and Setting ARITHABORT ON for All Connecting .Net Applications.

A matching procedure name is not a matching execution context, it is one part of the cache identity.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.