Brian O'Leary (7/5/2011)
doranmackay (7/5/2011)
For me it was quick to find the root cause.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.
Thats not true. If your nonclustered index is not covering you will still need to do a bookmark or rid lookup (thats the point of covering) regardless of the data type. The descision to perform a range scan by the optimizer is to do with cardinality rather than the use of a datetime data type.
For example, run this script against the AdventureWorks database and you will see an index seek based on the date filter, however if you change the date from '20040703' to '20040702' the optimzer will choose a clustered index scan.
Correct, I should have included that the specific indexes existed on a highly transactional database.
DateTime has low cardinality when only using the date portion,and based on the size of the result set estimated in adventureworks, I would expect successful usage of datetime index since the results from smaller tables is few.
Do the same test, where you have 1000 unique records being created per hour, and modified, with a couple of joins happening and due to the row count of the tables, a date filter will result in too much records. In those situations, inserts and updates cost because the likelihood of benefiting from a datetime index is zero unless you can specify the datetime down to the millisecond and use where x = '20040703.123456'.