Here's the run-down: I'm doing a select query. Every column in the WHERE
and ORDER BY
clauses are in a single non-clustered index IX_MachineryId_DateRecorded
, either as part of the key, or as INCLUDE
columns. I'm selecting all the columns, so that will result in a bookmark lookup, but I'm only taking TOP (1)
, so surely the server can tell the lookup only needs to be done once, at the end.
Most importantly, when I force the query to use index IX_MachineryId_DateRecorded
, it runs in less than a second. If I let the server decide which index to use, it picks IX_MachineryId
, and it takes up to a minute. That really suggests to me that I have made the index right, and the server is just making a bad decision. Why?
CREATE TABLE [dbo].[MachineryReading] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Location] [sys].[geometry] NULL,
[Latitude] FLOAT (53) NOT NULL,
[Longitude] FLOAT (53) NOT NULL,
[Altitude] FLOAT (53) NULL,
[Odometer] INT NULL,
[Speed] FLOAT (53) NULL,
[BatteryLevel] INT NULL,
[PinFlags] BIGINT NOT NULL,
[DateRecorded] DATETIME NOT NULL,
[DateReceived] DATETIME NOT NULL,
[Satellites] INT NOT NULL,
[HDOP] FLOAT (53) NOT NULL,
[MachineryId] INT NOT NULL,
[TrackerId] INT NOT NULL,
[ReportType] NVARCHAR (1) NULL,
[FixStatus] INT DEFAULT ((0)) NOT NULL,
[AlarmStatus] INT DEFAULT ((0)) NOT NULL,
[OperationalSeconds] INT DEFAULT ((0)) NOT NULL,
CONSTRAINT [PK_dbo.MachineryReading] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_dbo.MachineryReading_dbo.Machinery_MachineryId] FOREIGN KEY ([MachineryId]) REFERENCES [dbo].[Machinery] ([Id]) ON DELETE CASCADE,
CONSTRAINT [FK_dbo.MachineryReading_dbo.Tracker_TrackerId] FOREIGN KEY ([TrackerId]) REFERENCES [dbo].[Tracker] ([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_MachineryId]
ON [dbo].[MachineryReading]([MachineryId] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_TrackerId]
ON [dbo].[MachineryReading]([TrackerId] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_MachineryId_DateRecorded]
ON [dbo].[MachineryReading]([MachineryId] ASC, [DateRecorded] ASC)
INCLUDE([OperationalSeconds], [FixStatus]);
The table is partitioned into month ranges (though I still don't really understand what's going on there).
ALTER PARTITION SCHEME PartitionSchemeMonthRange NEXT USED [Primary]
ALTER PARTITION FUNCTION [PartitionFunctionMonthRange]() SPLIT RANGE(N'2016-01-01T00:00:00.000')
ALTER PARTITION SCHEME PartitionSchemeMonthRange NEXT USED [Primary]
ALTER PARTITION FUNCTION [PartitionFunctionMonthRange]() SPLIT RANGE(N'2016-02-01T00:00:00.000')
...
CREATE UNIQUE CLUSTERED INDEX [PK_dbo.MachineryReadingPs] ON MachineryReading(DateRecorded, Id) ON PartitionSchemeMonthRange(DateRecorded)
The query that I would normally run:
SELECT TOP (1) [Id], [Location], [Latitude], [Longitude], [Altitude], [Odometer], [ReportType], [FixStatus], [AlarmStatus], [Speed], [BatteryLevel], [PinFlags], [DateRecorded], [DateReceived], [Satellites], [HDOP], [OperationalSeconds], [MachineryId], [TrackerId]
FROM [dbo].[MachineryReading]
--WITH(INDEX(IX_MachineryId_DateRecorded)) --This makes all the difference
WHERE ([MachineryId] = @p__linq__0) AND ([DateRecorded] >= @p__linq__1) AND ([DateRecorded] < @p__linq__2) AND ([OperationalSeconds] > 0)
ORDER BY [DateRecorded] ASC
Query plan: https://www.brentozar.com/pastetheplan/?id=r1c-RpxNx
Query plan with forced index: https://www.brentozar.com/pastetheplan/?id=SywwTagVe
The plans included are the actual execution plans, but on the staging database (about 1/100th of the size of live). I'm hesitant to be fiddling with the live database because I only started at this company about a month ago.
I have a feeling it's because of the partitioning, and my query typically spans every single partition (e.g. when I want to get the first or last OperationalSeconds
ever recorded for one machine). However, the queries I have been writing by hand are all running a good 10 – 100 times faster than what EntityFramework has generated, so I'm just going to make a stored procedure.
Best Answer
That index is not partitioned, so the optimizer recognizes it can be used to provide the ordering specified in the query without sorting. As a non-unique nonclustered index, it also has the keys of the clustered index as subkeys, so the index can be used to seek on
MachineryId
and theDateRecorded
range:The index does not include
OperationalSeconds
, so the plan has to look that value up per row in the (partitioned) clustered index in order to testOperationalSeconds > 0
:The optimizer estimates that one row will need to be read from the nonclustered index and looked up to satisfy the
TOP (1)
. This calculation is based on the row goal (find one row quickly), and assumes a uniform distribution of values.From the actual plan, we can see the estimate of 1 row is inaccurate. In fact, 19,039 rows have to be processed to discover that no rows satisfy the query conditions. This is the worst case for a row goal optimization (1 row estimated, all rows actually needed):
You can disable row goals with trace flag 4138. This would most likely result in SQL Server choosing a different plan, possibly the one you forced. In any case, the index
IX_MachineryId
could be made more optimal by includingOperationalSeconds
.It is quite unusual to have non-aligned nonclustered indexes (indexes partitioned in a different way from the base table, including not at all).
As usual, the optimizer is selecting the cheapest plan it considers.
The estimated cost of the
IX_MachineryId
plan is 0.01 cost units, based on the (incorrect) row goal assumption that one row will be tested and returned.The estimated cost of the
IX_MachineryId_DateRecorded
plan is much higher, at 0.27 units, mostly because it expects to read 5,515 rows from the index, sort them, and return the one that sorts lowest (byDateRecorded
):This index is partitioned, and cannot return rows in
DateRecorded
order directly (see later). It can seek onMachineryId
and theDateRecorded
range within each partition, but a Sort is required:If this index were not partitioned, a sort would not be required, and it would be very similar to the other (unpartitioned) index with the extra included column. An unpartitioned filtered index would be slightly more efficient still.
You should update the source query so that the data types of the
@From
and@To
parameters match theDateRecorded
column (datetime
). At the moment, SQL Server is computing a dynamic range due to the type mismatch at runtime (using the Merge Interval operator and its subtree):This conversion prevents the optimizer from reasoning correctly about the relationship between ascending partition IDs (covering a range of
DateRecorded
values in ascending order) and the inequality predicates onDateRecorded
.The partition ID is an implicit leading key for a partitioned index. Normally, the optimizer can see that ordering by partition ID (where ascending IDs map to ascending, disjoint values of
DateRecorded
) thenDateRecorded
is the same as ordering byDateRecorded
alone (given thatMachineryID
is constant). This chain of reasoning is broken by the type conversion.Demo
A simple partitioned table and index:
Query with matched types
Query with mismatched types