SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Covering index on persisted computed column being ignored


Covering index on persisted computed column being ignored

Author
Message
waxingsatirical
waxingsatirical
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 241
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.
Gazareth
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4002 Visits: 5820
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
waxingsatirical
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
Points: 100 Visits: 241
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
Gazareth
SSCarpal Tunnel
SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)SSCarpal Tunnel (4K reputation)

Group: General Forum Members
Points: 4002 Visits: 5820
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search