SQL Server slow select from large Table

Question

i have 2 Really Big sql server Database tables for IOT Project

First TABLE IS Message (rows count 7,423,889,085 rows)

CREATE TABLE [aymax].[Message](
    [MessageId] [bigint] IDENTITY(1,1) NOT NULL,
    [ObjectId] [int] NOT NULL,
    [TimeStamp] [datetime] NOT NULL CONSTRAINT [DF__Message__TimeSta__3B75D760]  DEFAULT (getdate()),
    [GpsTime] [datetime] NOT NULL,
    [VisibleSatelites] [int] NOT NULL,
    [X] [float] NOT NULL,
    [Y] [float] NOT NULL,
 CONSTRAINT [Message_PK] PRIMARY KEY NONCLUSTERED 
(
    [MessageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Second table is MessageSensors , row count (26,359,568,037 rows) , this table have value for each sensor in message table

CREATE TABLE [aymax].[MessageSensors](
    [MessageId] [bigint] NOT NULL,
    [DataSourceId] [int] NOT NULL,
    [Value] [float] NOT NULL CONSTRAINT [DF__AnalogDat__Value__5812160E]  DEFAULT ((0)),
 CONSTRAINT [AnalogData_PK] PRIMARY KEY CLUSTERED 
(
    [MessageId] ASC,
    [DataSourceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

my problem that seek by time interval between 2 date time is really slow , also it became more slow if i select with message sensor data , also when i use sp_BlitzIndex check from brentozar.com it say that i have

"Indexaphobia: High value missing index"

1. [aymax].[MessageSensors] (EQUALITY: [DataSourceId], [Value] INCLUDES: [MessageId] )
2. [aymax].[MessageSensors] EQUALITY: [Value] INCLUDES: [MessageId], [DataSourceId]

I belive that create this 2 index is will increase storage alot , also will take too much time to be created , i need your advice for both table regarding index

my current indexes

1-

    CREATE NONCLUSTERED INDEX [IX_gpstime_objectid] ON [aymax].[Message]
(
    [GpsTime] ASC
)
INCLUDE (   [MessageId],
    [ObjectId]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

2-

alter TABLE [aymax].[Message] ADD  CONSTRAINT [Message_PK] PRIMARY KEY NONCLUSTERED 
    (
        [MessageId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
    GO

3rd-

ALTER TABLE [aymax].[MessageSensors] ADD  CONSTRAINT [AnalogData_PK] PRIMARY KEY CLUSTERED 
(
    [MessageId] ASC,
    [DataSourceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

4-

CREATE NONCLUSTERED INDEX [MessageData_DataSourceId_IDX] ON [aymax].[MessageSensors]
(
    [DataSourceId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

any help please , i need to make a fast retrieve from message , and message sensor

update while doing some investigate i found that select float value will slow up the result too much , from 1 second to 3 minutes

SELECT m.messageid, 
               m.objectid, 
               m.gpstime, 
               m.x, 
               m.y, 
               -- slow is here  if i replace md.value with md.messageId will return fast , md.value is float 
               md.Value , 
               0 
        FROM   aymax.[message] m  WITH (nolock)  
               left JOIN aymax.MessageSensors md WITH (nolock) 
                      ON m.messageid = md.messageid 
                      AND md.datasourceid = 425732 
                       
                        
        WHERE  m.objectid = 14099 
               AND m.gpstime BETWEEN '2017-04-01 19:46:18.607' AND '2017-04-10 19:05:18.607' 

Answer 1

Possible solutions:

1. Filtered index (filter by date and do not index old data) https://learn.microsoft.com/en-us/sql/relational-databases/indexes/create-filtered-indexes.
2. Clustered index on GpsTime, MessageId (Espessially if you have no plans about another indexes). Requires rebuild your table.
3. Partitions (see @Siyaul's comments)