Misc SQL Statistics Queries

February 10, 2012 DB

View statistics for a table:

SELECT name AS stats_name, 
STATS_DATE(object_id, stats_id) AS statistics_update_date,
no_recompute
FROM sys.stats
WHERE object_id = OBJECT_ID('dbo.csi_answer');

Each statistics shows when it was last updated and if no_recompute is on:

Database properties has a setting called “Auto Update Statistics”:

This sproc:

EXEC sp_updatestats

runs UPDATE STATISTICS against all user-defined tables in the current database. The documentation for sp_updatestats states this:

sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.

RowModCTR can be found with this query:

SELECT * FROM sys.sysindexes
WHERE id = OBJECT_ID('dbo.csi_answer')