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

How Stale Are My Statistics?

Unlike fine wine, you typically wouldn’t want your statistics to be aged. At least for tables that are being updated frequently! A while ago, I showed you how to find out what indexes were on a table using the GUI. Then, I showed you how to generate a listing of indexes for a table or for the entire database using a query. Today, I am going extend those queries to include when the statistics were last updated. As in my prior example, the scripts shown below will use the AdventureWorksDW2008R2 and is available on Codeplex.

 

These queries are nearly identical to the ones previously written about, but now I am using the STATS_DATE system function. I am not going to rehash what MSDN states about the STATS_DATE function, but just know that it “returns the date of the most recent update for statistics on a table or index view”. The query optimizer will attempt to use the best query that it can to return the quickest results. If the indexes are stale (have not been refreshed lately), then it may not be using the best index and/or be less efficient. As such, it is good to know how stale the statistics are when performance tuning.

 

Script 1 below generates a list of tables, their index names, type and the statistics date for every table within the specified database (AdventureWorksDW2008R2 in my case).

 

Script 1: List All Index Statistics in Current Database

USE AdventureWorksDW2008R2

GO

 

SELECT

            o.name AS TableName

            , i.name AS IndexName

            , i.type_desc AS IndexType

            , STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate

FROM

            sys.indexes i

            JOIN sys.objects o ON i.[object_id] = o.[object_id]

WHERE

            o.type = 'U'     --Only get indexes for User Created Tables

            AND i.name IS NOT NULL

ORDER BY

            o.name, i.type 

 

Figure 1: Sample Results – ALL Tables in Database

Index Statistics for All Tables  

 

 

 

If you only want to look at how stale the statistics are for a particular table, then you can just apply a filter to the name in the sys.objects table like shown in listing 2.

 

Script 2: List Index Statistics for Specific Table

USE AdventureWorksDW2008R2

GO

 

SELECT

            o.name AS TableName

            , i.name AS IndexName

            , i.type_desc AS IndexType

            , STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate

FROM

            sys.indexes i

            JOIN sys.objects o ON i.[object_id] = o.[object_id]

WHERE

            o.type = 'U'     --Only get indexes for User Created Tables

            AND i.name IS NOT NULL

            AND o.name = 'DimProduct'

ORDER BY

            o.name, i.[type] 

 

Figure 2: Sample Results – Filtered Table Name

Index Statistics For Specified Table 

 

So, basically, if your query isn’t performing as well as you’d like, change the above query to use the IN clause and supply it all of the tables you are including in your query. J This may help you determine what is causing the lag time. I hope that you have enjoyed this quick blog. If you did, please rate it! Also, if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs.

 

 

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant – Pragmatic Works

Email: bmcdonald@pragmaticworks.com | Blogs: SSRSGeek | SQLServerCentral | BIDN

Twitter: @briankmcdonald | LinkedIn: http://tinyurl.com/BrianKMcDonald

 

 

 

Comments

Posted by Anonymous on 5 November 2010

Pingback from  @briankmcdonald posts How Stale Are My Statistics? | SQL Server Central | sqlmashup

Posted by SQL Noob on 8 November 2010

i ran this and found that a few statistics on an important table are 3 months old.

i haven't looked but i'm 99% sure that all the indexes on that table have been rebuilt at least a few times in that time frame.

any reason for this?

Posted by Dmitriy Burtsev on 8 November 2010

How about Indexed Views?

SELECT

           o.name AS TableViewName

           ,CASE o.type WHEN 'U' THEN 'Table' WHEN 'V' THEN 'View' ELSE 'n/a' END AS Type

           , i.name AS IndexStatisticsName

           , i.type_desc AS IndexType

           , STATS_DATE(i.[object_id], i.index_id) AS StatisticsDate

FROM

           sys.indexes i

           JOIN sys.objects o ON i.[object_id] = o.[object_id]

WHERE

           o.type IN ('U', 'V')    

           AND i.name IS NOT NULL

union all

