Combining sys.dm_db_stats_properties & stats_date

Combining sys.dm_db_stats_properties & stats_date

sys.dm_db_stats_properties
Returns properties of statistics for the specified database object (table or indexed view) in the current SQL Server database whereas

stats_date :- 

--> Returns the date of the most recent update for statistics on a table or indexed view.

USE AdventureWorks2012;
GO
SELECT name AS stats_name, 
    STATS_DATE(object_id, stats_id) AS statistics_update_date
FROM sys.stats 
WHERE object_id = OBJECT_ID('Person.Address');
GO


USE AdventureWorks2012;
GO
SELECT name AS index_name, 
    STATS_DATE(object_id, index_id) AS statistics_update_date
FROM sys.indexes 
WHERE object_id = OBJECT_ID('Person.Address');
GO

sys.dm_db_stats_properties 

A. Simple example
The following example returns the statistics for the Person.Person table in the AdventureWorks database.

Returns properties of statistics for the specified database object (table or indexed view) in the current SQL Server database.

SELECT * FROM sys.dm_db_stats_properties (object_id('Person.Person'), 1);


B. Returning all statistics properties for a table

The following example returns properties of all statistics that exist for the table TEST.

SELECT sp.stats_id, name, filter_definition, last_updated, rows, rows_sampled, steps, unfiltered_rows, modification_counter 
FROM sys.stats AS stat 
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE stat.object_id = object_id('TEST');


C. Returning statistics properties for frequently modified objects

The following example returns all tables, indexed views, and statistics in the current database for which the leading column was modified more than 1000 times since the last statistics update.


SELECT obj.name, obj.object_id, stat.name, stat.stats_id, last_updated, modification_counter
FROM sys.objects AS obj 
INNER JOIN sys.stats AS stat ON stat.object_id = obj.object_id
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
WHERE modification_counter > 1000;

D. Returning statistics properties for statistics not updated from last 7 days.

SELECT OBJECT_NAME(id),name,STATS_DATE(id, indid),rowmodctr
FROM sys.sysindexes
WHERE STATS_DATE(id, indid)<=DATEADD(DAY,-7,GETDATE())
AND rowmodctr>0
AND id IN (SELECT object_id FROM sys.tables)

stats_date :- 

Returns the date of the most recent update for statistics on a table or indexed view.


A. Return the dates of the most recent statistics for a table

The following example returns the date of the most recent update for each statistics object on the Person.Address table.


USE AdventureWorks2012;
GO
SELECT name AS stats_name, 
    STATS_DATE(object_id, stats_id) AS statistics_update_date
FROM sys.stats 
WHERE object_id = OBJECT_ID('Person.Address');
GO

If statistics correspond to an index, the stats_id value in the sys.stats catalog view is the same as the index_id value in the sys.indexes catalog view, and the following query returns the same results as the preceding query. If statistics do not correspond to an index, they are in the sys.stats results but not in the sys.indexes results.


USE AdventureWorks2012;
GO

SELECT name AS index_name, 
    STATS_DATE(object_id, index_id) AS statistics_update_date
FROM sys.indexes 
WHERE object_id = OBJECT_ID('Person.Address');
GO
Examples: Azure SQL Data Warehouse and Parallel Data Warehouse

B. Learn when a named statistics was last updated

The following example creates statistics on the LastName column of the DimCustomer table. It then runs a query to show the date of the statistics. Then it udpates the statistics and runs the query again to show the updated date.


--First, create a statistics object

USE AdventureWorksPDW2012;
GO
CREATE STATISTICS Customer_LastName_Stats
ON AdventureWorksPDW2012.dbo.DimCustomer (LastName)
WITH SAMPLE 50 PERCENT;
GO

--Return the date when Customer_LastName_Stats was last updated 

USE AdventureWorksPDW2012;
GO
SELECT stats_id, name AS stats_name, 
    STATS_DATE(object_id, stats_id) AS statistics_date
FROM sys.stats s
WHERE s.object_id = OBJECT_ID('dbo.DimCustomer')
    AND s.name = 'Customer_LastName_Stats';
GO

--Update Customer_LastName_Stats so it will have a different timestamp in the next query  
GO

  UPDATE STATISTICS dbo.dimCustomer (Customer_LastName_Stats);


--Return the date when Customer_LastName_Stats was last updated.  

SELECT stats_id, name AS stats_name, 
    STATS_DATE(object_id, stats_id) AS statistics_date
FROM sys.stats s
WHERE s.object_id = OBJECT_ID('dbo.DimCustomer')
    AND s.name = 'Customer_LastName_Stats';
GO

C. View the date of the last update for all statistics on a table
This example returns the date for when each statistics object on the DimCustomer table was last updated.


--Return the dates all statistics on the table were last updated.

SELECT stats_id, name AS stats_name, 
    STATS_DATE(object_id, stats_id) AS statistics_date
FROM sys.stats s
WHERE s.object_id = OBJECT_ID('dbo.DimCustomer');
GO

If statistics correspond to an index, the stats_id value in the sys.stats catalog view is the same as the index_id value in the sys.indexes catalog view, and the following query returns the same results as the preceding query. If statistics do not correspond to an index, they are in the sys.stats results but not in the sys.indexes results.


USE AdventureWorksPDW2012;
GO
SELECT name AS index_name, 
    STATS_DATE(object_id, index_id) AS statistics_update_date
FROM sys.indexes 
WHERE object_id = OBJECT_ID('dbo.DimCustomer');
GO