How to identify tables which are changing very often

Question

Hi All,

Is there any query using which we can tell what is the table which is changing the most or more frequently? The idea, is to identify those tables and run update statistics with full scan on daily basis instead of weekly basis.

Regards,

Sam

Answer 1

In SQL Server, you can use the sys.dm_db_index_usage_stats dynamic management view to gather information about index usage and changes. This can help you identify tables with frequent updates, inserts, and deletes. Please note that these statistics are reset when the SQL Server instance is restarted.

SELECT 
    OBJECT_NAME(ius.object_id) AS TableName,
    SUM(ius.user_updates) AS TotalUpdates,
    SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS TotalReads
FROM 
    sys.dm_db_index_usage_stats ius
JOIN 
    sys.indexes idx ON ius.object_id = idx.object_id AND ius.index_id = idx.index_id
WHERE 
    ius.database_id = DB_ID() -- Current database
    AND idx.type_desc <> 'HEAP'
GROUP BY 
    ius.object_id
ORDER BY 
    TotalUpdates DESC;

Answer 2

Thanks for your question.

Here is a query to find out when the table was last updated.

If WHERE condition is removed, it will provide details of the entire database. (Need to run in specific database)

SELECT OBJECT_NAME(OBJECT_ID) AS TableName,

 last_user_update,*

FROM sys.dm_db_index_usage_stats

WHERE database_id = DB_ID( 'database name')

AND OBJECT_ID=OBJECT_ID('table name')

Let us know if this help!

Answer 3

Hi @Samantha r

Here is a workaround:

Step 1: Enable Change Tracking for a Database;

ALTER DATABASE AdventureWorks2019   
SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  

Step 2: Enable Change Tracking for a Table;

Step 3: Use the change tracking functions to obtain changes.

Refer to this article for more details and examples: Work with change tracking (SQL Server).

Best regards,

Cosmog Hong