Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Covering index on persisted computed column being ignored Expand / Collapse
Author
Message
Posted Wednesday, November 14, 2012 10:14 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 5:23 AM
Points: 52, Visits: 174
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.
Post #1384753
Posted Wednesday, November 14, 2012 10:43 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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
Post #1384768
Posted Wednesday, November 14, 2012 1:35 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 5:23 AM
Points: 52, Visits: 174
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!
Post #1384840
Posted Thursday, November 15, 2012 3:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:18 AM
Points: 1,769, Visits: 2,800
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
Post #1385048
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse