Updated: Dec 31, 2021
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'