Why is my query slower when I use the index I made for it?

Question

I have the following table made with EntityFramework 6.1:

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);

Thats a lot of information, and our most general (and slowest) query uses only a subset of it:

SELECT TOP 1 OperationalSeconds
  FROM MachineryReading
  WHERE MachineryId = @id
    AND DateRecorded > @start
    AND DateRecorded < @end
    AND OperationalSeconds <> 0

The table stores several million rows recorded since about 2012, although our code is set up to start some searches from 2000. It worked quite slowly, so one of the guys I worked with broke a table based on DateRecorded:

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)

However, the above request is still running rather slowly. Therefore, in addition, I made another index:

CREATE NONCLUSTERED INDEX [IX_MachineryId_DateRecorded]
    ON [dbo].[MachineryReading]([DateRecorded] ASC, [MachineryId] ASC)
    INCLUDE([OperationalSeconds], [FixStatus]);

, , , Constant Scan Index Seek IX_MachineryId. , .

, : WITH(INDEX(IX_MachineryId_DateRecorded)).

. , , ! ? ?

Answer 1

DateRecorded MechanicalId . .