Are these database indexes correct for my search

Question

I am new to Database Administration. I am following information provided by: http://www.brentozar.com/blitzindex/

I have a table VehicleMonitoringLog. When I execute the sp_BlitzIndex script it shows that this is a Heap, because it doesn't have a clustered index.

Now, all of our searches on this table are done based on iAssetId and dtUTCDateTime. This is the Query:

Select top (1) * 
from VehicleMonitoringLog 
where iAssetId = 1 
Order By dtUtcDateTime DESC

We don't search based on the PK, BUT we are inserting data into this table every few seconds.

My questions are:

Because we are inserting data every few seconds do I need to create a clustered index on the PK?

OR, should I create a clustered index on iAssetId and dtUtcDateTime like this?

CREATE CLUSTERED INDEX
[VehicleMonitoringLog_Asset_dtmUTCDateTime_ClusteredIndex]

ON [dbo].[VehicleMonitoringLog] ( [iAssetId] ASC, [dtUTCDateTime] 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) ON [PRIMARY]

EDIT: These are the 3 columns in Question:

I have added an image showing the PK and FK of the Table. Another search we do is something like this:

Select * from VehicleMonitoringLogs 
where iAssetId = 1 
and dtUTCDateTime > GetDate() - 1 
and dtUTCDateTime  <  GetDate() 
Order by dtUTCDateTime ASC

Answer 1

The clustered index in your question will optimize the select query because:

1) you are requesting all columns

2) the first column satisfies the equality predicate on iAssetID

3) the second column is useful for the dtUTCDateTime inequality predicate

Since you also have a surrogate iVehicleMonitoringId key that serves no purpose other than to provide a unique value, consider changing your primary key to a clustered composite one on iAssetId, dtUTCDateTime, and iVehicleMonitoringId. That would avoid the need for a separate index. My assumption is you have no tables that reference this log table so the wider key shouldn't be an issue.

Answer 2

Heap's are better for inserting into because they don't suffer the performance (albeit small) of working out where in the B-Tree to put your row and then adjust if necessary the index nodes in the tree.

If that is the only query you are running then a non-clustered index as follows would be best:

create nonclustered index nc_blah on VehicleMonitoringLog ( iAssetId, dtUTCDateTime desc )

The data will be indexed in the correct order for your query.

I wouldn't use a clustered index with the above because you'd be constantly suffering from page splits on the whole table rather than just the non-clustered index.

I've assumed your PK is ( iAssetId, dtUTCDateTime )