November 15, 2011 at 10:12 am
so the query would have to betweens, one that encapsulates my parameters and one that defines the boundaries..? Hmm..no, haven't tried that. Seems like it would be a 'clever' fix but also seems like it would might be something SS would barf at. I'll give it a shot.
November 15, 2011 at 10:15 am
Plan attached
November 15, 2011 at 10:22 am
You have an unmatched index warning (SSMS Denali).
http://technet.microsoft.com/en-us/library/cc280372.aspx
I haven't read the whole thing but this seems right on target.
November 15, 2011 at 10:23 am
Remi, Filtered Indexes run on a similar component to the partition choices of what indexes to use, aligned or non-aligned, as far as I know.
If the query isn't sure it's going to land in the filter its a whole lot more likely to be outright ignored. A parameter can't ensure that. At least for partitions it's sure it'll use some portion of the aligned index.
I've seen this before when I was goofing around with Filtered Indexes and decided they're rather particular about when I should put them into play. I almost treat them as a mini-partition. Unless I'm sure I'm going to direct some queries into those sections permanently and unerringly, I don't bother with them.
As mentioned above, the redundant where clause should correct the selectivity. I'm really curious about the error that was received though.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2011 at 10:25 am
Evil Kraig F (11/15/2011)
Remi, Filtered Indexes run on a similar component to the partition choices of what indexes to use, aligned or non-aligned, as far as I know.If the query isn't sure it's going to land in the filter its a whole lot more likely to be outright ignored. A parameter can't ensure that. At least for partitions it's sure it'll use some portion of the aligned index.
I've seen this before when I was goofing around with Filtered Indexes and decided they're rather particular about when I should put them into play. I almost treat them as a mini-partition. Unless I'm sure I'm going to direct some queries into those sections permanently and unerringly, I don't bother with them.
As mentioned above, the redundant where clause should correct the selectivity. I'm really curious about the error that was received though.
I only have 1 more idea aside from what the article say.
Change between to >= AND <=
+ recompile
If that doesn't do it you'll have to add the extra condition that forces the use of the index (between 1 and 100000????).
November 15, 2011 at 10:27 am
GilaMonster (11/15/2011)
http://sqlinthewild.co.za/index.php/2011/11/09/sql-university-advanced-indexing-filtered-indexes-2/Query please?
Yum, tasty. Good stuff there Gail, thanks for laying it out.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2011 at 10:28 am
Damn. In my actual sproc, I am using >= and <=, not between's. I just used between in this test example because I figured it didn't make a difference. I'll give the boundaried superset a shot. Thanks everyone.
November 15, 2011 at 10:30 am
Ninja's_RGR'us (11/15/2011)
I only have 1 more idea aside from what the article say.Change between to >= AND <=
+ recompile
If that doesn't do it you'll have to add the extra condition that forces the use of the index (between 1 and 100000????).
Yeah, flight = Lead Brick unfortunately. There's no way out of it. It doesn't want to try to figure out which plan to use based on the parameter values falling into or not into a filtered index. You've got to force the query into the cubbyhole or it just won't go there. I need to finish reading through Gail's article to see if she found some edge cases but 99% of the time you have to bind the query into the filter.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 15, 2011 at 10:32 am
Evil Kraig F (11/15/2011)
Ninja's_RGR'us (11/15/2011)
I only have 1 more idea aside from what the article say.Change between to >= AND <=
+ recompile
If that doesn't do it you'll have to add the extra condition that forces the use of the index (between 1 and 100000????).
Yeah, flight = Lead Brick unfortunately. There's no way out of it. It doesn't want to try to figure out which plan to use based on the parameter values falling into or not into a filtered index. You've got to force the query into the cubbyhole or it just won't go there. I need to finish reading through Gail's article to see if she found some edge cases but 99% of the time you have to bind the query into the filter.
Seems to be what the technet article is saying.
November 15, 2011 at 10:37 am
coronaride (11/15/2011)
declare @begin bigintdeclare @end bigint
set @begin = 10000
set @end = 15000
select FK_SourceID, RecordType from PartyAddress where FK_PartyMain between @begin and @end
That cannot possibly use a filtered index. It's a parameterised query (see the link I posted). Because the two values aren't constant values in the query, there's no way to tell what they will be at compile time. Hence, there's no way that the optimiser can use a filtered index for that because the plan could be valid or invalid depending on the values passed.
Doesn't matter how many times you recompile, because those are variables not constants that cannot use a filtered index at all, in any way.
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
November 15, 2011 at 10:37 am
Evil Kraig F (11/15/2011)
If the query isn't sure it's going to land in the filter its a whole lot more likely to be outright ignored. A parameter can't ensure that.
Exactly.
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
November 15, 2011 at 10:39 am
coronaride (11/15/2011)
Damn. In my actual sproc, I am using >= and <=, not between's. I just used between in this test example because I figured it didn't make a difference.
It doesn't make a difference. In the process of parsing (before optimisation) SQL will turn a BETWEEN into a >= and <= combination, so it's completely the same which you use
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
November 15, 2011 at 10:45 am
Thank you Gail.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply