Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

How to Find Outdated Statistics in SQL Server 2008

MSSQLTips author Tibor Nagy By:   |   Read Comments (7)   |   Related Tips: More > Indexing
Problem

In the previous tip we learned that outdated statistics can cause SQL Server performance problems. My question is: How can I find out which statistics are outdated in my SQL Server database?

Solution

We can use the STATS_DATE (object_id, stats_id) function to check when the last update happened for the given statistics. The input is the ID of the table and the ID of the statistics. This function returns the last update date in datetime format. In the next section I will show how to collect data about the outdated statistics into a table.

First, we will create a table for the results:

--Create a table for the outdated statistics
CREATE TABLE Outdated_statistics
([Table name] sysname,
[Index name] sysname,
[Last updated] datetime NULL,
[Rows modified] int NULL)
GO

Now we will query every statistics object which was not updated in the last day and has rows modified since the last update. We will use the rowmodctr field of sys.sysindexes because it shows how many rows were inserted, updated or deleted since the last update occurred. Please note that it is not always 100% accurate in SQL Server 2005 and later, but it can be used to check if any rows were modified.

--Get the list of outdated statistics
INSERT INTO Outdated_statistics
SELECT OBJECT_NAME(id),name,STATS_DATE(id, indid),rowmodctr
FROM sys.sysindexes
WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-1,GETDATE()) 
AND rowmodctr>0 
AND id IN (SELECT object_id FROM sys.tables)
GO

After collecting this information, we can decide which statistics require an update.

If we are sure that all out dated statistics should be immediately updated, then we can write a cursor to update them:

--Set the thresholds when to consider the statistics outdated
DECLARE @hours int
DECLARE @modified_rows int
DECLARE @update_statement nvarchar(300);

SET @hours=24
SET @modified_rows=10

--Update all the outdated statistics
DECLARE statistics_cursor CURSOR FOR
SELECT 'UPDATE STATISTICS '+OBJECT_NAME(id)+' '+name
FROM sys.sysindexes
WHERE STATS_DATE(id, indid)<=DATEADD(HOUR,-@hours,GETDATE()) 
AND rowmodctr>=@modified_rows 
AND id IN (SELECT object_id FROM sys.tables)
 
OPEN statistics_cursor;
FETCH NEXT FROM statistics_cursor INTO @update_statement;
 
 WHILE (@@FETCH_STATUS <> -1)
 BEGIN
  EXECUTE (@update_statement);
  PRINT @update_statement;
 
 FETCH NEXT FROM statistics_cursor INTO @update_statement;
 END;
 
 PRINT 'The outdated statistics have been updated.';
CLOSE statistics_cursor;
DEALLOCATE statistics_cursor;
GO

As you can see, you can define the thresholds as to when to consider the statistics outdated. You can run this as an ad-hoc query or you can create a stored procedure and execute it with a scheduled job as needed. Either way, after you execute this query, the statistics should be up to date.

Next Steps


Last Update: 8/3/2012


About the author
MSSQLTips author Tibor Nagy
Tibor Nagy is a technical and business professional from the financial industry. His experience includes SQL Server 2000-2008, DB2 and MySQL.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, August 03, 2012 - 8:13:03 AM - Van Heghe Eddy Read The Tip

Hi Tibor,

Nice post and was really helpfull to,
I have been taken the permission to alter your code a little bit so it would also serve for those who are using different 'schemas'  whereas your code would fail in those cases.

Altered code:
SELECT OBJECT_NAME(id) AS Tabel ,st.[name] AS Tnaam, si.name,STATS_DATE(id, indid) AS DateLastUpdate,rowmodctr,st.*
FROM sys.sysindexes AS si INNER JOIN sys.[tables] AS st ON si.[id] = st.[object_id]
INNER JOIN sys.schemas AS ss ON st.[schema_id] = [ss].[schema_id]
WHERE STATS_DATE(id, indid)AND rowmodctr>10
AND st.[type] ='U' -- user tables only
ORDER BY [rowmodctr] DESC

And the new update statement for your cursor:
--Update all the outdated statistics
DECLARE statistics_cursor CURSOR FOR
SELECT 'UPDATE STATISTICS ' + ss.[name] +'.'+ OBJECT_NAME(id) + ' ' + si.name
FROM sys.sysindexes AS si INNER JOIN sys.[tables] AS st ON si.[id] = st.[object_id]
INNER JOIN sys.schemas AS ss ON st.[schema_id] = [ss].[schema_id]
WHERE STATS_DATE(id, indid)AND rowmodctr>=@modified_rows
AND st.[type] ='U' -- user tables only

Still have 1 question for witch i don't seem to find an answer:
Some of my tables have multiple statistics al starting with ' _WAxxxxxx_SYS'
For example i have a table with 2 indexes (1 clustered PK, 1 non-clustered) and i find 12 statistics
Is this normal or could it be the left over from old indexes.?

Wkr,

Eddy

 

 


Friday, August 03, 2012 - 9:00:12 AM - Martyn Read The Tip

As I understand, stats are created automatically (if enabled) to help with query plans. They can also be added after analysing trace data. They are used in query planning so just because there is no index doesn't mean it should go.... at least that's what I understand to be correct, feel free to correct me if I'm wrong.

I'd be interested to know how to check how/when the stats are used, similar to index useage. If stats exist but aren't used, presumably they can be cleared. Some code to provide this information, with a related drop statement, allowing confident and easy removal would be fantastic!

Thanks

Martyn

 


Friday, August 03, 2012 - 4:34:32 PM - Tibor Nagy Read The Tip

Hi Eddy,

Thanks for the updated code.

The _WA_SYSxxx statistics are automatically created by the Query Optimizer for the columns which do not have existing statistics. It is an estimation which helps to choose between the possible Query Plans when the statistics do not exist. I already submitted a tip on this topic and it will be published soon :)

Regards,

Tibor


Thursday, August 09, 2012 - 7:08:01 AM - Tibor Nagy Read The Tip

Hi Eddy,

Here is my tip about _WA_SYSxxx stats: http://www.mssqltips.com/sqlservertip/2734/what-are-the-sql-server-wasys-statistics/

 


Sunday, September 09, 2012 - 11:36:31 PM - Pete Read The Tip

sp_updatestats was introduced in SQL Server 2005 - does it all for you

 

http://msdn.microsoft.com/en-us/library/ms173804(v=sql.90).aspx

 

To run it on all databases:

 

sp_msForEachDB 

'

use [?];

print ''-----------------------'';

print ''begin updating statistics for [?]'';

execute sp_updatestats;

print ''end updating statistics for [?]'';

'

 


Tuesday, May 14, 2013 - 8:56:54 AM - Igor Micev Read The Tip

Hi

In your code intended for forming the "UPDATE STATISTICS" strings you do not have the schema name. That would not work on databases with different schema from the default ('dbo').

You can make it as the following:

SELECT 'UPDATE STATISTICS '+schema_name(so.schema_id)+'.'+OBJECT_NAME(id)+' '+ssi.name
FROM sys.sysindexes ssi
join sys.tables st on ssi.id=st.object_id
join sys.objects so on ssi.id=so.object_id and st.object_id=so.object_id
WHERE STATS_DATE(id, indid)
AND rowmodctr > 0 

 

Regards

IgorMi


Friday, April 11, 2014 - 7:02:12 AM - Parker Read The Tip

My goodness. This is outdated and a now deprecated methodology, yet was sent out in their newsletter today. Wild.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.