SQL Server Performance

How to know the statistics are upto date.

Discussion in 'Performance Tuning for DBAs' started by haridba, Oct 11, 2007.

  1. haridba New Member

    Hi Guys
    How to know the statistics on tables and indexes are upto date.
    Thanks in advance.
    haridba, Oct 11, 2007
    #2

  2. MichaelB Member

    MichaelB, Oct 11, 2007
    #3

  3. Luis Martin Moderator

    SELECT 'Index Name' = i.name,
    'Statistics Date' = STATS_DATE(i.id, i.indid)
    FROM sysobjects o, sysindexes i
    WHERE o.name = 'your table' AND o.id = i.id
    Luis Martin, Oct 11, 2007
    #4

  4. ranjitjain New Member

    When you execute sp_updatestats, it actually shows you whether any updates on statistics happened or not under Messages tab
    ranjitjain, Oct 12, 2007
    #5

  5. MichaelB Member

    How do you know if the optimizer thinks it is out of date (date means very little sometimes) and how can you reach this info programtically rather than seeing it in the showplan? I am looking for a job I could run that some t-sql, loop thru stats for each table (or DMV) and tell me what the optimizer thinks needs a look?
    Mike
    MichaelB, Oct 15, 2007
    #6

  6. satya Moderator

    This is where it lacks on SQL 2000, with 2005 yes you can hand in with DMVs.
    satya, Oct 15, 2007
    #7

Share This Page