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
