For me it was quick to find the root cause.

I found many non clustered indexes included dateTime as part of a covering index. 6 indexes on the main mothershiptable of our app.

Problem is that dateTime is not an int.

Chances of getting the exact time is very bad so doesnt work with "=".

They work best when clustered on datetime , i.e. ">=; <" etc. (if clustered on a column that is ever increasing and never changing.)

So when you have stuff like "where [datetimecolumn] >=getdate()" (if datetimecolumn is indexed)

the optimizer says "You know what, range scan on date time is so slow jumping between index and record and I have to do it a milliion times, let me rather do a table scan, as there is no jumping between index and record".

It then avoids the index at all costs cause it would be, well, costly.

The next question should be, who actually allowed an index to be created including datetime on a nonclustered index.

I then ran a query finding all nonclustered indexes which include datetime and surprise surprise, they had millions of updates and no userscan, no index seek, no index scan, rid lookup(heap).

This is fine if it is the PK that never gets queried on, and links to other tables using FK's.

But I am a mere tester so my opinion is worth less than squat. Even with proof.