Can this SQL Query be improved
Off the top of my head, these are the improvements I would try:


Add a clustered (unique if possible) index to the temp table, #tblAssets (iAssetId). This will eliminate the Sort operator, and may potentially improve the type of join operator.
On the index IX_VehicleMonitoringLog_iAssetId_eEventCode_NonClustered, include the column iVehicleMonitoringId. This will eliminate the KeyLookup operator, which is potentially very expensive.


Not so much an optimization, but rather for correctness: Shouldn't this...

PARTITION BY ignOn.iAssetId, ignOn.dtUTCDateTime ORDER BY ignOff.dtUTCDateTime

... rather be...

PARTITION BY ignOn.iAssetId, ignOn.dtUTCDateTime ORDER BY p.dtUTCDateTime?

I'm thinking that for all the records of any given partition, the ignOff.dtUTCDateTime should be the same, and you probably want to loop through each "segment", i.e. the p.dtUTCDateTime, right?

You may already get the correct results by accident with the current query because the records may be correctly ordered from the Index Seek on p, but I would probably want to make sure by specifying the correct ordering. As a bonus, this might improve performance.