At my job I often help customers with Performance tuning their SQL Server environments and their application. One of the common recommendation we give is to check their maintenance plans and see if the Statistics of tables are up-to-date. This is a very common exercise and given that you have 1000s of tables to monitor, you might want an easy script to figure out which are those tables / indexes that need attention. I am giving a simple script that we use at our environments to identify the same.
OBJECT_NAME(ind.object_id) AS ‘Table Name’,
ind.name AS ‘Index Name’,
STATS_DATE(ind.object_id, ind.index_id) AS ‘Statistics Date’
FROM sys.indexes ind
ORDER BY 3 DESC
With the above script the output would be as below:
Look at the “Statistics Date” column and this gives you information of how dated the statistics are. Once you have figured out the statistics are out-of-date by few weeks or months, use the UPDATE STATISTICS command to update the same. Yet another command used for this purpose is sp_updatestats.
Also, even if you have AUTO_CREATE_STATISTICS, AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC (Statistics documentation) options enabled inside your database Statistics *can* be out-of-date. So analyze your environments from time-to-time and then come up with your updating strategy.
This entry was posted on Tuesday, October 30th, 2012 at 20:50 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.