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.

dm_db_index_physical_stats

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(*).

dm_db_partition_stats

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

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