Unlike fine wine, database statistics do not improve with age. I recently helped out with a client who was having issues with poor query performance on a SQL Server 2005 instance on very good hardware. After having them run my standard SQL Server 2005 Diagnostic Information queries, nothing glaringly obvious was jumping out at me. Sure, there were a couple of missing indexes that needed to be added, but overall query performance was still pretty bad. The server as a whole was not under obvious CPU, memory, or IO pressure.

The database in question had both Auto Create Statistics and Auto Update Statistics enabled. The client also told me that they had a maintenance job that rebuilt all of the indexes in the database once a week, during a maintenance window.

After some thought, I had them run a query to check the STATS_DATE for every index in the database, along with whether no_recompute was set for each index. It turned out that no_recompute had been turned on for about 20% of the indexes in the entire database (including most of the more important tables), and that the statistics on these indexes had not been updated in over two months. This was with an OLTP workload, with very volatile tables.  That index-level setting overrides the database level Auto Update Statistics setting for each individual index. This is generally a bad thing to do in most cases.  An exception would be if you had an extremely volatile index that was changing so rapidly that auto update statistics could not keep up with the changes, and you decided to manually update statistics very frequently from an Agent job.

The two queries I had them run are shown below:

-- SQL Server 2005/2008 Statistics Queries
-- Glenn Berry 
-- August 2010
-- http://glennberrysqlperformance.spaces.live.com/
-- Twitter: GlennAlanBerry
-- When were Statistics last updated on all indexes?
SELECT o.name, i.name AS [Index Name],  
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], 
       s.auto_created, s.no_recompute, s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id] 
AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;    
-- Find indexes with no_recompute turned on
SELECT o.name, i.name AS [Index Name],  
       STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], 
       s.auto_created, s.no_recompute, s.user_created
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id] 
AND i.index_id = s.stats_id
WHERE o.[type] = 'U'
AND no_recompute = 1
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;    

After discovering this, I had them run sp_updatestats on that database. That system stored procedure updates all of the statistics (both index statistics and system generated statistics) that need to be updated, with the default 10% sampling ratio. After the sp_updatestats command finished, we noticed much better query performance, and a drop of roughly 10% in average CPU utilization for the instance.

The next step was to rebuild each of those indexes, setting STATISTICS_NORECOMPUTE to OFF. A sample of how to do this is shown below. Notice that the ONLINE option is turned on, which only works in Enterprise Edition.

    -- Rebuild an Index with STATISTICS_NORECOMPUTE  = OFF
    ALTER INDEX [IX_UserAccount_Active_UserID_UserName] 
    ON [dbo].[UserAccount] REBUILD  
    WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
    ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, 
    ONLINE = ON, SORT_IN_TEMPDB = OFF);
    GO

One alternative would have been to just change the property setting for that index, like you see in the example below. Rebuilding the index with the proper statistics no_recompute setting will also do an UPDATE STATISTICS WITH FULLSCAN, so I thought that was a better long-term solution.

-- Set the STATISTICS_NORECOMPUTE option Off
ALTER INDEX PK_FeedInfo      
ON dbo.FeedInfo       
SET (STATISTICS_NORECOMPUTE  = OFF);

These queries and commands work the same way in SQL Server 2005, 2008 and 2008 R2.