0

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:

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

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

  • 3
    The query optimizer will choose the plan it expects to obtain the most eficient execution. Not always using the index is 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, note 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. – Ronaldo Nov 13 '19 at 17:52
  • 1
    @Ronaldo, yes, you are correct, when I force the index the logical read is near 4x bigger than without forced indexes. – Click Ok Nov 13 '19 at 18:40
  • 1
    Click Ok, by the time I commented your question I couldn't find the references I wanted to give a more complete answer. Recently I was making some research on the Query Optimizer and remembered your question yet unanswered. If you find it suitable, now you can mark the correct answer so that others can benefit too. – Ronaldo Apr 20 at 10:58
2

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

| improve this answer | |
4

SQL server can definitely do an index seek and then a lookup, even when you don't cover the query.

The optimizer has no idea what values you have in your variables (that is how variables work). So it has to guess the selectivity. You can look at the actual execution plan and see how many rows it guesses. Apparently it guesses so many rows so it decides that it is better to do a table scan (cl ix scan).

If you add OPTION(RECOMPILE) at the end of the query, you should see a different estimated selectivity, and potentially usage of the index (all based on what selectivity you have in the end).

Also, if you have literals (values are known) or stored procedure parameters (values are sniffed), you'll see how it will estimate differently.

| improve this answer | |
2

The issue you are observing has to do with the way SQL Server determines the best way to execute a query and is called parameter sniffing.

Reference Reading

What?

When you executed your query for the fist time the SQL Serer Query Optimizer with the help of the Cardinality Estimator used the statistics of the available indexes to determine which way to retrieve the data you were then requesting.

Now if the values you passed to the variables were initially like the following:

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

..then the Query Optimizer quickly determined that it would need to scan all the data in the index IX_TestTable_DateField1 to retrieve all rows (or possibly even slightly more than 50% of the whole data) to efficiently fulfil the statement.

Instead of using the IX_TestTable_DateField1 index to retrieve all the records to match your query the query optimizer instead chose to read the Clustered Index because the Clustered Index is in effect the data. (Why read a non-clustered index to then retrieve the data, when you can read the clustered index instead and already have the data).

Because this was the first run, the Query Optimizer stored the execution plan (which was optimized for the initial values) in the plan cache.

Whenever a new query (with different values) hits the server, the QO will see that it already has an execution plan that meets the requirements of the query.

The major setback is that the query plan was optimized for the initial values and when you provide new values for the parameters, then the QO is not going to create a new query plan, because that is "expensive".

Solution

If you want your query to use the index, then you are going to have to either:

  • execute WITH RECOMPILE
  • execute the query WITH OPTIMIZE FOR @startDate = '<value>', @endDate = '<value>'
  • execute the query WITH OPTIMIZE FOR UNKNOWN
  • remove the query plan from the plan cache

After which you might observe that the data is retrieved using your index.

Reference Reading

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.