Covering index on persisted computed column being ignored

  • Hi All,

    I came across this rather odd bit of behaviour so I thought I would share it. Either I'm missing something or this is non-ideal behaviour from the optimiser.

    I am attempting to use a non-clustered covering index to speed up a large join I'm doing. The value I need from the table is a persisted computed column and I need it ordered for the join so the index is made to match the join conditions.

    Trouble is, whatever I do the optimiser decides to ignore my covering index! Grr. If I change the computed column to a regular column then the corresponding covering index is used, so I guess this is something to do with persisted columns and covering indices not playing well together.

    There is some SQL below to replicate the issue. Turn on Actual Execution plan and look at queries 3, 4 & 6. You'll see that when I force the use of the covering index it still does a RID Lookup. Cheeky so-and-so!

    -- CREATE the table for the test

    IF OBJECT_ID('PersistedTest') > 0

    BEGIN

    DROP TABLE PersistedTest

    END

    CREATE TABLE PersistedTest (id INT NOT NULL, col1 INT, col1Persisted AS col1 PERSISTED)

    -- Populate with a thousand (ish!) rows of dummy data

    ;

    WITH a AS (

    SELECT 0 AS num

    UNION ALL

    SELECT a.num + 1

    FROM a

    WHERE a.num < 1000

    )

    INSERT INTO PersistedTest

    SELECT CAST(CAST(NEWID() AS BINARY(4)) AS INT) AS id , 999 AS col1

    FROM a

    OPTION (MAXRECURSION 0)

    GO

    -- Create covering index over the persisted column

    CREATE UNIQUE NONCLUSTERED INDEX IX_PersistedTest_col1Persisted ON PersistedTest (id) INCLUDE (col1Persisted)

    GO

    -- SELECT statement that should use covering index but doesn't

    SELECT id

    , col1Persisted

    FROM PersistedTest

    ORDER BY id

    -- SELECT statement forced to use covering index but still doesn't!

    SELECT id

    , col1Persisted

    FROM PersistedTest WITH ( INDEX(IX_PersistedTest_col1Persisted))

    ORDER BY id

    -- Create covering index over regular column for control test

    CREATE UNIQUE NONCLUSTERED INDEX IX_PersistedTest_col1 ON PersistedTest (id) INCLUDE (col1)

    -- SELECT statement that uses covering index as expected3

    SELECT id

    , col1

    FROM PersistedTest

    ORDER BY id

    Any ideas? I don't really want to add code to populate the column manually.

  • I think SQL optimiser is second-guessing you because your computed column is equal to the 'real' one.

    See if you get the behaviour you expect when the column is defined as: col1Persisted AS col1*2 PERSISTED

    Cheers

    Gaz

  • Hi Gaz,

    The code I've posted is just some sample code to replicate the problem. When this happened in real life the persisted column was not just a repeat of another column in the table!

  • waxingsatirical (11/14/2012)


    Hi Gaz,

    The code I've posted is just some sample code to replicate the problem. When this happened in real life the persisted column was not just a repeat of another column in the table!

    Good, I'd hope not! 🙂

    Would need a closer approximation of your specific scenario to try and reproduce - if I make the change I mentioned above then the covering index is used in query 3 & 4. So I can't really tell what's happening in your case with the information given.

    Thanks

    Gaz

Viewing 4 posts - 1 through 3 (of 3 total)

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