One of the biggest call generators at Microsoft SQL Server Support when SQL Server 7.0 was released was issues with statistics. In the beginning, they tended to be about the Autoupdate statistics feature interfering with user activity but when SQL Server 2000 came, and systems running SQL Server became larger, issues with skewed statistics/data and problems related to the sample rate used when gathering statistics emerged and in 2005, the challenges are the same. There are some new statistics features in 2008 but overall, this post applies to 2008 as well.
First a few words on the symptoms that are typical for issues related to statistics. What they all have in common is seemingly strange variations in duration when the server has no resource problems and you have confirmed that there is no blocking:
- The same query (with the same values in the search argument or WHERE clause) varies in duration without any related blocking or resource problem
- Some queries degrade in performance as time passes since the last Index rebuild
- The same procedure will perform different depending on what values are passed into it
- Intermittent performance problem on some tables without any related blocking or resource problem
Although all of these symptoms may be related to other issues, I always look into the quality of the statistics before I spend time on more time consuming troubleshooting.
Background - Statistics Default Settings
Even though the default settings (which is Autoupdate and Autocreate Statistics ON for all tables and indexes), will take you a long way, it is absolutely necessary to have a more granular approach as your databases and your workload grows. There is no tuning available that can work around a problem with lack of accurate statistics. The problems related to statistics (or lack thereof) are either about:
- How often the statistics are updated
- The sample rate used when the statistics is built
One thing that you might not have thought about is when Autoupdate statistics will be triggered. A lot of people will say it is when a certain threshold for changes is reached (more details below) but that’s not the whole truth – it does not automatically start when the threshold is reached. It is part of the compilation or optimization process and will only be triggered when/if the threshold has been reached AND the index/column is touched by a query. The thresholds are really Recompilation thresholds and not Statistics thresholds.
Simplified it works like this:
Picture based on diagram in http://technet.microsoft.com/en-us/library/cc966425.aspx
There are some other processes that will trigger an update of the statistics. And not only that, it will trigger an update of the statistics WITH FULLSCAN (i.e. using all rows in the index to build statistics as opposed to Autoupdate statistics which will use some sample rate):
- DBCC REINDEX (not INDEXDEFRAG)
- CREATE INDEX …. WITH DROP_EXISTING
If you do any of the above on an index, you will get the best statistics possible and it is not too uncommon to see cases when switching from Index Rebuild to UPDATE STATISTICS … WITH FULLSCAN, gives the same performance boost. It is also important to know that once the Autoupdate statistics process runs, it will reduce the sample rate of the statistics down to the default.
If your maintenance window is getting crowded, and index rebuild is part of your maintenance plans, you might want to experiment with a combination of index rebuilds and statistics updates (needless to say updating statistics is not going to do anything for fragmentation should that be an issue).
Now to the reasons why Autoupdate statistics might not work well in all situations:
1. Statistics gets out-of-date or stale
This is a problem related to large tables. What happens is that it takes more and more modifications for the Recompilation Threshold to be met. The formula for tables with more than 500 rows is:
Rowmodctr 500 + 20% of total rows (rowcnt)
(the two columns, rowmodctr and rowcnt, can be found in the sys.sysindexes compatibility view).
The problem following this, is that the Optimizer will have somewhat misleading information about the cardinality of tables and indexes (i.e. a WHERE or JOIN clause returns more or less rows than expected).
2. Again for large tables, the default sample rate (used by the Autoupdate Statistics feature) may not be enough because it gets relatively lower as the tables grow larger (to avoid issues with Autoupdate Statistics process taking too long).
3. Data in the column is naturally "skewed" (e.g. LastName; Smith vs. Redei for instance)
4. The Autoupdate Statistics process interrupts your user activity
How do you diagnose and address these issues? In this post I will explain how to do that for the first 2 items in the list above; stale statistics and problems with default sample rate.
To diagnose, you would typically look at two things:
- When is the last time the statistics were updated?
- What is the ratio rowcnt/rowmodctr (for tables/indexes with rowcnt > somenumber)?
The solution to this is quite simple; you just need to schedule manual UPDATE STATISTICS jobs on the tables when a threshold that you deem reasonable is met:
, object_name(ssi.id) AS tblName
, stats_date(ssi.id,ssi.indid) as StatsDate
, cast(ssi.rowmodctr as decimal)/cast(ssi.rowcnt as decimal) as ChangedRowsRatio
, ss.no_recompute AS IsAutoUpdateOff
FROM sys.sysindexes ssi left join sys.stats ss
ON ssi.name = ss.name
WHERE ssi.id > 100
AND indid > 0
AND ssi.rowcnt > 500
AND (ssi.rowmodctr/ssi.rowcnt) > 0.15 -- enter a relevant number
ORDER BY 3
You would probably want to change the ssi.rowcnt and the ratio to something that is relevant for your database.
The output will look something like this:
In this case, I have some statistics on a non-clustered index (indid > 1) which has not been updated for 3 years. The IsAutoUpdateOff = 0 tells me that Autoupdate statistics feature is ON for this particular index (this can also be viewed, or changed, with the sp_autostats procedure).
Once you have confirmed that stale statistics is an issue, you have two options:
- Update the statistics for all indexes in the database using sp_updatestats (if Autoupdate statistics is turned on, there is no point in using the ‘resample’ of option of sp_updatestats)
- Update statistics only for the tables you have identified with UPDATE STATISTICS (use UPDATE STATISTICS <tablename> WITH ALL if you want to update all indexes of a table)
Low Sample Rate
This is slightly trickier than the problem with stale statistics; both to diagnose and to solve. The way you would know that sample rate is an issue, is if a query that you think have suspiciously high duration runs faster after you have run an UPDATE STATISTIC ... WITH FULLSCAN on it (or if you did an Index rebuild that gave you statistics with a full scan).
It involves finding the point when the sample rate is high enough to create useful statistics but low enough for the process of updating statistics to fit into your maintenance window. You also need to decide how often the job should run.
Your starting point should be the current sample rate which you will find with DBCC SHOW_STATISTICS:
DBCC SHOW_STATISTICS ([schemaname.tablename], indexname) WITH STAT_HEADER
Since this output cannot be presented with table results, I have attached a script that will present the output from DBCC SHOW_STATISTICS for all indexes for all tables and schemas in the current database. The output will look something like this:
What you want to look for is large discrepancies between Rows and RowsSampled (in the above output a full scan has been used to create the statistics so the numbers are equal).
As mentioned, for the large tables where you believe that this might be an issue, you should start with some number above the default sample rate (RowsSampled/Rows) to get an idea of how long it will take to update the statistics with a certain sample rate. Then move the sample rate up until the execution time of UPDATE STATISTICS hits the threshold given by your maintenance window.
Once you have found your ideal sample rate, you then need to set up jobs to run UPDATE STATISTICS ... WITH SAMPLE RATE XX, NO_RECOMPUTE and turn off the Autoupdate feature with:
EXEC sp_autostats ‘tablename’, ‘OFF’
If the NO_RECOMPUTE clause is omitted, Autoupdate statistic will be switched on and the next time that particular index passes the Recompilation Threshold, Autoupdate Statistics will be run with the default sample rate (which you have just concluded is not enough to provide the optimizer with accurate statistics).
Next post I will show you how you can diagnose issues with “skewed” data/statistics.