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?