Query statistics for a single table

SQL Server Statistics Overview

sys.stats: This contains a row for each statistics object related to tables, indexes, and indexed views in a SQL Server database.

sys.dm_db_stats_properties: This returns details about statistics for a given database object (either a table or an indexed view) in the current SQL Server database.

Query to Check Statistics Information

Use the following query to retrieve statistics details for a specific table or indexed view:

-- Execute the query below to inspect statistics
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('HumanResources.Employee');
    

Explanation of Columns:

• Stats_ID: The unique ID for the statistics object.
• Name: The name of the statistics object.
• Last_updated: The timestamp of when the statistics were last updated.
• Rows: The total number of rows at the time of the last update.
• Rows_sampled: The number of rows sampled for generating the statistics.
• Unfiltered_rows: The count of rows in the table without any filters (it may be the same as rows_sampled if no filter is applied).
• Modification_counter: This counts the number of changes made to the table since the last update to the statistics.

Identify Auto-Created Statistics by SQL Server

To find statistics that were automatically generated by SQL Server, run this query:

-- Query to find auto-created statistics by SQL Server
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('HumanResources.Employee')
AND name LIKE '_WA%';
    

Helpful Link

For more information on the sys.dm_db_stats_properties dynamic management view, check the official documentation:

sys.dm_db_stats_properties - Microsoft Docs

Example: Identify Tables with Modified Statistics

The query below retrieves a list of all tables, indexed views, and statistics in the current database where the leading column has been 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;