Update statistics causing negative performance
Recently we have seen a negative performance for a query that is running against a 500GB size of database, where AUTO UPDATE STATISTICS is enabled and we perform weekly reindexing on this database. In addition to this I perform intermittent update statistics against set of tables that has frequently update/deletes.
As usual I have checked for TEMPDB contention during this query operation, nothing found wrong. Event Transacton log backup is continuing very well. Then realized that this could be a total fragmentation issue on the indexes, as the data is frequently updated due to the nature of application transactions. The default behaviour of SQL engine is that when the statistics are updated the optimizer might then decide not to use your indexes anymore (or scan the whole index), which will result in the slow performance of getting results with a time increase.
Obviously the performance is far better during Mondays and gets inferior by Thursday! This is due to the weekly reindexing job runs every Sunday morning.
So whenever you get struck in such a situation then first thing is to check for estimated execution plan, even though that particular query is frequently used. Due to the nature of index fragmentation this will fail the performance even to return simple set of rows. Further if you want to know how to detect the table fragmentation refer to this BlogPost.
**__________________________________**
SQL Server MVP, Sr. DBA & industry expert.
-
Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.