Thursday, February 23, 2012

When were the statistics for a table last updated?

The below query returns the statistics for a table last updated. 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 AS Table_Name, AS Index_Name,
i.type_desc AS Index_Type,
dex_id) AS Date_Updated --,sp.rows --if you want t
STATS_DATE(i.object_id,i.i no know how many rows unrem this FROM
ject_id = i.object_id JOIN sys.partitions sp ON t.object_id = sp.
sys.indexes i JOIN sys.tables t ON t.o bobject_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 ASC, i.type_desc ASC, ASC
This little query can be useful in troubleshooting and diagnosing performance-related issues. Sometimes, it's as simple as outdated statistics.

No comments:

Post a Comment