1

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?

2

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.

3

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

  • Great answer, thanks for the help. – MSOACC Aug 16 at 16:28
  • I assume that SQL Azure database doesn't need the Flag 2371 because it's running on the latest version possible. – Alexander Suprun Aug 16 at 16:50
  • Alexander - I think you’re right. I haven’t used Azure but wasn’t sure with which services SQL Server is maintained (to the latest version) by Microsoft, and when the client’s local version of SQL Server is essentially hosted on Azure infrastructure. – JShark Aug 17 at 5:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.