This SQL Server Tutorial talks about finding out the last Statistics Update Date in SQL Server. We all know that Statistics are vital for SQL Server. Statistics are used to help SQL Server develop the best execution plans for our queries.
Statistics are undoubtedly crucial for any given database because they contain data distribution among database objects. It's pretty common to hear people speak of statistics regarding not optimal queries and the suggestion to update them in such cases when it is necessary.
If we have the latest statistics installed, the execution plan that SQL Server gets for our query will be better and lead to better performance. If you regularly update statistics, you can run the following query to find out the last time you updated your statistics.
While dealing with a performance issue, sometimes it becomes necessary to find when SQL Server Statistics were last updated. You can use the below query to find out the last updated date for the specified object.
SELECT name AS index_name,
STATS_DATE(OBJECT_ID, index_id) AS StatsUpdated
FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('SalesLT.Product')
GO
Suppose you want to get more details such as number of rows, last updated date, modification counter (number of rows updated since last stats update), and data sampling percentage. In that case, you can use the following query.
SELECT stats.name AS StatisticsName,
OBJECT_SCHEMA_NAME(stats.object_id) AS SchemaName,
OBJECT_NAME(stats.object_id) AS TableName,
[rows] AS [Rows],
last_updated AS LastUpdated,
rows_sampled as [NumberofSamplingrows], modification_counter,
(rows_sampled * 100)/rows AS SamplePercent
FROM sys.stats
INNER JOIN sys.stats_columns sc
ON stats.stats_id = sc.stats_id AND stats.object_id = sc.object_id
INNER JOIN sys.all_columns ac
ON ac.column_id = sc.column_id AND ac.object_id = sc.object_id
CROSS APPLY sys.dm_db_stats_properties(stats.object_id, stats.stats_id) dsp
WHERE OBJECT_NAME(stats.object_id)='Product'
Comments