Ensuring that statistics are up-to-date is a critical maintenance task because statistics are used by the query optimizer to choose an optimal query plan. Poor statistics can result in poor query plans, which can result in poor performance of both the individual query and the overall server. If a poor query plan is chosen in my environment, which has a federated farm containing both large and small clients on my multidatabase servers, a spike in CPU usage can occur (often read: CPU pegged at 100 percent).
Like many of you, I always ensure my indexes are properly maintained. I rebuild and reorganize indexes whenever I have the opportunity and have the auto update statistics asynchronously option enabled for any statistics that might not have been updated during an index rebuild. In theory, based on the rebuilds and frequency of data changes triggering automatic statistics updates, the statistics in my environment should be up-to-date. However, even with all of these measures in place, there’s still the possibility that my statistics could be out-of-date because of how the auto update statistics option functions. Thus, I set out to find a better way of ensuring that statistics are fresh.
The initial requirements for my process were pretty typical: I wanted to ensure statistics were updated efficiently and with the least amount of impact on my server as possible. I created the IntelligentStatsUpdater stored procedure to accomplish this task. Let’s take a look at this stored procedure and its parameters. (You can download IntelligentStatsUpdater.sql by clicking the 103405.zip file at the top of the page.)
The Graduated Update Scale
Simply put, executing IntelligentStatsUpdater will locate out-of-date statistics based on input parameters and update them using a Graduated Update Scale to determine an appropriate statistics sample percentage for the table size. Line 590 of IntelligentStatsUpdater.sql, which Figure 1 shows, is the case statement that constitutes the Graduated Update Scale.
I chose to use a case statement because it’s easy to read and modify.
You must use caution when updating statistics using the FULLSCAN option on large tables because it’s performance-intensive and comes with the inherent potential for blocking while statistics are gathered. Large tables that have millions of rows might need only a small percentage to sample for proper statistics. That’s why I based the update statistics sample percentage on the number of rows in a specific table.
The scale I use is based on what I know works given the data and high transactional volume in my environment. Although you can use the scale right out of the box, the frequency and the degree to which you update your statistics is dependent on your environment. I encourage you to explore and tweak the conditions to suit your needs.
IntelligentStatsUpdater’s Input Parameters
The IntelligentStatsUpdater stored procedure includes several features that are controlled by input parameters to target statistics and databases and limit the impact on the server. The first input parameter is @DaysOlderOutDated. This is the number of days equal to or after which you consider statistics to be out-of-date. For instance, if you assign @DaysOlderOutDated a value of 7, any statistics with a date that’s seven days or older than the current date will be updated. If you want to target or exclude specific databases on every run, @DBInclude and @DBExclude, which are comma-delimited strings of databases to either include or exclude, can be very helpful.