SELECT

           o.name

           ,CASE o.type WHEN 'U' THEN 'Table' WHEN 'V' THEN 'View' ELSE 'n/a' END

           ,s.[name]

           ,'Statistics'

           ,STATS_DATE(s.object_id, s.stats_id)

FROM sys.stats s

JOIN sys.objects o ON s.[object_id] = o.[object_id]

WHERE

           o.type IN ('U', 'V')  

ORDER BY 1, 2

Posted by Brian K. McDonald on 8 November 2010

SQL Noob, I'm not sure why you're would be reporting as older than 3 months if you are sure they have been rebuilt. If I were you, I would rebuild one of the three that you are referring to and then rerun the query. Keep us posted if it is still coming up after rebuilding.

Posted by Brian K. McDonald on 8 November 2010

That's a great idea Dmitriy. Thanks for sharing it with us.

Posted by lianvh on 8 November 2010

Good Day. Thank you for the script . It revealed some interesteing information.  I updated the statistics, but when I ran the script and it  showed that the statistics were not updated .

Posted by Brian K. McDonald on 9 November 2010

I just ran the script against the DimAccount table in the AdventureWorksDW2008R2 database. Before rebuilding the index the date was 6/16/2010 and after rebuilding the indexes for this table, the StatisticsDate was set to today. If you have the AdventureWorksDW2008R2 database installed. Try running my script, then update the statistics for DimAccount and then check the stats again. Please keep us posted. Maybe its an environment issue. Which version of SQL are you on?

Posted by Brian K. McDonald on 10 November 2010

lianvh - Try running this script against the AdventureWorksDW2008R2 or AdventureWorksDW2008 database and let me know if you have different dates.

Thanks

USE AdventureWorksDW2008R2

GO

--Temp table to hold stats

CREATE TABLE #CompareStats

(

TableName VARCHAR(100) NOT NULL

, IndexName VARCHAR(200) NOT NULL

, OldStatisticsDate DATETIME NOT NULL

, NewStatisticsDate DATETIME NULL

)

--Get Original Value

INSERT INTO #CompareStats (TableName, IndexName, OldStatisticsDate)

SELECT o.name, i.name, STATS_DATE(i.[object_id], i.index_id)

FROM sys.indexes i JOIN sys.objects o ON i.[object_id] = o.[object_id]

WHERE o.type = 'U' AND o.name = 'DimAccount'

ORDER BY o.name, i.type

--Update the stats for table

UPDATE STATISTICS DimAccount;

--Now get the New Date

UPDATE #CompareStats

SET NewStatisticsDate = STATS_DATE(i.[object_id], i.index_id)

FROM sys.indexes i JOIN sys.objects o ON i.[object_id] = o.[object_id]

JOIN #CompareStats cs ON cs.IndexName = i.name AND cs.TableName = o.name

WHERE o.type = 'U' AND o.name = 'DimAccount'

--Return the results

SELECT * FROM #CompareStats

--Clean up

DROP TABLE #CompareStats

Posted by hardik.doshi on 11 November 2010

Thanks for the script.

I have executed the script on my database, but few records has StatisticsDate column as NULL.

Can you please tell me the reason?

Posted by Steve Rezhener on 19 November 2010

Brian,

Thanks for the script.

I was going to incorporate it into my index maintenance procedure, but now I've a second thought about it.

hardik (and other great SQL gurus),

I spent few hours trying to understand as to why this function is not persistent.

Here is my humble explanation to the "miracle" of StatisticsDate IS NULL or StatisticsDate with the wrong date.

STATS_DATE function is relative to the database of the session/query analyzer. STATS_DATE throws NULL when object_id could not be found in the sys.objects of this database. STATS_DATE gives incorrect date when object_id is found, even though it belongs to a different object.

The only way to incorporate STATS_DATE in a script is by applying USE <myDatabase> keyword first.

All the best,

Steve.

Posted by Brian K. McDonald on 21 December 2010

Steve,

you are correct that you have to use the "USE" statement as it the stats_date function is executed in the context of the database that you are running the script in. :)

Posted by Big Pic on 23 July 2012

Another reason you can get NULL for the StatisticsDate is where you have zero rows in the table!

Leave a Comment

Please register or log in to leave a comment.