[Solved] SQL Server 2008 R2 strange index usage

EverSQL Database Performance Knowledge Base

SQL Server 2008 R2 strange index usage

Database type:

I have a question about SQL Server 2008 R2 index usage, specifically filtered index usage. Here is my scenario:

@@version output:

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.

Best regards!

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Use Numeric Column Types For Numeric Values (query line: 14): Referencing a numeric value (e.g. 20131212) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
Optimal indexes for this query:
CREATE INDEX metadata_idx_ntypei_vcvalu_vcvalu_vcvalu_dtvalu ON dbo.Metadata (nTypeID,vcValue001,vcValue003,vcValue011,dtValue002);
The optimized query:
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

Related Articles



* original question posted on StackOverflow here.