Forcing an index spool

16 votes

1 answer

1960 views

sql-server-2012 t-sql execution-plan index-spool

                          I know its something that should be avoided for performance reasons, but am trying to show a condition where it appears as a demo on how to make sure it does not appear.

However, I end up with a missing index warning, yet the optimizer chooses not to create a temporary index.

The query I am using is

    SELECT 
        z.a
    FROM dbo.t5 AS z WITH(INDEX(0))
    WHERE 
        EXISTS 
        (
            SELECT y.a 
            FROM dbo.t4 AS y
            WHERE y.a = z.a
        )
    OPTION (MAXDOP 1);

Table schemas are:

    CREATE TABLE dbo.t4
    (
        a   integer NULL,
        b   varchar(1000) NULL,
        p   varchar(100) NULL
    );
    
    CREATE TABLE dbo.t5
    (
        a   integer NULL,
        b   varchar(1000) NULL
    );
    
    CREATE UNIQUE CLUSTERED INDEX c1 
    ON dbo.t5 (a);

Both tables have 10,000 rows, which you can simulate with:

    UPDATE STATISTICS dbo.t4 
    WITH 
        ROWCOUNT = 10000, 
        PAGECOUNT = 1000;
    
    UPDATE STATISTICS dbo.t5 
    WITH 
        ROWCOUNT = 10000,
        PAGECOUNT = 1000;

The query plan is:

It even tells me to create this index:

    USE [planoper];
    GO
    CREATE NONCLUSTERED INDEX []
    ON [dbo].[t4] ([a]);

Asked by Akash (1032 rep)

Dec 11, 2012, 09:57 AM
Last activity: Aug 31, 2020, 01:04 PM

Forcing an index spool

Related Questions