Here's the run-down: I'm doing a select query. Every column in the
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 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.