Indexes

Question

Indexes

Viewing 3 posts - 16 through 17 (of 17 total)

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 1

Carlo Romagnano (3/21/2014)
Remember that "Filtered indexes" are used only if the WHERE clause matches or is compatible with the filter.
View http://www.sqlservercentral.com/questions/T-SQL/105826/

And even then, they are often not used at all.

I just uploaded slide deck and sample code of my "Advanced Indexing" talk to http://www.sqlsaturday.com/viewsession.aspx?sat=275&sessionid=18987 - the reason I mention this is that the sample code includes a repro where SQL Server does not use the filtered index, even though it would be a lot cheaper (by head, 1100 vs 80 logical reads, as you can see when hinting the filtered index). Adding an INCLUDE clause with the column used in the filter causes the optimizer to pick the filtered index more often, but still not all the time.

Also see http://connect.microsoft.com/SQLServer/feedback/details/454744/filtered-index-not-used-and-key-lookup-with-no-output (and maybe add a vote or two).

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 2

Hugo Kornelis (3/30/2014)
Carlo Romagnano (3/21/2014)
Remember that "Filtered indexes" are used only if the WHERE clause matches or is compatible with the filter.
View http://www.sqlservercentral.com/questions/T-SQL/105826/
And even then, they are often not used at all.
I just uploaded slide deck and sample code of my "Advanced Indexing" talk to http://www.sqlsaturday.com/viewsession.aspx?sat=275&sessionid=18987 - the reason I mention this is that the sample code includes a repro where SQL Server does not use the filtered index, even though it would be a lot cheaper (by head, 1100 vs 80 logical reads, as you can see when hinting the filtered index). Adding an INCLUDE clause with the column used in the filter causes the optimizer to pick the filtered index more often, but still not all the time.
Also see http://connect.microsoft.com/SQLServer/feedback/details/454744/filtered-index-not-used-and-key-lookup-with-no-output (and maybe add a vote or two).

Hugo always the NUMBER ONE!

Thanks!

🙂

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 3

I dont thino that the options are all correct.

Thanks.