Find busy tables

Filename : Find_Busy_Tables.sql

PURPOSE: Find the most busy tables of all user databases plus the current running database

--Find the busy tables of ALL databases in this DB instance

SELECT DBname = DB_NAME(database_id), TableName = OBJECT_NAME(object_id, database_id)

　　　　,Total_IO_Count = SUM(leaf_insert_count + leaf_delete_count + leaf_update_count + range_scan_count + singleton_lookup_count)

　　　　,Total_Write_Count = SUM(leaf_insert_count + leaf_delete_count + leaf_update_count)

　　　　,Total_Read_Count = (range_scan_count + singleton_lookup_count)

FROM sys.dm_db_index_operational_stats(

 NULL --DB, use NULL for all databases

,NULL --object, all tables and indexes under this user database

,NULL --index, NULL means all indexes

,NULL --partition, NULL means all partitions

)

WHERE database_id > 4 -- forget about all the system databases

GROUP BY database_id, object_id

ORDER BY Total_IO_COUNT desc,DBname asc, TableName asc

 

--next one is to search all the user-defined table of the current DB

 

SELECT DBname = DB_NAME(database_id), TableName = OBJECT_NAME(object_id, database_id)

　　　　,Total_IO_Count = SUM(leaf_insert_count + leaf_delete_count + leaf_update_count + range_scan_count + singleton_lookup_count)

　　　　,Total_Write_Count = SUM(leaf_insert_count + leaf_delete_count + leaf_update_count)

　　　　,Total_Read_Count = (range_scan_count + singleton_lookup_count)

FROM sys.dm_db_index_operational_stats(

 DB_ID() --only for local database

,NULL --object, all tables and indexes under this user database

,NULL --index, NULL means all indexes

,NULL --partition, NULL means all partitions

)

WHERE database_id > 4 -- forget about all the system databases

AND objectproperty(object_id, 'ISTABLE')=1 --it is a table(of local database),it can't use database_id

AND OBJECTPROPERTY(object_id, 'ISSYSTEMTABLE')=0

GROUP BY database_id, object_id

ORDER BY Total_IO_COUNT desc,DBname asc, TableName asc