Post reply

UPDATA STATISTICS

  • November 11, 2007 at 6:53 am

    #183818

    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

    #750101

    DBCC SHOW_STATISTICS (table_name , index_name)

  • November 13, 2007 at 12:47 am

    #750499

    -- 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • November 13, 2007 at 6:57 am

    #750653

    Very coool.

    Thanks, that helps.

    Data Sheet

  • November 13, 2007 at 6:59 am

    #750655

    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