When Were The Statistics In SQL Server Last Updated?

When SQL Server receives a new query, the Query Optimizer undertakes the task of identifying a good plan to resolve the 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. Dr. DeWitt did an amazing job of explaining this process in understandable terms at the 2010 PASS Community Summit.

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 in 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. Statistics also influence the logical join order and physical types selected.

That’s why it is crucial that the statistics 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 in SQL Server 2005/2008. 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

In SQL Server 2000, a similar query can be run.

SELECT
   o.name AS Table_Name
   ,i.name AS Index_Name
   ,STATS_DATE(o.id,i.indid) AS Date_Updated
FROM
   sysobjects o JOIN
   sysindexes i ON i.id = o.id
WHERE
   xtype = ‘U’ AND
   i.name IS NOT NULL
ORDER BY
   o.name ASC
   ,i.name ASC

These queries can be useful while troubleshooting and diagnosing performance-related issues. Sometimes, it’s as simple as outdated statistics.

About these ads

5 Responses to When Were The Statistics In SQL Server Last Updated?

  1. Pingback: When Were The Statistics In SQL Server Last Updated? « WebbTech … - sql

  2. Hi Joe,

    Using sys.stats is a better choice

    Try this

    SELECT *, stats_date(object_id, stats_id) AS update_date FROM sys.stats

    Regards,

    Ben

    • Joe says:

      Ahhh, very nice Ben!

      For the blog I was pulling out scripts I’d used in the past for delving into performance issues. This one seemed a bit odd, but I couldn’t place it. You hit it right on.

      Thanks!

      Joe

  3. Pingback: A NULL Is Not Unknown « WebbTech Solutions

  4. Pingback: How To Write Better Queries WebCast « WebbTech Solutions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 33 other followers