Avoid optional_spid Plan Cache Issues when Sharing Temp Tables

Introduction

There are many ways to share data between stored procedures as covered in detail by Erland Sommarskog in his article, How to Share Data between Stored Procedures. I expanded on the option of using T-SQL cursor variables in Sharing Data Between Procedures using a Cursor.

Sharing a temporary table

One popular choice is to use a shared temporary table, where the table is created in an outer procedure then used—and perhaps modified—in a nested procedure. This sounds like the perfect solution, but there are drawbacks.

1. The inner procedure makes use of a temporary table it did not create, which makes the code a little mysterious and hard to read.
2. The temporary table creation script must be duplicated and maintained in every outer procedure that calls the inner procedure.
3. Prior to SQL Server 2019, any statement in the inner procedure referencing the temporary table will recompile on every execution. The 2019 improvement was documented by Joe Sack in Reduced recompilations for workloads using temporary tables across multiple scopes.
4. Any procedure using a temporary table it did not create has optional_spid added to the plan’s cache key.

While all these matter, the plan cache and compilation impacts of optional_spid deserve special attention in this article.

The optional_spid Plan Cache Key

SQL Server adds the session id (spid) to the cached execution plan’s keys whenever a temporary table is used in a scope that did not also create the table. This means the cached plan can only be reused by the same session.

The word “scope” here is important. Each batch is its own scope, which includes modules like stored procedures. Less intuitively, executing dynamic SQL using EXEC or sp_executesql also creates a new scope.

Examples

Some quick examples might help clarify the concept:

1. Creating a temporary table in a stored procedure, then referencing it in dynamic SQL will cause the dynamic plan to be cached with an optional_spid cache key.

-- Stored procedure with dynamic SQL
DROP PROCEDURE IF EXISTS dbo.P;
GO
CREATE PROCEDURE dbo.P AS
    CREATE TABLE #T (i integer NULL);
    EXECUTE sys.sp_executesql
        N'
        -- The plan for this batch will have optional_spid
        -- because we are referencing a temporary table
        -- not created in the same scope
        INSERT #T (i) VALUES (@i);
        ',
        N'@i integer',
        @i = 123;
GO
EXECUTE dbo.P;
GO

2. Creating a temporary table in an ad hoc batch, then accessing it after a GO separator in SSMS will cause the second plan to have an optional_spid because the access is in a new batch scope.

-- Separate ad hoc batches
DROP TABLE IF EXISTS #T;
GO
CREATE TABLE #T (i integer NULL);
GO
-- The plan for this batch will have optional_spid
-- because we are referencing a temporary table
-- not created in the same scope
INSERT #T (i) VALUES (123);
GO

Plan cache query

You can use a DMV query like the following to see the optional_spid for the examples above:

SELECT
    ST.[text],
    CP.usecounts,
    PA.attribute,
    PA.[value],
    QP.query_plan
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_query_plan(CP.plan_handle) AS QP
CROSS APPLY sys.dm_exec_plan_attributes(CP.plan_handle) AS PA
WHERE
    ST.[text] LIKE N'%#T%'
    AND ST.[text] NOT LIKE N'%dm_exec_cached_plans%'
    AND PA.is_cache_key = 'true'
    AND PA.attribute = N'optional_spid'
    AND CP.cacheobjtype = N'Compiled Plan';
GO

