March Madness – SQL Azure – UPDATE STATISTICS

Yesterday we looked at a handful of queries that you can use with the sys.dm_exec_query_stats system view to return information about the activity on your instance of SQL Azure. Today we will talk about one way you can help yourself to improve performance for your SQL Azure instance: updating statistics.

If you check the list of supported statements in SQL Azure you will see that there are a handful of useful commands specific for statistics (CREATE, UPDATE, DBCC SHOW_STATISTICS). Now, SQL Azure has the AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS database options enabled by default (and you can’t disable them), but there are still going to be times when you will want (or need) to manually update the statistics inside your SQL Azure database, same as you need today for your on-premise version of SQL Server.

[For those of you that want to disable the auto updating of statistics for your index you can use the NORECOMPUTE clause to get that done but I have never come across a case where you would want to do this. Not saying it isn’t possible, just that I have never seen it, nor do I read about such edge cases very frequently. Also, that statement is due to be removed from a future version of SQL Server. Just sayin.]

To update the statistics for all indexes on all tables in your SQL Azure database you can run the following:

EXEC sp_updatestats

You can also update statistics for just one table by using the following command:

UPDATE STATISTICS dbo.Table

You can also update statistics for just one index by using the following command:

UPDATE STATISTICS dbo.Table index_name

The real question you should be asking yourself now is: “Self, when would I want to be manually updating statistics, since SQL Azure should be doing that for me automatically anyway?”

Great question. Here are the most common reasons for why you would want to manually update statistics.

1. When queries suddenly start running slowly for no specific reason

This is usually the first thing that most DBAs will do when queries start taking longer than expected. And it is often the most misunderstood as to why it often works. If your queries are suddenly taking longer and an update of statistics fixes the issue then there is a really good chance you haven’t fixed anything. This is because your issue is likely due to parameter sniffing, the fact that the statistics update caused the query to recompile, and the very next query put a plan into cache that is better suited for the majority (but not all, obviously) of the other queries using the same plan.

2. After you have modified a large percentage of data in a table

For example, after maintenance operations where you do a large bulk insert or update, or perhaps even after a truncation. Before your users start running queries again you may want to do an update of the statistics yourself manually. Doing so will save your end users the recompilation time that will happen when their queries hit the data and SQL Server decides it needs to do an auto update because you haven’t done the update manually yet. And another case is where you insert some data, but not enough to trigger the auto updating of stats, and then users are going to be selecting that same data. Those stats won’t be used when the query optimizer builds a good enough plan, so you should consider updating them yourself then.

Tomorrow we are going to look at another way for you to help make improvements for query performance in SQL Azure.