I have a question about SQL Server 2008 R2 index usage, specifically filtered index usage. Here is my scenario:
Table in question has 52.114.057 records (with primary key Id field). It is consisted of one ID (int) primary key IDFK (int) (unique, foreign key), TypeID (int) field (not primary key!) and multiple custom metadata fields (vcValue001 - vcValue020 for varchar values, dcValue - dcValue020 for number values (decimal sql type) and dtValue001 - dtValue020 for datetime fields).
Simple layout would be:
Distinct TypeID's inside that table is 71, and the TypeID in question is 131 (number is actually not relevant, it just happens that this is actual number used). For this TypeID=131 there are 27.583.573 records inside a table. Indexes are created on each individual TypeID + ("vc", "dc" and "dt" fields) (that means it has 60 indexes + some other). e.g. index on "TypeID, vcValue001" (since I'm always using TypeID as criteria when constructing query).
Now to the query part:
select top 1000 m.IDFK, m.dtValue002 [MyDate] from dbo.Metadata m where (m.nTypeID = 131) and (m.vcValue001 = 'A') and (m.dtValue002 between '20131212' and '20140312 23:59:59') and (m.vcValue003 = 'B') and (m.vcValue011 = 'C') order by m.dtValue002 desc, m.IDFK desc
I have replaced actual values with A, B and C, only datepart is correct. Counts for separate ranges are following:
For faster performance I've also created filtered index:
CREATE NONCLUSTERED INDEX IX_Metadata_nTypeID000131_vcValue001_dtValue002_vcValue003_vcValue005_IDFK ON dbo.Metadata ( vcValue001 asc, dtValue002 asc vcValue003 asc, vcValue005 asc, IDFK asc ) WHERE (nTypeID = 131 AND vcValue001 IS NOT NULL AND dtValue002 IS NOT NULL)
With my current SQL knowledge the above query should benefit from using filtered index, but unfortunately does not. The strangest part is that if I remove "desc" keyword from "m.IDFK" order by column, then it uses filtered index. I've also tried asc order for both columns (m.dtValue002 and m.IDFK) but it still won't use it. If filtered index is not used, then the query uses index on (nTypeID, dtValue002) resulting in approx. 5.000.000 reads, while when using filtered index I get 17 reads. I also cannot force filtered index with index table hint, because SQL Server won't allow it, although WHERE condition matched condition used in filtered index.
Here is table of "order by" combinations and filtered index usage:
How it is possible that filtered index is only used when "order by" fields do not match in direction (asc, dec). I've searched for this problem, but have not found any solution to it. I've also tried creating filtered index on all descending order, an alternate order, but no luck. However, I do need both fields be ordered the same way. Any direction or suggestion would help me a lot.
Just when I think I know some SQL stuff, it's puzzle like this that shakes my knowledge ;)
EDIT: If instead of using IDFK I use ID field for sorting, it work OK (e.g. I get a lot less reads and both sorts can be turned into same direction).
P.S. I'm new to SO, so if there's something not right with my question, please let me know.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
CREATE INDEX metadata_idx_ntypei_vcvalu_vcvalu_vcvalu_dtvalu ON dbo.Metadata (nTypeID,vcValue001,vcValue003,vcValue011,dtValue002);
SELECT TOP 1000 m.IDFK, m.dtValue002 [MyDate] FROM dbo.Metadata m WHERE ( m.nTypeID = 131 ) AND ( m.vcValue001 = 'A' ) AND ( m.dtValue002 BETWEEN '20131212' AND '20140312 23:59:59' ) AND ( m.vcValue003 = 'B' ) AND ( m.vcValue011 = 'C' ) ORDER BY m.dtValue002 DESC, m.IDFK DESC