SQL Server : clustered index slow

Question

I have 2 database tables:

Message table that contains gps message
MessageData that contains message sensors values

I join them to get sensor values between 2 dates.

The query returns results slowly with 99% of time seeking the clustered index.

How to improve this performance? Any advice on the index or the query side?

I run the following query for join: I filter with primary key > 3264353049 to prevent seek dates with rows from last years

SELECT 
    t.MessageId, t.DataSourceId, t.[Value],
    DataSource.SourceNameId, DataSource.Name
FROM 
    [MessageData] t
INNER JOIN 
    [Message] m ON t.MessageId = m.MessageId
LEFT JOIN
    DataSource ON t.DataSourceId = DataSource.DataSourceId
WHERE
    t.MessageId > 3264353049 
    AND m.ObjectId = @objId 
    AND m.GpsTime BETWEEN @dtFrom AND @dtTo 
    AND m.Valid = 1;

I have these indexes:

ALTER TABLE [MessageData] 
    ADD CONSTRAINT [AnalogData_PK] 
    PRIMARY KEY CLUSTERED ([MessageId] ASC, [DataSourceId] ASC)

CREATE NONCLUSTERED INDEX [MessageData_DataSourceId_IDX] 
ON [MessageData] ([DataSourceId] ASC)
GO

CREATE NONCLUSTERED INDEX [IX_gpstime_objectid] 
ON [Message] ([GpsTime] ASC)
INCLUDE ([MessageId], [ObjectId]) 
GO

CREATE UNIQUE CLUSTERED INDEX [Message_Object_UK] 
ON [Message] ([ObjectId] ASC, [GpsTime] ASC, [MessageId] ASC)
GO

Message table data:

MessageId            ObjectId    VectorAngle VectorSpeed Altitude    GpsTime                        X                      Y                      VisibleSatelites
-------------------- ----------- ----------- ----------- ----------- ------------------------------ ---------------------- ---------------------- ----------------
9988600080           192         0           0           0           2017-07-19 00:03:20            0             0             0
9988600082           192         0           0           0           2017-07-19 00:08:20            0             0             0
9988600086           192         0           0           0           2017-07-19 00:13:20            0             0             0
9988600089           192         0           0           0           2017-07-19 00:18:20            0             0             0
9988600092           192         0           0           0           2017-07-19 00:23:20            0            0             0

and MessageData table data:

MessageId            DataSourceId Value                  SourceNameId Name
-------------------- ------------ ---------------------- ------------ ------------------------------
9988600080           6364         0                      1            Engine
9988600080           6365         0                      2            Digital Input Status 2
9988600080           325346       0                      179          DOUT 1
9988600080           325347       0                      180          DOUT 2
9988600080           334214       0                      69           Bettary
9988600082           6364         0                      1            Engine
9988600082           6365         0                      2            Digital Input Status 2
9988600082           325346       0                      179          DOUT 1
9988600082           325347       0                      180          DOUT 2
9988600082           334214       0                      69           Bettary
9988600086           6364         0                      1            Engine
9988600086           6365         0                      2            Digital Input Status 2
9988600086           325346       0                      179          DOUT 1
9988600086           325347       0                      180          DOUT 2
9988600086           334214       0                      69           Bettary
9988600089           6364         0                      1            Engine
9988600089           6365         0                      2            Digital Input Status 2
9988600089           325346       0                      179          DOUT 1
9988600089           325347       0                      180          DOUT 2
9988600089           334214       0                      69           Bettary
9988600092           6364         0                      1            Engine
9988600092           6365         0                      2            Digital Input Status 2
9988600092           325346       0                      179          DOUT 1
9988600092           325347       0                      180          DOUT 2
9988600092           334214       0                      69           Bettary

SQL Server : clustered index slow

1 Answers