Have you ever experienced that suddenly a query that used to run fast from night to day started to run for hours? Well, I guess when there have been no releases that the first thing that you will be starting to look at are the indexes. Using a tool like Red gate Sqlserver toolbelt can be really handy comparing datamodel but when you discover that the database structure objects look all the same what do you do?
You start comparing execution plans and you discover that the execution plan looks completely different. How is that possible when the database structure has not changed? The answer is: statistics….
Probably somebody rebuild and index and that made the statistics on columns that were not part of that specific index but ARE part of other indexes STALE.
The golden rule is:
Index rebuild will only update the stats on the specific index column. Other stats created on other index columns will never get updated. Index reorg will not update any stats.
So… you must UPDATE STATISTICS the specific table so that all columns’ statistics will be recalculated. You will notice that after doing this your query will run fast as usual again.