Example output (when running the test from session #76):

Plans accessing a temporary table outside the creating scope have optional_spid as a cache key

Notice the two entries with optional_spid set to 76. A value of zero means the plan is not keyed to any particular session id.

Increased Compilations and Plan Cache Usage

Having a non-zero optional_spid cache key means that SQL Server will compile and cache a separate plan for each session (as identified by @@SPID).

Having a stored procedure that is called from 100 different sessions could result in 100 cached copies of essentially the same plan.

Notice that the executions do not need to be concurrent for this to happen. Each new @@SPID compiles and caches a fresh plan because none of the existing entries match on the optional_spid cache key.

If the plans are large—as stored procedures tend to be—the memory usage caused by this behaviour might be significant. Naturally, plan cache entries that are not reused will be aged out over time, but this is additional unnecessary work at best.

We should prefer to avoid these extra compilations and cache cluttering effects if we can.

Historical background

Somewhat ironically, the original motivation for adding optional_spid as a plan cache key was to avoid unnecessary recompilations. The underlying cause is that temporary tables are referenced by name in plans where the object is created in the same scope, but referenced by object id otherwise.

For more details, see the archived 2007 articles How Cache Lookups Work and Temporary Tables, Table Variables and Recompiles by the Microsoft SQL Programmability & API Development Team.

Though dated, these still explain the core mechanics better than anything else I am aware of.

Potential Solutions

Some people will choose to tolerate these compilation and caching effects for the convenience of being able to pass data to and from child procedures using a shared temporary table. Some possible justifications:

• The extra compilation activity may not be noticeable on an otherwise unstressed system.
• The plan cache often contains many useless or obsolete entries, so a bit of extra baggage is not a pressing concern.
• Having stored procedures referencing temporary tables without a local CREATE TABLE statement is not ideal, but this can be addressed through code comments and general development discipline.

These are all fair points so long as they’re made from an informed perspective. Others might look for an improved solution or even an alternative to sharing temporary tables.

Alternatives

Most alternatives to using shared temporary tables are listed with their respective advantages and disadvantages in Erland’s article.

For a scenario requiring table-valued input and output while also allowing modifications in the called procedure, you could consider a process-keyed permanent table or transport using structured strings (XML or JSON). I’m not going to cover those here because Erland does it very well.

I do want to offer you an improvement to the standard shared temporary table arrangement, and an alternative that builds on the cursor variable suggestion I presented last time.

Demo

Let’s start by looking at a concrete AdventureWorks example using a shared temporary table.

Original outer procedure

The outer procedure locates items with total inventory below their safety limit, and passes them to a child procedure using a temporary table:

-- Any version will do
USE AdventureWorks2022;
GO
-- Clear the plan cache
DBCC FREEPROCCACHE;
GO
CREATE OR ALTER PROCEDURE dbo.CheckProductStock
AS
SET NOCOUNT, XACT_ABORT ON;

-- Temporary table shared with subprocedure
CREATE TABLE #QualifiedProducts
(
    ProductID integer NOT NULL PRIMARY KEY,
    ProductName nvarchar(50)
        COLLATE DATABASE_DEFAULT NOT NULL,
    SafetyStockLevel smallint NOT NULL,
    TotalStock integer NOT NULL
);

-- Find products with low stock levels
INSERT #QualifiedProducts
(
    ProductID, 
    ProductName,
    SafetyStockLevel,
    TotalStock
)
SELECT
    P.ProductID, 
    P.[Name], 
    P.SafetyStockLevel,
    TotalStock = SUM(I.Quantity)
FROM Production.Product AS P
JOIN Production.ProductInventory AS I
    ON I.ProductID = P.ProductID
GROUP BY 
    P.ProductID, 
    P.[Name],
    P.SafetyStockLevel
HAVING
    P.SafetyStockLevel > SUM(I.Quantity);

-- Call the subprocedure
EXECUTE dbo.ReorderProducts;

-- Return products that couldn't be reordered
SELECT 
    ProductID, 
    ProductName, 
    SafetyStockLevel
FROM #QualifiedProducts;
GO

Original inner procedure

The inner procedure simulates an ordering process:

CREATE OR ALTER PROCEDURE dbo.ReorderProducts
AS
SET NOCOUNT, XACT_ABORT ON;

-- ================================
-- Code to reorder products omitted
-- ================================

-- Remove successfully reordered products
-- (done randomly for the demo)
DELETE #QualifiedProducts
WHERE RAND(CHECKSUM(ProductName, NEWID())) <= 8e-1;

-- Set stock level to zero for the remainder
UPDATE #QualifiedProducts
SET TotalStock = 0;
GO

Execution

Run the demo by executing the outer procedure:

EXECUTE dbo.CheckProductStock;

The output is random by design, but on one representative run I saw:

Sample results from the shared temporary table implementation

Cache effects

We can see the effect on the plan cache with the following DMV query:

SELECT
    ProcName = 
        CONCAT
        (
            QUOTENAME(OBJECT_SCHEMA_NAME(ST.objectid)),
            N'.',
            QUOTENAME(OBJECT_NAME(ST.objectid))
        ),
    CP.usecounts,
    PA.attribute,
    PA.[value],
    QP.query_plan
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_query_plan(CP.plan_handle) AS QP
CROSS APPLY sys.dm_exec_plan_attributes(CP.plan_handle) AS PA
WHERE
    ST.[text] LIKE N'%Product%'
    AND CP.objtype = N'Proc'
    AND CP.cacheobjtype = N'Compiled Plan'
    AND ST.[text] NOT LIKE N'%dm_exec_cached_plans%'
    AND PA.attribute = N'optional_spid'
    AND PA.is_cache_key = 'true';

DMV query results showing cached plans and their attributes

Notice that the inner dbo.ReorderProducts stored procedure has a non-zero optional_spid because it references a temporary table it did not create.

Running the outer procedure again from a different SSMS window confirms the plan is not shared among sessions:

DMV query results after a second run from a different session

There are now two cached plans for dbo.ReorderProducts, one for executions by session #62 and one for session #76.

This effect scales linearly; the more sessions you have calling the procedure, the more memory pressure on the plan cache.

Shared Temporary Table Improvement

An improvement suggested by Erland in his article is to add WITH RECOMPILE to the inner stored procedure. This will certainly avoid the optional_spid issue, but at the cost of recompiling the entire procedure each time it is called.

The cost of making this trade-off is higher on SQL Server 2019 and later. These versions incorporate the recompilation improvements I mentioned earlier. Without those improvements, every statement in the inner procedure that references the shared temporary table will recompile anyway, making WITH RECOMPILE less onerous.

A more precise improvement

We can do better than that with our knowledge of plan caching and the scoping rules involved with optional_spid.

The key insight is to localise the problem in new scopes at the beginning and end of the inner procedure. These new scopes can be created neatly with EXECUTE or sp_executesql.

At the start of the procedure, we copy the contents of the shared table into a new locally-created temporary table using a new scope. At the end of the procedure, we clean out the shared table and repopulate it from the local one, again in a new scope. The remainder of the inner procedure works exclusively with the local temporary table it created, avoiding the optional_spid issue.

These changes mean only the small new scopes will have plans with a non-zero optional_spid cache key. We might go further and decide these tiny plans don’t warrant caching (per session) at all.

Inner procedure implementation

The outer procedure remains as it was. Only the inner procedure needs to change:

-- Inner procedure
CREATE OR ALTER PROCEDURE dbo.ReorderProducts
AS
SET NOCOUNT, XACT_ABORT ON;

-- Our private copy of the shared table
CREATE TABLE #QualifiedProducts_Local
(
    ProductID integer NOT NULL PRIMARY KEY,
    ProductName nvarchar(50)
        COLLATE DATABASE_DEFAULT NOT NULL,
    SafetyStockLevel smallint NOT NULL,
    TotalStock integer NOT NULL
);

-- Copy the shared temporary table into our local one
EXECUTE
(
    -- The plan for this scope would have
    -- an optional_spid cache key.
    -- OPTION (RECOMPILE) prevents caching.
    N'
    INSERT #QualifiedProducts_Local
    SELECT * 
    FROM #QualifiedProducts
    OPTION (RECOMPILE);
    '
);

-- ================================
-- Code to reorder products omitted
-- ================================

-- Remove successfully reordered products
-- (done randomly for the demo)
DELETE #QualifiedProducts_Local
WHERE RAND(CHECKSUM(ProductName, NEWID())) <= 8e-1;

-- Set stock level to zero for the remainder
UPDATE #QualifiedProducts_Local
SET TotalStock = 0;

-- Update the shared temporary table
EXECUTE
(
    -- The plan for this scope would also have 
    -- an optional_spid cache key
    N'
    GOTO Start;
    -- Prevent this plan being cached
    CREATE CERTIFICATE DoNotCache FROM BINARY = 0x;

    Start:
    TRUNCATE TABLE #QualifiedProducts;
    INSERT #QualifiedProducts
    SELECT * FROM #QualifiedProducts_Local;
    '
);
GO

Preventing plan caching

The only point requiring expansion is the approach taken to prevent plan caching in the copy out at the end of the inner procedure.

I could have used an unrestricted DELETE #QualifiedProducts with OPTION (RECOMPILE), but this feels like a shame because temporary tables support the more efficient TRUNCATE TABLE. Unfortunately, the truncate statement does not support query hints. EXECUTE used with a string does not allow the WITH RECOMPILE option either.

To address this, I borrowed a trick from my article, Simple Parameterization and Trivial Plans.

Any batch containing text considered to be security sensitive is not cached. That text need not be executed; it just has to be present. I used a CREATE CERTIFICATE statement here and skipped it using GOTO and a label. It is a bit of a hack, but it does work—the plan is not cached.

Using trace flag 253 (which prevents all ad hoc plan caching) is another option, but that requires sysadmin permissions and is not fully documented.

If you prefer a more explicit solution, you could create a separate procedure for the copy out, adding WITH RECOMPILE to its definition or at the call site.

Plan cache effects

We can use the following DMV query to see the effect on the plan cache:

SELECT
    ST.[text],
    CP.objtype,
    CP.usecounts,
    PA.attribute,
    PA.[value],
    QP.query_plan
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_query_plan(CP.plan_handle) AS QP
CROSS APPLY sys.dm_exec_plan_attributes(CP.plan_handle) AS PA
WHERE
    ST.[text] LIKE N'%QualifiedProducts%'
    AND CP.cacheobjtype = N'Compiled Plan'
    AND ST.[text] NOT LIKE N'%dm_exec_cached_plans%'
    AND PA.attribute = N'optional_spid'
    AND PA.is_cache_key = 'true';

Without the OPTION (RECOMPILE) on the copy in scope’s query and CREATE CERTIFICATE in the copy out scope, the results show optional_spid is limited to those plans:

Plan cache entries without caching preventing in the new scopes

Importantly, the inner procedure’s plan now has a zero optional_spid, meaning it can be shared with any session. In normal situations, the procedure’s cached plan will be very much larger than the new scopes. Transferring the optional_spid issue to the new small scopes could be a big win for cache efficiency.

With the cache-preventing statements inserted in the new scopes, those entries simply disappear from the output.

Plans for the copy in and copy out will be compiled on every execution of the inner procedure, but these plans are low value and quick to compile. Moreover, the plan cache will not be littered with per-session copies of the scoped plans.

Considerations

There are obviously trade-offs to be made here. The extra copying work should not be a concern, unless the data sets are very large.

For scenarios where you need to return a result set with a different shape to the input, the outer procedure could provide two shared temporary tables. The inner procedure would need shadow copies of both to avoid the optional_spid issue.

The copy in scope would use one table and the copy out scope would use the other. With the TRUNCATE TABLE no longer needed, the copy out could also use OPTION (RECOMPILE) to prevent plan caching instead of the CREATE CERTIFICATE hack.

Cursor Implementation

Let’s now turn to rewriting the original procedure to not share a temporary table at all.

The key to this alternative is passing the data set around in a cursor worktable instead of a temporary table. Cursor worktables do not add optional_spid to the plan cache key when referenced in a different scope.

Cursor type

For this to solve the optional_spid problem completely, we need to ensure the inner procedure never accesses a temporary table created in a different scope, even via the cursor.

A dynamic or keyset cursor built on an outer procedure temporary table would still need to access the table during a FETCH operation in the inner procedure. This means these cursor types cannot help us avoid the optional_spid issue.

That leaves us with using a static cursor, which fully populates its worktable during opening. No underlying table access is required during fetching, so the optional_spid problem does not arise.

Cursor Type	Accesses Base Table?	Avoids optional_spid?
Dynamic	Yes	No
Keyset	Yes	No
Static	No	Yes

The read only issue

Unfortunately, a static cursor is always read only, which puts it at odds with our requirement to modify the data set in the inner procedure.

While true, we wouldn’t want to be modifying data through the cursor anyway, as this would cause updates to the AdventureWorks base tables.

My workaround for this is to:

1. Populate an inner procedure temporary table from the static cursor’s worktable.

   This is the equivalent of the copy in operation when using the improved shared temporary table implementation.

2. Modify the temporary object within the inner procedure as needed.

3. Return a new static cursor over the modified data to the calling procedure.

   This is the equivalent of the copy out.

All this is certainly possible to do using T-SQL cursor variables as I described last time. For variety, this time I am going to assume the inner procedure is a set-based operation and does not require a T-SQL cursor. This means we can use an API cursor and do away with row-by-row processing altogether.

Cursor outer procedure

The new implementation uses sp_cursoropen to open a static, read-only API cursor over the qualification query. No temporary table is needed.

This call returns an integer cursor handle, which is passed to the inner procedure as a parameter. The parameter is marked as OUTPUT so a new cursor handle (over the modified data set) can be returned to us by the inner procedure.

API cursors are always global, so the outer procedure also gains some rudimentary error handling code to ensure the cursor is always closed before the procedure returns:

-- Outer procedure
CREATE OR ALTER PROCEDURE dbo.CheckProductStock
AS
SET NOCOUNT, XACT_ABORT ON;

BEGIN TRY
    DECLARE @APIcursor integer;

    -- Suppress API cursor result set
    DECLARE @BitBucket table
    (
        ProductID integer NOT NULL,
        ProductName nvarchar(50)
            COLLATE DATABASE_DEFAULT NOT NULL,
        SafetyStockLevel smallint NOT NULL,
        TotalStock integer NOT NULL
    );

    INSERT TOP (0) 
        @BitBucket
    EXECUTE sys.sp_cursoropen
        @APIcursor OUTPUT,
        -- Find products with low stock levels
        N'
        SELECT
            P.ProductID, 
            P.[Name], 
            P.SafetyStockLevel,
            TotalStock = SUM(I.Quantity)
        FROM Production.Product AS P
        JOIN Production.ProductInventory AS I
            ON I.ProductID = P.ProductID
        GROUP BY 
            P.ProductID, 
            P.[Name],
            P.SafetyStockLevel
        HAVING
            P.SafetyStockLevel > SUM(I.Quantity);
        ', 
        -- STATIC
        8,
        -- READ_ONLY
        1;

    -- Call the subprocedure
    -- Passing in the API cursor handle (integer)
    EXECUTE dbo.ReorderProducts
        @APIcursor OUTPUT;

    -- Show all the outstanding products
    EXECUTE sys.sp_cursorfetch
        @APIcursor,
        1, -- FIRST
        0, -- rownum (unused in this call)
        2147483647; -- nrows

    EXECUTE sys.sp_cursorclose @APIcursor;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

    -- Ensure API cursor is closed
    IF EXISTS
    (
        SELECT * 
        FROM sys.dm_exec_cursors(@@SPID)
        WHERE cursor_id = @APIcursor
    )
    BEGIN
        EXECUTE sys.sp_cursorclose @APIcursor;
    END;

    THROW;
END CATCH;
GO

Cursor inner procedure

The inner procedure gets its working set via the passed-in API cursor handle, providing access to the cursor’s worktable.

A temporary table is created and used* only in the inner procedure’s scope, so no optional_spid is needed.

After populating the temporary object (copy in) using a single sp_cursorfetch call, the inner procedure closes the API cursor to release resources.

When the necessary deletions and updates are completed, the procedure then opens a new static, read-only API cursor over the modified temporary table (copy out). The new API cursor handle is returned to the caller in the same OUTPUT parameter used to receive the first handle.

The temporary table will go out of scope at the end of the inner procedure, as usual. This is not a problem because the returned static API cursor has already saved the contents to its worktable. The cursor’s worktable is not affected by the temporary table’s removal.

As a side benefit, note that the cursor implementation allows the returned result to easily have a different schema from the input set.

Inner procedure code

-- Inner procedure
CREATE OR ALTER PROCEDURE dbo.ReorderProducts
    @APIcursor integer OUTPUT
AS
SET NOCOUNT, XACT_ABORT ON;

-- Temporary table now created where it is used
CREATE TABLE #QualifiedProducts
(
    ProductID integer NOT NULL PRIMARY KEY,
    ProductName nvarchar(50)
        COLLATE DATABASE_DEFAULT NOT NULL,
    SafetyStockLevel smallint NOT NULL,
    TotalStock integer NOT NULL
);

-- Add all rows from the cursor in a single call
INSERT #QualifiedProducts
EXECUTE sys.sp_cursorfetch
    @APIcursor,
    1, -- FIRST
    0, -- rownum (unused in this call)
    2147483647; -- nrows

-- Release API cursor resources
EXECUTE sys.sp_cursorclose @APIcursor;

-- ================================
-- Code to reorder products omitted
-- ================================

-- Remove successfully reordered products
-- (done randomly for the demo)
DELETE #QualifiedProducts
WHERE RAND(CHECKSUM(ProductName, NEWID())) <= 8e-1;

-- Set stock level to zero for the remainder
UPDATE #QualifiedProducts
SET TotalStock = 0;

-- Return products that couldn't be reordered

-- Suppress API cursor result set
DECLARE @BitBucket table
(
    ProductID integer NOT NULL,
    ProductName nvarchar(50)
        COLLATE DATABASE_DEFAULT NOT NULL,
    SafetyStockLevel smallint NOT NULL
);

INSERT TOP (0)
    @BitBucket
-- Store outstanding products in a new API cursor
-- New cursor handle returned by this procedure's OUTPUT param
-- Omit the TotalStock column since it is all zero
-- Shows the return can have a different schema
EXECUTE sys.sp_cursoropen
    @APIcursor OUTPUT,
    N'
    SELECT
        ProductID, 
        ProductName, 
        SafetyStockLevel
    FROM #QualifiedProducts
    OPTION 
        (RECOMPILE);
    ', 
    -- STATIC
    8,
    -- READ_ONLY
    1;
GO

API cursor opening scope

The sp_cursoropen statement contains an OPTION (RECOMPILE) hint on its embedded query. This is only required on the output cursor query.

Recompile hint solves the optional_spid issue

Without the hint, the cursor statement acquires an optional_spid because the #QualifiedProducts table is accessed outside the scope in which it was created:

Inner procedure’s cursor opening plan with a non-zero optional_spid

The execution plan for the problem statement is available by clicking the query_plan link in SSMS:

Cached API cursor execution plan

This confirms the query was issued by the sp_cursoropen call and #QualifiedProducts was accessed outside its creating scope.

API Cursor Results

Running the outer procedure produces a new set of random results:

EXECUTE dbo.CheckProductStock;

Results of the dbo.CheckProductStock procedure execution

The plan cache DMV query confirms that optional_spid is zero for both procedures:

The API cursor implementation does not set optional_spid

Running the outer procedure again from a different session increments both existing plans’ usecounts, confirming plan reuse:

Both stored procedure plans successfully reused

Success

We have achieved our objective of sharing a modifiable data set between two stored procedures using only a single cursor handle parameter, and without introducing an optional_spid issue.

Final Thoughts

Be aware of the effect on the plan cache of accessing a temporary table outside the scope in which it is created. It is not always immediately obvious what constitutes a ‘scope’, so careful thought is sometimes required.

The improved shared temporary table method is relatively easy to implement, though it does require some extra typing compared with the basic form. Copying the data in and out is not for free, but may be worth it for the compilation reduction and more efficient plan cache usage.

The API cursor implementation is mostly provided as an interesting curiousity. It does offer a certain elegance in the way it reuses a single parameter for both input and output. Being able to return a different result set shape easily is also a plus. Finally, it makes creative use of the cursor’s worktable to pass data around.

Thanks for reading.