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?
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:
Manually update statistics – recommend Ola Hallengren’s scripts that can be configured to maintain indexes and/ or statistics as required. Test different parameters (all/ indexes only/ sample rate etc) and test running daily/ weekly/ at start of proc and the like, and see what works best for you with least overhead.
Scripts are here: https://ola.hallengren.com/
Brent Ozar article about updating stats only with those scripts:https://www.brentozar.com/archive/2016/04/updating-statistics-ola-hallengrens-scripts/
Enable Trace Flag 2371 (if SQL Server version older than 2016)
For large tables, this reduces the threshold for automatically updating statistics dynamically. This algorithm is used as standard since SQL Server 2016.
Good further reading: