Index(es) really are not going to help you with this aggregate function; however, there are alternatives if you can add columns, tables, or procedures. The increases offered are going to be determined by the quantity of devices and the percentage of rows they contribute.
To make things better... we have a couple of options...
Let's say we went on a normalization approach and created a
TelemetryDevice table to be a unique listing of the devices and some simple information about it
CREATE TABLE TelemetryDevice (
DeviceID INT IDENTITY(1,1) NOT NULL,
DeviceName NVARCHAR(100) NOT NULL,
LastContact DATETIME NULL,
CONSTRAINT PK_TelemetryDevice_DeviceName PRIMARY KEY CLUSTERED ([DeviceID] ASC) ON [PRIMARY]
) ON [PRIMARY]
GO
We could then Create/Alter a Stored Procedure which populatates the Telemetry log table AND updates the Device table
CREATE PROCEDURE Telemetry_Log_IncomingCommunication (
@DeviceName VARCHAR(50)
) AS
BEGIN
DECLARE @Now DATETIME = GetDate()
INSERT Telemetry (DeviceName, SentAt )
VALUES (@DeviceName, @Now )
UPDATE TelemetryDevice
SET LastContact = @Now
WHERE DeviceName = @DeviceName
END
GO
To make this truly normalized, your existing table would be modified to be the actual DeviceID; and the Stored Procedure would be adjusted accordingly.
Your new query would be
SELECT DeviceName, LastContact
FROM Telemetry
As the DeviceName is a Primary Key, you would not need to add an index
The other approach would be to add a column to your existing table
ALTER TABLE Telemetry
ADD LastMessage BIT NULL DEFAULT (0)
GO
And again a corresponding Stored Procedure for populating it
ALTER PROCEDURE Telemetry_Log_IncomingCommunication (
@DeviceName VARCHAR(50)
) AS
BEGIN
DECLARE @Now DATETIME = GetDate()
UPDATE Telemetry
SET LastMessage = 0
WHERE DeviceName = @DeviceName
INSERT Telemetry (DeviceName, SentAt, LastMessage )
VALUES (@DeviceName, @Now, 1 )
END
GO
Your new query would then be
SELECT DeviceName, SentAt
FROM Telemetry
WHERE LastMessage = 1
Which would benefit IF you had indexes on DeviceName by itself and a Unique Index of DeviceName and the LastMessage column.