Sql-server – Why is this datetime index not being used

execution-planindexindex-tuningquery-performancesql server

I have a table like this:

CREATE TABLE TestTable
(
    [TestTableID] [int] IDENTITY(1,1) NOT NULL,
    [IntField1] [int] NOT NULL,
    [IntField2] [int] NOT NULL,
    [IntField3] [int] NOT NULL,
    [IntField4] [int] NOT NULL,
    [IntField5] [int] NOT NULL,
    [DateField1] [datetime] NOT NULL,
    [IntField6] [int] NOT NULL,
    [IntField7] [int] NOT NULL,
    [TextField1] [nvarchar](300) NULL,
    [DateField2] [datetime] NULL,
    [TextField2] [nvarchar](300) NULL,
    [DateField3] [datetime] NULL,
    [BoolField1] [bit] NULL
)

I have created an index like this:

CREATE NONCLUSTERED INDEX IX_TestTable_DateField1
ON TestTable(DateField1);

And now I have this query:

    DECLARE @startDate DATETIME = '20190101'
          , @endDate   DATETIME = '20200101'

    SELECT [TestTableID], 
           [IntField1], 
           [IntField2], 
           [IntField3], 
           [IntField4], 
           [IntField5], 
           [DateField1], 
           [IntField6], 
           [IntField7], 
           [TextField1], 
           [DateField2], 
           [TextField2],
           [DateField3], 
           [BoolField1]
      FROM TestTable
     WHERE DateField1 >= @startDate
       AND DateField1 < @endDate

This table has nearly 10,000,000 records, and this query will return nearly 10,000 records.

Now, I expected the query to at least use my index IX_TestTable_DateField1 (Index Scan + Key Lookup) but it is doing a Clustered Index Scan (on PK field). I think this is because the query is returning all fields for the table.

My previous thinking was:

If the index has INCLUDEd all fields, so the SqlServer will do an Index Seek;
If not included all fields, but if the field is used in WHERE or ORDER, it will use Index Scan + Key Lookup;
If neither 1 or 2, it will do a Clustered Index Scan;

Is this correct? Why an "Index Scan + Key Lookup" is not happening?

Best Answer

Here's what Microsoft says about Optimizing SELECT statements

The SQL Server Query Optimizer is a cost-based optimizer. Each possible execution plan has an associated cost in terms of the amount of computing resources used. The Query Optimizer must analyze the possible plans and choose the one with the lowest estimated cost.

[...]

The SQL Server Query Optimizer does not choose only the execution plan with the lowest resource cost; it chooses the plan that returns results to the user with a reasonable cost in resources and that returns the results the fastest. For example, processing a query in parallel typically uses more resources than processing it serially, but completes the query faster.

As you can see the Query Optimizer will choose the plan it expects to obtain the most eficient execution. Sometimes using an (Index Scan + Key Lookup) isn't the most eficient method.

As a test, you could compare the execution plan STATISTICS TIME and STATISTICS IO results you're getting now with the ones generated for your query forcing it to use the index with a query hint. Please, notice that I'm not advising you to use this hint as a solution, but as a way of comparing what would the execution performance be like if it used the index as you wish.

For further reading, Benjamin Nevarez's article brings some good information: The SQL Server Query Optimizer

Related Solutions

Sql-server – Why clustered index scan

This table is very small!

It has 20 rows of which 2 match the search condition. The table definition contains three columns and two indexes (which both support uniqueness constraints).

CREATE TABLE Person.ContactType(
    ContactTypeID int IDENTITY(1,1) NOT NULL,
    Name dbo.Name NOT NULL,
    ModifiedDate datetime NOT NULL,
    CONSTRAINT PK_ContactType_ContactTypeID PRIMARY KEY CLUSTERED(ContactTypeID),
    CONSTRAINT AK_ContactType_Name UNIQUE NONCLUSTERED(Name)
)

Running

SELECT index_type_desc,
       index_depth,
       page_count,
       avg_page_space_used_in_percent,
       avg_record_size_in_bytes
FROM   sys.dm_db_index_physical_stats(db_id(), 
                                      object_id('Person.ContactType'), 
                                      NULL, 
                                      NULL, 
                                      'DETAILED')

Shows both indexes only consist of a single leaf page with no upper level pages.

+--------------------+-------------+------------+--------------------------------+--------------------------+
|  index_type_desc   | index_depth | page_count | avg_page_space_used_in_percent | avg_record_size_in_bytes |
+--------------------+-------------+------------+--------------------------------+--------------------------+
| CLUSTERED INDEX    |           1 |          1 | 15.9130219915987               | 62.5                     |
| NONCLUSTERED INDEX |           1 |          1 | 13.1949592290586               | 51.5                     |
+--------------------+-------------+------------+--------------------------------+--------------------------+

Rows on each index page aren't necessarily in index key order but each page has a slot array with the offset of each row on the page. This is maintained in index order.

The nonclustered index covers two out of the three columns (Name as a key column and ContactTypeID as a row locator back to the base table) but is missing ModifiedDate.

You can use index hints to force the NCI seek as below

SELECT ct.*
FROM   Person.ContactType AS ct WITH (INDEX = AK_ContactType_Name)
WHERE  ct.Name LIKE 'Own%';

But you can see that under SQL Server's cost model this plan is given a higher estimated cost than the competing CI scan (roughly double).

enter image description here

The single page clustered index scan would just need to read all the 20 rows on the page, evaluate the predicate against them and return them.

The single page nonclustered index range seek might potentially be able to perform a binary search on the slot array to reduce the number of rows evaluated however the index does not cover the query so it would also need a potential IO to retrieve the CI page and then it would still need to locate the row with the missing column values on there (for each row returned by the NCI seek).

On my machine running 1 million iterations of the non clustered index plan took 15.245 seconds compared to 11.113 seconds for the clustered index plan. Whilst this is far from double the plan without the hint was measurably faster.

Even if the table was orders of magnitude larger however you may well still not get your expected plan with lookups.

SQL Server's costing model prefers sequential scans to random IO lookups and the "tipping point" between it choosing a scan of a covering index or a seek and lookups of a non covering one is often surprisingly low as discussed in Kimberley Tripp's blog post here.

It is certainly not out of the question that it would choose such a plan for a 10% selective predicate but the clustered index would likely need to be quite a lot wider than the NCI for it to do so.

Postgresql – Why is the composite index not used for this table

As @dezso suggested, the table with test data was not big enough for the query planner bother going to the index. After I imported a larger set of data, the query uses the index as expected.

Best Answer

Related Solutions

Sql-server – Why clustered index scan

Postgresql – Why is the composite index not used for this table

Related Question