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
March 26, 2023 at 3:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
You can gather statistics using this object sys.dm_db_index_usage_stats
It will provide information about seeks, scans, updates etc.
SELECT
DB_NAME(database_id) AS [Database Name],
OBJECT_NAME(object_id) AS [Table Name],
last_user_update,
last_user_seek,
last_user_scan,
last_user_lookup,
user_updates,
user_seeks,
user_scans,
user_lookups
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID()
ORDER BY last_user_update DESC;
=======================================================================
March 27, 2023 at 8:41 am
You can gather statistics using this object sys.dm_db_index_usage_stats
It will provide information about seeks, scans, updates etc.
SELECT
DB_NAME(database_id) AS [Database Name],
OBJECT_NAME(object_id) AS [Table Name],
last_user_update,
last_user_seek,
last_user_scan,
last_user_lookup,
user_updates,
user_seeks,
user_scans,
user_lookups
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID()
ORDER BY last_user_update DESC;
Just remember to track this very frequently, as this info is lost on server/instance restart.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply