Indexes on Date Time columns can sometimes create inconsistencies in performance.. Some of the examples that we have come across are
=> Processing of cube till last month which contains more data took less time than processing of cube for current month with less data.
=> Queries for today took more time than yesterday or any other date in the past though current day’s data returned is less.
Above of some of the typical cases for indexes DateTime Columns. Other such cases would be Auto Increment cases or identity columns in columns.
When a query is executed against database software (SQL or Oracle or etc), they first use statistics for generating execution plan (remember cost based optimization). Statistics for database engine (IMHO) are more important than indexes themselves.
In above scenarios, statistics would be quickly outdated as even if Auto Update Stats (in SQL ) are turned on, they would kick only some threshold values. So in essence statistics would not be updated as frequently as actual updates tables. Due this inputs to optimizer from statistics are incorrect and as a consequence suboptimal plan would be generated which would be slower.
Most of cases it seemed that in the Showplan_all (SQL) or Explain Plan ( Oracle), Nested Loop (Loop) join would be used which typically runs at disk speed instead of Hash or Merge which are more CPU / Memory oriented.
To test these, run the same query (slow query) with hint (hash join), some thing as below
<Query> option (Hash Join)).
If above works, update statistics for such index more frequently with below script.
Update Statistics <Index Name> with Sample 5 Percent
Please note that though update statistics does not cause blocking, if may cause indirect impact by utilizing disk resources.
Are there any other cases where indexes may return inconsistent results, if so please respond to this and let us compile a large list to republish.
If needed more info about optimizer let us know and we would be glad to explain in detail as this is a Tip we have restricted only to a few thousand words.
As usual, we are open to respond through mail and feel free to send mail to firstname.lastname@example.org