What is the most efficient, least intrusive, way of getting table row counts (say, for keeping periodic statistics)? Select(*) is accurate. Using index statistics is instant but the statistics could be out of date.
Dynamic management view. http://technet.microsoft.com/en-us/library/ms188917.aspx
-- record_count for all tables in current database. SELECT object_name(object_id) AS TableName ,record_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') WHERE index_id IN ( 0 ,1 ) AND alloc_unit_type_desc = 'IN_ROW_DATA'
record_count may not match (count(*)) for heaps (see TN article), and indeed I’ve found that to be the case. Adding a PK to the table and running sp_updatestats changed record_count, but it still didn’t match count(*).
Sum the row_count of each partition. This gave accurate results in my tests where db_index_physical_stats didn’t. However one shouldn’t rely on it because TN describes row_count as “The approximate number of rows in the partition.”
SELECT t.name, object_schema_name(t.object_id) as schemaName, SUM(stat.row_count) FROM sys.dm_db_partition_stats stat INNER JOIN sys.tables t on stat.object_id=t.object_id WHERE (index_id =1 or index_id=0) AND t.type='U' GROUP BY t.name, object_schema_name(t.object_id) ORDER BY t.name
When were Statistics last updated?
List indexes and the date their statistics were last updated – using STATS_DATE(objetc_id, index_id).
SELECT t.name AS Table_Name ,i.name AS Index_Name ,i.type_desc AS Index_Type ,STATS_DATE(i.object_id,i.index_id) AS Date_Updated FROM sys.indexes i JOIN sys.tables t ON t.object_id = i.object_id WHERE i.type > 0 ORDER BY t.name ASC ,i.type_desc ASC ,i.name ASC
Full statistics details – including usage histogram:
dbcc show_statistics ([Transactions], IX_Transactions_compound)
Updating statistics can be resource intensive in itself; it can also cause queries to recompile.
- exec sp_updatestats
Updates stats for all tables in the current database if necessary. For example, if at least 1 row has been modified since last statistics update.
- DBCC UPDATEUSAGE(0) WITH COUNT_ROWS, NO_INFOMSGS
- UPDATE STATISTICS [table_name]
Query to find rowmodctr (rows modified). Source: http://www.littlekendra.com/2009/04/21/how-stale-are-my-statistics/
SELECT DISTINCT tablename = object_name(i.object_id) ,o.type_desc ,index_name = i.[name] ,statistics_update_date = STATS_DATE(i.object_id, i.index_id) ,si.rowmodctr FROM sys.indexes i(NOLOCK) INNER JOIN sys.objects o(NOLOCK) ON i.object_id = o.object_id INNER JOIN sys.sysindexes si(NOLOCK) ON i.object_id = si.id AND i.index_id = si.indid WHERE o.type = 'U' --ignore system objects AND STATS_DATE(i.object_id, i.index_id) IS NOT NULL ORDER BY si.rowmodctr DESC