Sql-server – T-SQL SELECT use multiple indexes for no reason

sql serversql-server-2008t-sql

I have a question about indexer and execution plans in T-SQL.

My database is SQL Server 2008.

I have a simple three-table db schema:

database schema

The InverterData table is very large and partitioned (28,880,436 rows).

The Date column is calculated like this:

[Date] AS (ISNULL(CONVERT([date], [TimeStamp]), CONVERT([DATE], '19000101', (112)))) PERSISTED NOT NULL,

There is also a index for this column:

CREATE NONCLUSTERED INDEX [NonClusteredIndex] 
ON [InverterData] ([Date] DESC, [InverterID] ASC)

Select query #1:

I now want to make a simple select that include all three tables and the 'Date' column in a where clause:

SELECT 
    [TimeStamp], [ACPower], [DCPower]
FROM 
    [InverterData]
JOIN 
    [Inverter] ON [InverterData].[InverterID] = [Inverter].[ID]
JOIN 
    [DataLogger] ON [Inverter].[DataLoggerID] = [DataLogger].[ID]
WHERE 
    [InverterData].[Date] = '05.01.2016'
    AND [DataLogger].[ProjectID] = 20686

It took round about 19 seconds on me current database (result ~80 rows).

This is the execution plan:

slow query by date

Select query #2:

In the first select I detected that there is a long duration index seek for the 'Date' column. So I run a second select that only include the primary key column 'TimeStamp'.

This is the second select:

SELECT 
    [TimeStamp], [ACPower], [DCPower]
FROM 
    [InverterData]
JOIN 
    [Inverter] ON [InverterData].[InverterID] = [Inverter].[ID]
JOIN 
    [DataLogger] ON [Inverter].[DataLoggerID] = [DataLogger].[ID]
WHERE 
    [TimeStamp] >= '05.01.2016' AND [TimeStamp] < '06.01.2016'
    AND [DataLogger].[ProjectID] = 20686

It took only about 2 seconds on me current database.

This is the execution plan:

fast query by timestamp

Question:

Why are there two index seeks? I included all used columns from select 1 in one index. Why did it took so much longer?

details

Update 1:

If someone needed the complete schema I will add it to a SQL fiddle and post it.

Update 2:

Tooltip of index seek

details2

Best Answer

In your first query the index is used to find rows matching [InverterData].[Date] = '05.01.2016' then it needs to lookup the rest of the row data to satisfy being able to return ACPower and DCPower - if you remove these columns from the output you'll see the extra lookup go away.

You could include the extra columns in the index with:

CREATE NONCLUSTERED INDEX [NonClusteredIndex] 
ON [InverterData] ([Date] DESC, [InverterID] ASC) INCLUDE ([ACPower], [DCPower])

This removes the extra lookup via the clustered index at the expense of making the non-clustered index consume more space on disk (and in memory). This query speed and used space trade-off is something you'll have to decide upon by running benchmarks on the bits of the application that use that table.

Note that you could also do:

CREATE NONCLUSTERED INDEX [NonClusteredIndex] 
ON [InverterData] ([Date] DESC, [InverterID] ASC, [ACPower], [DCPower])

which uses the extra columns as part of the key rather than just INCLUDING them. The former example is likely to be more efficient as the values are unlikely to be filtered/sorted upon so it saves page splits caused by the engine trying to keep the (effectively random) values stored in order.

Related Question