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.