Home > SQL Server, T-SQL > Get Last Updated Date for SQL Statistics

Get Last Updated Date for SQL Statistics


STATS_DATE(,)  returns the date of the most recent update for statistics on a table or indexed view.

Syntax

STATS_DATE ( object_id , stats_id )

This function returns datetime value on successful execution or  NULL value if there is an error in the argument.

  • object_id: This is an ID of the table or indexed view with the statistics.
  • stats_id: This is an ID of the statistics object.

Simple note about SQL Statistics:

Statistics in the SQL Server helps the query optimizer to calculate the cost of the query plans. If the statistics are missing or out of date, it does not have correct data to estimate the plan then it can generate moderate or sub-optimized query plan. This may impact the SQL query performance.

More information for Statistics:

This blog post is to show you the use of function STAT_DATE with an example.

Example 1: This SQL query will display all Index statistics of a Database.

USE AdventureWorks2012;
GO
SELECT t.name as table_name,  i.name AS index_name,
STATS_DATE(i.object_id, i.index_id) AS statistics_last_updated_date,
cast(DATEDIFF(dd, STATS_DATE(i.object_id, i.index_id), getdate()) as varchar(5)) +  ‘ Day(s)’ as Days_Since_Last_Update
FROM sys.indexes as i join sys.tables as t on i.object_id = t.object_id
GO

Example 2: This SQL Query will display all table statistics of a database.

USE AdventureWorks2012;
GO
SELECT object_name(object_id) as table_name, name AS statistics_name,
STATS_DATE(object_id, stats_id) AS statistics_last_updated_date,
cast(DATEDIFF(dd, STATS_DATE(object_id, stats_id), getdate()) as varchar(5)) +  ‘ Day(s)’ as Days_Since_Last_Update
FROM sys.stats where left (name,4) = ‘_WA_’ and object_id in ( select object_id from sys.tables where type =’u’)
GO

These queries help you to identify the statistics which are not up-to-date using  ‘Days_Since_Last_Update’ column in the SQL query.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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

%d bloggers like this: