To be able to troubleshoot something, I need full information, yes. Here I was wondering whether the only the JUL2013 was indexed - that certainly asks for trouble. And I would have appreciated to get everything in one script.

Anyway, I looked closer at the complex plan, and this is a good plan. If you look at the Filter operators in each branch, they have a startup expression, which means that the shard in question is only visited if the date falls within the interval for the shard.

I don't understand the indexing. Why is there a separate clustered index on reportStamp, and then a NC primary key on reportStamp and Id? Why not just make the PK clustered?

Nothing good can come out of these hints. NOLOCK is a hint which is very difficult to use and which can lead to all sorts of nasty surprises. And you should not need it for a query that reads a single row. And FORCESEEK is also pointless here. There is a index all set up to use.

So why is this query slow some times? We would need to see the plan from such an occasion. If someone fiddles with the constraints so that they are not trusted anymore, the startup expression will disappear. However, it is not impossible that the inappropriate definition of the index is the culprit. It should really be:

CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[tblDWProbe]

(

[parameterId],

[vehicleId],

[reportStamp])

INCLUDE ([paramValue])

Since there is equality conditions on parameterId and vehicleId, they should come before reportStamp. Thus, I would recommend that you change the index on all shards.