Filtered indexes issue -URGENT HELP REQUIRED
-
Dear all,
I have created one filtered index on column a, b, c and covering columns are d,e,f and the filter is on one date let say column g and one more column called as f
The problem is column a, b, d, g, f are the part of UC. So everytime my query is picking up the UC instead of using the filtered index. The whole part is im using b,c in my select list and also im providing the filters in where clause.
If i provide the filtered index hint with option recompile in procedure then it is running quite smoothly.where as if i run it as query then i dont even required to provide the hints, also i have given the exact where conditions, even in the same order in which i have given in filtered index that means no parameterization.
I tried to seek about this in google but no such link i have found which gives me the idea about this.
I don't want to use the option recompile everywhere as it is not a good practice and i have around 100 of queries like this..
please let me know how can i overcome with this problem.
Thanks
Vineet bhargava
Thanks And Regards
Vineet Bhargava
vineetbhargav@gmail.com -
Please post query and index, can't quite follow what's happening from the description
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
If the optimizer still thinks, based on statistics, a different index is more selective than your filtered index, that would explain why it's being selected. I can't say beyond that since we're talking in such general terms.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
I will recommend you to post the query and the index. I'm sure that with that info Gail or Grant will be able to help you.
-
Hi all
I have a similar issue so I though a more in depth description may help vineetbhargav too.
I have a proc that is used in a report. The report has shared datasets that work out all related dates that are used in the reports eg. start of year, start of month etc The report pass a parameter @YStartDate, it's a date, to the stored procedure.
The WHERE clause of my query within the proc is WHERE deal_date > @YStartDate
The Filter on my index is (deal_date > '20120401') - I intended to run a job to update the value when necessary ... if I can get it working.
The value passed is from a shared dataset and is currently '20120401' but will obviously change when we move to the next financial year.
What happens:
When the procedure is executed the index is not used. If I hard code the value in the WHERE clause it is. I've tried WITH RECOMPILE and OPTION (RECOMPILE) to try and get a new plan when the parameter is passed but no joy.
The optimizer is clearly unaware that the index can be used and a hint to use it causes an error so I've resorted to using (deal_date is not null) instead.
Any help on ways around this, other than dynamic sql, would be very much appreciated.
Many thanks!
-
MartJ (12/19/2012)
Hi allI have a similar issue so I though a more in depth description may help vineetbhargav too.
I have a proc that is used in a report. The report has shared datasets that work out all related dates that are used in the reports eg. start of year, start of month etc The report pass a parameter @YStartDate, it's a date, to the stored procedure.
The WHERE clause of my query within the proc is WHERE deal_date > @YStartDate
The Filter on my index is (deal_date > '20120401') - I intended to run a job to update the value when necessary ... if I can get it working.
The value passed is from a shared dataset and is currently '20120401' but will obviously change when we move to the next financial year.
What happens:
When the procedure is executed the index is not used. If I hard code the value in the WHERE clause it is. I've tried WITH RECOMPILE and OPTION (RECOMPILE) to try and get a new plan when the parameter is passed but no joy.
The optimizer is clearly unaware that the index can be used and a hint to use it causes an error so I've resorted to using (deal_date is not null) instead.
Any help on ways around this, other than dynamic sql, would be very much appreciated.
Many thanks!
I may be misunderstanding but if your index filter is (deal_date > '20120401') and your query filter is (deal_date = '20120401') then those do not overlap so the index will not apply.
Please post the table and index definition as well as the query. An actual execution plan may help as well.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
There's no way around that.
A filtered index for deal_date > '20120401' cannot satisfy a query WHERE deal_date > @YStartDate because of the parameterisation.
The plan is cached and reused. The optimiser, knowing that, must generate a plan that is safe for reuse, one where the result will always be correct and the query will not error. In most cases, that 'safe for reuse' rule applies even with recompile is specified.
A plan that uses the filtered for a parameterised query is not safe for reuse, because if the query is rerun with a parameter value less than what is specified in the filtered index, the results of the query will be wrong.
p.s. Please post new questions in a new thread in future.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Thanks Gail, makes perfect sense when explained that way.
Thanks again.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply