Always wonder what the most active or volatile tables are in a particular database?

The following queries (both SQL 2000 and SQL 2005 examples provided) on the system tables and data management views provide insight into how tables are being accessed and modified.

The example below display just the top 20….

This information can be useful in planning of index maintenance, application usage trends or capacity planning…

--SQL 2000 syntax….
SELECT TOP 20
SUBSTRING(db_name(),1,32) AS [DBName],
o.name AS [Tablename],
i.name AS [IndexName],
i.rows AS [TotalRows],
i.rowmodctr AS [RowsModified],
STATS_DATE(i.id, i.indid) as [LastStatsDate]
FROM SYSOBJECTS o JOIN SYSINDEXES i
ON o.id = i.id
WHERE i.rowmodctr > 0  and o.xtype = 'U'
AND i.indid=1  --Clustered
ORDER BY i.rowmodctr DESC

--SQL 2005 Syntax (uses the function:  sys.dm_db_index_operational_stats)
SELECT TOP 20
db_name(database_ID),
object_name(object_id),
leaf_insert_count,
leaf_delete_Count,
leaf_update_count,
range_scan_count,
row_lock_count
FROM sys.dm_db_index_operational_stats(NULL, NULL, NULL, NULL)
WHERE db_name(database_ID)='AdventureWorksDW'  --Or you can leave out the WHERE clause to examine stats on ALL databases.
ORDER BY leaf_update_count desc   --you can order by any column you wish to analyze further

More info can be found in SQL 2005 Books Online Topic: sys.dm_db_index_operational_stats
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/13adf2e5-2150-40a6-b346-e74a33ce29c6.htm
