T-SQL SELECT use multiple indexes for no reason

Question

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:

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:

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:

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?

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

Answer 1

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.

Answer 2

The execution plans seems fine to me and logical.

In your first query, you're doing an index seek on your nonclustered index on your Date column and then doing a clustered key lookup (I think, my german is rusty to say the least, but from context it maches).

Is not a 'two seeks' operation. It's a seek and get additional data.

In your second query you're not using your non-clustered index because you qualified your query with timestamp column and then it can use your clustered index (in your case also primary key), which have all the data and therefore does not need to look up additional.

It seems sensible enough.