Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Tuesday, 12 May 2015

How to check Last updated Statistics in Table for SQL Server?

How to check Last updated Statistics in Table for SQL Server:

First - find the statistics you want to check:


Second - see its properties, and there you will see the last updated timestamp:




(Or )
you may want to execute the following query:
SELECT t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated 
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
    ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'

(Or)

Display the status of all statistics on a table

The following displays the status of all statistics on the Product table.
USE DatabaseName;
GO
EXEC sp_autostats 'TableName';
GO
(Or)
The best way to get information about statistics is through the command
DBCC SHOW_STATISTICS (TableName,indexname)
That will return information not just about when the stats where updated, but their size, density, how selective they are, and the histogram that shows the distribution of data. With all that, you can determine if those stats are up to date and effective.