Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How Old Are Your Database Statistics?

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.

image

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.

Comments

Posted by Jason Brimhall on 31 August 2010

Great scripts Glenn

Posted by jts_2003 on 1 September 2010

Very useful - using Glenn's scripts I have found a scary number of statisticss which haven't been updated since out upgrade to SQL Server 2008 three months ago.

Posted by donaldlindo on 2 September 2010

Im using an app called sql defrag which shows a very high number of fragmentation on index that are changed frequently, i will check if no_recompute was set for each index, this option is not revealed in the app, thanks

Posted by ctps833 on 2 September 2010

Nice scripts.  One other side effect that should be considered is to mark all the stored procedures to be recompiled on their next execution.  Even with the new stats, the query plans on stored procedures may not take full advantage of them.

'>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<

DECLARE @ProcName sysname

DECLARE @SQLSTMT  varchar(2000)

DECLARE ProcCursor CURSOR FOR

select name, * from sys.sysobjects  

where xtype = 'P' AND category <> 2

OPEN ProcCursor

FETCH NEXT FROM ProcCursor

INTO @ProcName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQLSTMT = 'sp_recompile ' + '[' + @ProcName + ']'

EXECUTE (@SQLSTMT)

FETCH NEXT FROM ProcCursor INTO @ProcName

END

CLOSE ProcCursor

DEALLOCATE ProcCursor

'>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<

Posted by wim.buyens on 2 September 2010

You can create a maintenance plan for all databases to do Update of statistics on all existing statistics.

We run it every night. No recompute is off on all our indexes.

Greets.

Wim.

Posted by Ninja's_RGR'us on 2 September 2010

Moreover, I have an ERP that is screwing this up... only 10% of the stats were ever updated.  so MANY thanks

And since all the queries are adhoc, I had to run this on the prod server... good thing we are on a slow period of the year :

DBCC FREEPROCCACHE()

That'll recompile everything since nothing compiled is left on the server :-P.

Posted by Glenn Berry on 2 September 2010

If you have a static or nearly static table, the statistics will not need to be updated that often. This is more of an issue with an OLTP workload.

Posted by Aldo Gonzalez on 2 September 2010

Glenn,

Thank you for the scripts.  Please explain why not just update the statistics for the index (UPDATE STATISTICS tablename indexname) instead of rebuilding it.

Thanks.

Posted by Charles Kincaid on 2 September 2010

Aldo;

I suspect that the rebuild gets rid of index fragmentation at the same time as updateing the statistics.

Posted by Glenn Berry on 2 September 2010

You can just do an UPDATE STATISTICS TableName WITH FULLSCAN, but rebuilding the index does the same thing, plus it will get rid of any fragmentation that may exist.

Posted by Aldo Gonzalez on 2 September 2010

Thank you Glenn.  Rebuilding an index is a much more expensive operation than just updating the stats of the index. This is specially true for large indexes that are also heavily used. If fragmentation does not demand a rebuild, then a quick update stats will do.

Thank again.

Posted by Glenn Berry on 2 September 2010

Aldo,

I agree that rebuilding an index is relatively expensive, and should not be done unless the level of fragmentation is high enough to warrant it.  I am definitely against blindly rebuilding all indexes regardless of their fragmentation level.

On the other hand, with a relatively small index, on newer hardware, rebuilding an index can be a pretty trivial operation.

Posted by Aldo Gonzalez on 2 September 2010

I agree.

Thanks again.

Posted by pamat on 6 September 2010

thank you!

Posted by osgcurt on 6 September 2010

I really liked this piece Glenn.  

Often I see responses that the Maintenance plan wizards do all of this work too.  I don't agree.  Too often I see some VAR's Consultant run every possible Mainetance job on all the databases in an instance every night.

Your in depth knowledge of SQL shows through.

I will share this with my "Errant" clients.

Thanks.

Posted by Glenn Berry on 7 September 2010

Thank you for your kind words, osgcurt.

Posted by Mike on 7 September 2010

Nice job.

I added a join with a quick row count so I could visually verify any indexes that came back with NULL statistics_date had zero rows.

-- 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, st.row_count

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

inner join sys.dm_db_partition_stats as st with (nolock)

ON o.[object_id] = st.[object_id]

WHERE o.[type] = 'U'

ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC;    

Posted by Glenn Berry on 7 September 2010

Nice addition, sql2kdawg.

Posted by Unsy on 9 September 2010

Hi Glen

A great addition to my housekeeping routines

Just a couple of points to enhance your fine script

Ive been been tripped up before when the same table name is used across different schemas - so I've added

SCHEMA_NAME(o.Schema_ID) + '.' + o.NAME AS 'ObjectName',

to my version

Also is there a reason why you exclude Views. do you know how Statistics can be Updated on  Indexed Views. I can only seem to Update them by rebuilding

Cheers

Posted by balram on 9 January 2013

Nice Glen, The query realy helped to understand the subject.

Leave a Comment

Please register or log in to leave a comment.