Is it bad practice to automate the updating of statistics and re-making of query plans on a relatively small SQL Azure database?

Posted on

Question :

I have a SQL Azure database that’s about 500MB in size. Every 2-3 weeks the database will suddenly start to run slow and some queries (generated by LINQ) will timeout. There is no gradual increase in execution time, it just suddenly spikes.

The only way to fix it is to update the statistics; I usually delete the query plans at the same time. It then goes back to normal.

Some of the indexes were wrongly created with STATISTICS_NORECOMPUTE=ON at the beginning. Since changing them to OFF, the issue happens less frequently. However, when I look at the indexes and table-creation SQL, nothing is unusual and nothing is unusual about the system. There’s new rows being added daily but nothing unusually large or small. I read that statistics only update the 20%+500 of the table has been changed, but I have other similar databases running without issue.

So, if I scheduled the update of statistics every few days at midnight, am I treating the symptom whilst ignoring the elusive cause? Is it bad practice or is this standard database maintenance?

Answer :

SQL Server optimiser uses statistics to generate execution plans. The more accurate statistics the better plans it can potentially generate. By default statistics automatically updated only if more than 20% of the records have been changed. What does it mean in practice? Let’s say you have a table with 10 months of data and statistics was just recalculated. It will take two months to trigger update statistics again. If during this period somebody runs a query asking for some data in that 2 month period optimiser will think that there will be no records matching the condition and may generate a very bad execution plan.

Outdated statistics is the root cause of the performance issues not a symptom therefore it should be updated regularly.

Reorganize/rebuild is not a replacement for update stats job. Reorganize doesn’t update stats at all. Rebuild doesn’t update all the stats. Only index stats are updated during rebuild not the column stats.

If auto update statistics is not enough for your DB, try a couple of things:

Leave a Reply

Your email address will not be published. Required fields are marked *