Filtered index with IS NULL predicate

Question

Filtered index with IS NULL predicate

Viewing 15 posts - 16 through 30 (of 33 total)

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

baoshan sheng SSChasing Mays Points: 636 More actions · Answer 1

GilaMonster (5/5/2009)
Jeff Moden (5/4/2009)
I'm not sure how creating a filtered index on the Department column can be considered as a covering index on this query... it's still just an index on the Department column even though there's a filter on a column that happens to be used in the query.
From Books Online: (http://msdn.microsoft.com/en-us/library/cc280372.aspx)
In some cases, a filtered index covers the query without including the columns in the filtered index expression as key or included columns in the filtered index definition. The following guidelines explain when a column in the filtered index expression should be a key or included column in the filtered index definition.
A column in the filtered index expression does not need to be a key or included column in the filtered index definition if the filtered index expression is equivalent to the query predicate and the query does not return the column in the filtered index expression with the query results. For example, FIBillOfMaterialsWithEndDate covers the following query because the query predicate is equivalent to the filter expression, and EndDate is not returned with the query results. FIBillOfMaterialsWithEndDate does not need EndDate as a key or included column in the filtered index definition.
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials (ComponentID, StartDate)
WHERE EndDate IS NOT NULL;
SELECT ComponentID, StartDate FROM Production.BillOfMaterials
WHERE EndDate IS NOT NULL;
GO
That is precisely the case here. The managerID is only used in the where clause and the filtered index expression is equivalent to the query predicate.

Predicate IS NOT NULL works fine, but IS NULL will generate a inner join and key lookup alone.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Thanks for the info folks. Learned something new which is always a pleasure. I was just going for the "old doctor" trick. You know the one... go to the doctor and say "When I hold my arm over my head like this, it hurts real bad". Doctor always says, "So don't do that and it won't hurt." 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

No worries Jeff. By digging up proof for you I learned some stuff about filtered indexes too. 😀

Sheng, I chatted with some friends about this. It appears (though I haven't tested) that on SP1 the optimiser does pick the filtered indexes without the hint, but there's still that odd key lookup on the IS NULL version. We've concluded that it's either a bug, a limitation that should be documented, or the documentation is wrong.

I'll be logging this on Connect this week.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

baoshan sheng SSChasing Mays Points: 636 More actions · Answer 4

Thank you, everyone.

A key lookup only output the key column is fairly odd. Without asking the developers of Microsoft, I've no idea on this:-) But if we noticed the something happened is not what we want, we can hack it by using the "old doctor hack":-D

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 5

It must be a bug.

I've tested in on SQL2008 SP1. (10.0.2531.0 (Intel X86) )

It gets even worse if you explicitly use the ID column in the filtered index definition.

(tested as well usage in the key-part as usage as include column)

Why ? Because in that case one should be 100% sure it will be a index only query (not relying on the auto include of a unique(ified) clustering index key).

The execution plans still shows a key lookup on the clustering index.

edited: Thank you Gail for taking this to Connect. (please provide the url)

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

ALZDBA (5/6/2009)
It must be a bug.
I've tested in on SQL2008 SP1. (10.0.2531.0 (Intel X86) )
It gets even worse if you explicitly use the ID column in the filtered index definition.
(tested as well usage in the key-part as usage as include column)

Thank you. That was the one thing that I still wanted to test.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=454744

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 8

Thank you for the feedback.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

christian_t Hall of Fame Points: 3573 More actions · Answer 9

I know this post has not been updated for a while,

but wanted to know if there was an hotfix/workaround (not the docy-style :-))

I created a few of the IS NULL filtered indexes and while putting together a sql the execution plan would only take the indexes if I force it with hints.

However your descripted phenomenon only happend to me if I did NOT put the column which is "IS NULL" into the included columns of the filtered index.

Additionally I must say, that the filtered colum is not part of the resultset, it is just in the join (ON)-clause.

So, am I just lucky or has there been an fix and if it was fixed, why does the optimizer not pick my index without hint?

Microsoft SQL Server Enterprise Edition SP1 (64-bit) (10.0.2531.0)

Windows Server 2008 SP2 (64-bit) (6002)

Cheers,

Mitch

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 10

It was supposed to have been fixed in SP2, but honestly, I haven't even thought about this issue for a while, I haven't tested it.

Putting the column in the include would remove the (incorrect) use of a key lookup, as the column would be in the index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

AlBran LVP SSC Eights! Points: 857 More actions · Answer 11

I'm on SQL 2008 R2 (10.50.1600) and I just created a filtered index with "MyColumn IS NULL" as the filter and my queries appear to be using the index when they should, so I think it's fixed.

Paul White SSC Guru Points: 150457 More actions · Answer 12

Alex Bransky (11/7/2011)
I'm on SQL 2008 R2 (10.50.1600) and I just created a filtered index with "MyColumn IS NULL" as the filter and my queries appear to be using the index when they should, so I think it's fixed.

The bug is the unnecessary key lookup with a predicate that matches the filter condition.

This limitation still exists on 10.50.2772 and 11.0.1440 (2012 CTP 3).

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 13

SQL Kiwi (11/7/2011)
Alex Bransky (11/7/2011)
I'm on SQL 2008 R2 (10.50.1600) and I just created a filtered index with "MyColumn IS NULL" as the filter and my queries appear to be using the index when they should, so I think it's fixed.
The bug is the unnecessary key lookup with a predicate that matches the filter condition.
This limitation still exists on 10.50.2772 and 11.0.1440 (2012 CTP 3).

So much for 'fixed in the next version of SQL'... 🙁

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Paul White SSC Guru Points: 150457 More actions · Answer 14

GilaMonster (11/8/2011)
So much for 'fixed in the next version of SQL'... 🙁

The comments on the Connect item are contradictory, but the most recent one says:

"This is now an active DCR for a future release of SQL Server."

...which sounds a bit like "We'll get to it one day, maybe". :ermm:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 15

SQL Kiwi (11/8/2011)
GilaMonster (11/8/2011)
So much for 'fixed in the next version of SQL'... 🙁
The comments on the Connect item are contradictory, but the most recent one says:
"This is now an active DCR for a future release of SQL Server."
...which sounds a bit like "We'll get to it one day, maybe". :ermm:

I got a mail direct from the dev team that said 'fixed in the next version'. Was sent just before they closed it 'Won't fix'. Time to reopen the item I think.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass