In order for the SQL Server Query Optimizer to make good decisions, it must have up-to-date statistics on the tables and indexes in your database. For each table in your database, SQL Server automatically maintains this information on them:
- The number of data pages used by each table.
- The number rows in each table.
- The number of INSERTS, UPDATES, and DELETES that affect the keys of the table since the last statistics update.
Besides the table statistics, SQL Server can maintain statistics on all of the indexes in your database. I say “can” because you can stop maintaining index statistics if you want, but that would not be a good idea as the Query Optimizer needs current statistics in order to make good query optimization decisions. SQL Server maintains this index statistics information:
- A histogram of the distribution of the data in the first column of the index.
- The densities of all column prefixes.
- The average key length of the index.
SQL Server can also collect the above statistics (which is normally only collected for indexes) for any column you specify. This data can be used by the Query Optimizer to make better decision. Column statistics aren’t automatically collected unless you tell SQL Server to collect them.
In most cases, you will probably allow SQL Server to update index statistics automatically. This feature can be changed by setting the database option “Auto Update Statistics” to either true of false. Be default, this feature is set to true, which means that index statistics are automatically updated.
But the question might be, when does the auto update index statistics feature turn itself on? In other words, how does SQL Server know when to update statistics? SQL Server follows a very specific set of rules on when it should update the statistics of an index. Here they are:
- If the number of rows in a table are greater than 6, but less than or equal to 500, then statistics are automatically updated when there have been 500 modifications made.
- If the number of rows in the table are greater than 500, then updates are automatically made when (500 plus 20 percent of the number of rows in the table) have been modified.
If you like, you can check to see how many modifications have been made to a table and at the same time estimate when an automatic statistics update will occur. If you go to the sysindexes table of the database in question, and look at the rowmodctr column, it will show you what the count is. From this number, you can estimate when the next automatic update of statistics will occur.
*****
To provide the up-to-date statistics the query optimizer needs to make smart query optimization decisions, you will generally want to leave the “Auto Update Statistics” database option on. This helps to ensure that the optimizer statistics are valid, helping to ensure that queries are properly optimized when they are run.
But this option is not a panacea. When a SQL Server database is under very heavy load, sometimes the auto update statistics feature can update the statistics at inappropriate times, such as the busiest time of the day.
If you find that the auto update statistics feature is running at inappropriate times, you may want to turn it off, and then manually update the statistics (using UPDATE STATISTICS or sp_updatestats) when the database is under a less heavy load.
But again, consider what will happen if you do turn off the auto update statistics feature? While turning this feature off may reduce some stress on your server by not running at inappropriate times of the day, it could also cause some of your queries not to be properly optimized, which could also put extra stress on your server during busy times.
Like many optimization issues, you will probably need to experiment to see if turning this option on or off is more effective for your environment. But as a rule of thumb, if your server’s resources are not maxed out, then leaving this option turned on is probably the best decision.
*****
If you have a database that acts as a datamart or data warehouse, set the “read only” database option to true. This will turn off locking and greatly speed queries against the data.
If you do make your database “read-only, be sure to manually update the database’s statistics first. This is especially important for SQL 7.0 and 2000 as “Auto Update Statistics” is turned off when a database is set to “read only.”
*****
The Auto Update Statistics database option is a great feature of SQL Server 7.0 and 2000, at least in most cases. In some very busy SQL Servers, this feature can interfere with normal daily activity. This is because this feature can “kick in” at times when the server is already very busy, degrading performance. In these cases, it is often better to turn this feature off and to manually or schedule an update statistics during lulls in database usage.
So how do you find out if your SQL Server is performing an auto update? One of the best ways is to use the SQL Server Profiler. If you are using SQL Server 7.0, you must go to the Profiler’s menu and click on Tools, then Options. On the General tab, select “All Event Classes”. You must do this in order to see the Auto Update Stats sub-event when you create the trace. If you are using SQL Server 2000, you don’t have to perform this step.
Now it is time to create your trace. In SQL Server 7.0, you need to go to the the Events tab, open the Misc. event, and then select the Auto Update Stats sub-event. In SQL Server 2000, open the Objects event, and then select the Auto Update Stats sub-event. You can also choose any other events and Data Columns you want displayed as part of your trace. I would recommend that you sort your results by Event Class so that it will be easier to identify and view the Auto Update Stats event.
Run this trace for a day and see how many auto update statistics events you have. If you find that they occur often, and/or if they take a long time to run (the time it takes for the update to run is listed under the Duration column in the Profiler window), and/or if it runs during very busy times of the day, then you may want to consider updating statistics manually instead of automatically using the Auto Update Statistics database option.
*****
One way to determine how often that AUTOSTATS runs in SQL runs is to use the trace flag 8721. When turned on, it outputs to the errorlog every time AUTOSTATS runs. This information can be used to help determine if AUTOSTATS is running too often or not. Be sure to turn off this trace flag when you are done with it, as it can produce unnecessary overhead.
]]>