Suppose that we use the following standard backup strategy :
Backupset : ( 3:Sun/5:Mon/7:Tue /9:Wed/etc)
Suppose now that we hit a media failure at the end of the week (Saturday night for example) and when trying to recover the database we receive the following error :
This is a case i was working on a few days ago.I was asked to tune a specific query on our production server, so here is a quick test case to model the problem (i only kept the interesting part of the original query which was much more longer)
Unused Index although they seem not needed , dropping them must be carefully tested as it can have some side effect. As explained by Jonathan Lewis, due to index sanity check cardinality miss estimate can happen after dropping the unused index.
“The reason for this is the “index sanity check”. When the optimizer is doing its cardinality estimates, if it see equality conditions on the set of columns that make up an index it can use the distinct_keys statistic from the index in the calculation rather than using the standard calculation of multiplying together the num_distinct of the separate columns. ”
There is also a good example of the impact this can have on the query execution plan(switching join method from hash join to nested loop due to the drop of the estimated number of rows returned).Link
In this post i will show another interesting case when this can have an impact on query performance.As Franck Pachot Said :
Hatem Mahmoud's blog 