November 11, 2007 at 6:53 am
Envrionment: SQL Server 2000 but probably is the same in SQL Server 2005
Is there any place where we can find out when was the last time this object has statistics updated?
When we use UPDATE STATISTICS table_name 'index' , how to find out when this 'index' statistics was updated last time? did not see any information from sp_helpindex.......
Thanks for any input.
Data Sheet
November 11, 2007 at 9:09 am
DBCC SHOW_STATISTICS (table_name , index_name)
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 13, 2007 at 12:47 am
-- For SQL 2005
SELECT STATS_DATE(object_id, index_id), object_name(object_id)
from sys.indexes where object_id = object_id('TableNameHere')
Edit: Just noticed your on SQL 2000....
Not exactly the same, cause of the changes to the system tables
-- For SQL 2000
SELECT STATS_DATE(id, indid), object_name(id)
from sysindexes where id = object_id('TableNameHere')
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 13, 2007 at 6:57 am
Very coool.
Thanks, that helps.
Data Sheet
November 13, 2007 at 6:59 am
you may find sp_autostats tablename is quickest if you just want basic information
this query is more complex - run in the database you're interested in
( this is part of my work on index analysis )
select object_name(s.[object_id]) as TableName,c.name as ColumnName,s.name as StatName,s.auto_created,s.user_created,s.no_recompute,s.[object_id],
s.stats_id,sc.stats_column_id,sc.column_id,stats_date(s.[object_id], s.stats_id) as LastUpdated , sum(par.rows) as [rows]
from sys.stats s join sys.stats_columns sc on sc.[object_id] = s.[object_id] and sc.stats_id = s.stats_id
join sys.columns c on c.[object_id] = sc.[object_id] and c.column_id = sc.column_id
join sys.partitions par with (nolock) on par.[object_id] = s.[object_id]
join sys.objects obj with (nolock) on par.[object_id] = obj.[object_id]
where objectproperty(s.OBJECT_ID,'IsUserTable') = 1
and s.auto_created | s.user_created = 1
group by object_name(s.[object_id]),c.name ,s.name ,s.auto_created,s.user_created,s.no_recompute,s.[object_id],
s.stats_id,sc.stats_column_id,sc.column_id,stats_date(s.[object_id], s.stats_id);
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply