Covering index on persisted computed column being ignored

Question

Post reply

Covering index on persisted computed column being ignored

waxingsatirical

SSC Eights!

Points: 934
More actions
November 14, 2012 at 10:14 am

#266958

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.

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

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

Gazareth One Orange Chip Points: 27737 More actions · Answer 1

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

waxingsatirical SSC Eights! Points: 934 More actions · Answer 2

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!

Gazareth One Orange Chip Points: 27737 More actions · Answer 3

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