When were my statistics last updated

Quick query to show how to get when your statistics (column or index) statistics were last updated.  If you wanted to track how often they were being updated, you could capture this to a table and run some reports off it.

SELECT 
	OBJECT_NAME(object_id) AS TableName,
	Name, 
	'Index' AS ObjectType,
	STATS_DATE ( object_id , index_id ) as StatsCreatedDate
FROM 
	sys.indexes 
UNION 
SELECT 
	OBJECT_NAME(object_id) AS TableName,
	Name,
	'Column Statistic' AS ObjectType,
	STATS_DATE(object_id, stats_id) AS StatsCreatedDate
FROM 
	sys.stats
ORDER BY 
	StatsCreatedDate DESC

Incidentally, this is also helpful in identifying any recently created indexes you may have created and forgotten to save to run on your various prod\qa\uat boxes [not that I’ve ever done this…]. 

Unfortunately, there is no create date for indexes or stats in the sys.indexes or sys.stats table to know when a particular index was created.  You have to be quick though, as statistics are updated automagically by sql server all day long, when [information gleaned from Statistics Used by the Query Optimizer in Microsoft SQL Server 2005]:

  • The number of rows in the table goes from 0 to more than 0
  • The number of rows when the stats were created was less 501 [500 or less] and the column modification counter [often referenced as colmodctr] of the leading column of the statistics object has changed by more than 500 rows since.
  • The table had more than 500 rows when the leading column of the statistics object has changed by 500 + 20% of the number of rows in the table when the stats were created.

Unless, you have AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS off.  (Don’t do it. Eventually, you’ll regret it.)

Advertisements

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: