Joe Webb

Musing and observations about SQL Server, other technogies, and sometimes just life in general
posts - 85, comments - 857, trackbacks - 0

My Links

News

This blog has moved!

Click here for the new location.

Follow me on Twitter
Add to Technorati Favorites

Search this Blog
 




Archives

Post Categories

About me

When were the statistics for a table last updated?

When SQL Server receives a new query, it attempts determine the best possible plan for resolving that query. It considers a number of different factors as it analyzes the query and maps out a way in which to retrieve the information requested.

Whether or not the query optimizer deems an index to be useful in resolving a query largely depends on the information contained in the statistics for that index.

If the statistics are outdated and do not accurately represent the distribution of values within the table, the query optimizer may not produce an optimal plan for resolving the query. Misleading statistics may result in the optimizer not using an index when it should, or using an index when it would be more efficient to scan the table.

That's why it is crucial that the statistics for an index be updated regularly. How often? Well, that, of course, is going to depend on the data being stored, the frequency of updates, inserts, and deletes to the table, etc. It could be nightly; it could monthly. It just depends.

So, how can you tell when the statistics where last updated for an index?

The following query demonstrates this. It makes use of the sys.indexes and sys.tables catalog views, along with the STATS_DATE() function, to retrieve the date that each index was last updated for every user table in the current database.

SELECT         
t.name AS Table_Name
,i.name AS Index_Name
,i.type_desc AS Index_Type
,STATS_DATE(i.object_id,i.index_id) AS Date_Updated
FROM
sys.indexes i JOIN
sys.tables t ON t.object_id = i.object_id
WHERE
i.type > 0
ORDER
BY
t.name ASC
,i.type_desc ASC
,i.name ASC

 

This little query can be useful in troubleshooting and diagnosing performance-related issues. Sometimes, it's as simple as outdated statistics.

Cheers!

Joe

kick it on DotNetKicks.com

Print | posted on Friday, August 31, 2007 6:37 PM | Filed Under [ T-SQL SQL Server ]

Feedback

Gravatar

# re: When were the statistics for a table last updated?

Hi Joe!
I made some modification to your useful script.
In detail, I'm add join to sys.partition and filter indexes which have row count = 0.

SELECT
t.name AS Table_Name
,i.name AS Index_Name
,i.type_desc AS Index_Type
,STATS_DATE(i.object_id,i.index_id) AS Date_Updated
FROM
sys.indexes i JOIN
sys.tables t ON t.object_id = i.object_id JOIN
sys.partitions sp ON i.object_id = sp.object_id
WHERE
i.type > 0 and sp.rows > 0
ORDER BY
t.name ASC
,i.type_desc ASC
,i.name ASC
9/4/2007 2:17 AM | Alexandr Volok
Gravatar

# re: When were the statistics for a table last updated?

Ah, very good Alexandr. Thanks for sharing!

Joe
9/4/2007 6:59 AM | Joe Webb
Gravatar

# re: When were the statistics for a table last updated?

Apparently this is specific to SQL2005 because it did not work on 2000.
9/4/2007 1:43 PM | DHB
Gravatar

# re: When were the statistics for a table last updated?

I run this scrip on SQL server, but i find that script not run. The error message is "Invalid object name 'sys.indexes'"
9/5/2007 5:34 AM | aderp
Gravatar

# re: When were the statistics for a table last updated?

Yes, this script doesn't work for SQL Server 2000 since the sys.indexes view doesn't exist in that version. I should have pointed that out in the original post.

I'll create a similar script for SQL Server 2000 and post in the next day or two.

In the meantime, for SQL Server 2000 you can use the DBCC SHOW_STATISTICS command to see the last time an index's statistics were updated. For example, the following command shows information about the aunmind index on the authors tables in the pubs database.

DBCC SHOW_STATISTICS (authors, aunmind)


HTH...

Joe

9/5/2007 2:00 PM | Joe Webb
Gravatar

# re: When were the statistics for a table last updated?

Neat stuff... one more tweak for AW's code to eliminate dupes on the new join, and a few other optionals

SELECT
t.name AS Table_Name
,i.name AS Index_Name
,i.type_desc AS Index_Type
,STATS_DATE(i.object_id,i.index_id) AS Date_Updated
--,sp.rows --if you want to know how many rows unrem this
FROM
sys.indexes i JOIN
sys.tables t ON t.object_id = i.object_id JOIN
sys.partitions sp ON i.object_id = sp.object_id
and i.index_id = sp.index_id --new

WHERE
i.type > 0 and --if you want to see heap rem this
sp.rows > 0
ORDER BY
t.name ASC
,i.type_desc ASC
,i.name ASC
10/16/2007 11:34 AM | st
Gravatar

# re: When were the statistics for a table last updated?

Very nice. Thanks for providing an update!

BTW - Since my original script didn't work for SQL Server 2000, I've modified it for use with SQL Server 2000. You can view it here:

http://weblogs.sqlteam.com/joew/archive/2007/09/06/60322.aspx
10/23/2007 8:37 AM | Joe Webb
Gravatar

# re: When were the statistics for a table last updated?

thanks for the article! that was exactly what I was looking for!
2/17/2009 8:32 AM | Remote DBA
Gravatar

# re: When were the statistics for a table last updated?

what about stats that are created, but are not indexes?
sys.indexes does not work - there is no rows for those.
4/14/2010 2:27 PM | SteveG
Gravatar

# re: When were the statistics for a table last updated?

Hi;
Unfortunately, although I realize I could not read the topic defalrca to help me in this regard would be glad if the mail.
mary lou
marylou23@gmail.com
5/12/2010 6:09 AM | saç ekimi
Gravatar

# re: When were the statistics for a table last updated?

Beyoncé - Baby Boy - Reggae Medley
7/30/2010 9:02 AM | tup bebek tedavisi
Gravatar

# re: When were the statistics for a table last updated?

How often? Well, that, of course, is going to depend on the data being stored, the frequency of updates, inserts, and deletes to the table, etc. It could be nightly; it could monthly. It just depends.



snow boots | columbia jackets | mac makeup | the north face outlet
10/20/2010 2:19 AM | snow boots for women
Gravatar

# re: When were the statistics for a table last updated?

I ran into this a lot when I was in the data warehousing world. We had tons of stored/archived data that was only occasionally queried on, but when it was, more times than not, I would need to go in and make updates such as this. Fortunately the systems are much more aware and will clue you in when these changes need to happen.
9/23/2011 1:27 PM | ediscovery
Gravatar

# re: When were the statistics for a table last updated?

This was really helpful...! Here's a version that excludes a number of unwanted index types (I think this is only for SQL 2005).

SELECT t.name AS Table_Name,
i.name AS Index_Name,
i.type_desc AS Index_Type,
STATS_DATE(i.object_id, i.index_id) AS Date_Updated
FROM sys.indexes i
JOIN sys.tables t ON t.object_id = i.object_id
WHERE i.type > 0
AND i.is_hypothetical = 0
AND is_ms_shipped = 0
ORDER BY 4
10/6/2011 4:03 PM | Alex Oss
Gravatar

# re: When were the statistics for a table last updated?

Comments made ??about the issues impressed me. I like your site..
2/27/2012 3:22 AM | fuesacekimi
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET