Useful SQL Server Statistics Queries

-- Check Stats for specific tables

SELECT object_name(sp.object_id) AS [Table]

      ,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('SPECIFY TABLE HERE');

-- Check stats for all user tables on current database

SELECT SCHEMA_NAME(schema_id) AS [SchemaName]

      ,obj.name AS [TableName]

      ,obj.object_id

         ,stat.name [StatName]

         ,stat.stats_id

         ,filter_definition

         ,rows

         ,rows_sampled

         ,steps

         ,unfiltered_rows

         ,last_updated

         ,modification_counter

FROM sys.tables AS obj

JOIN sys.stats stat ON stat.object_id = obj.object_id

CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp

WHERE obj.type <> 'S'

ORDER BY last_updated ASC;

--Check Stats for current database where the leading column has been modified over 1000 times

SELECT SCHEMA_NAME(schema_id) AS [SchemaName]

      ,obj.name AS [TableName]

      ,obj.object_id

         ,stat.name [StatName]

         ,stat.stats_id

         ,last_updated

         ,modification_counter

FROM sys.tables AS obj

JOIN sys.stats 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

AND obj.type <> 'S';