How to find the latest updated table in SQL Server
At time of monitoring the performance of the database, we do want to find outwhats happening now in database,
what is the last updated table,
More User seek operations are happening,
More User scan operations are happening.
Above steps are important at performance level also because there is a lot of diference between User Seek operation and User Scan operation.
When does system go for User Seek operation:
When a query is run against a table, two types of operations may occur one is Seek and the other is Scan.
If the query use the index created on the table for retreiving the rows then it will go for "Seek" opearation.
Query to find out the top 5 tables which has more number of Seek:
/*change DB_NAME with respected database name*/
SELECT top 5 OBJECT_NAME(OBJECT_ID) AS Tablename,user_seeks
object_id IN (select id from sysobjects where type like 'U')order by user_seeks desc
When does system go for User Scan operation:
If the query does not use proper index or no index then it has to endup with the complete scan of a table. This is called as "SCAN" operation.
Query to find out the top 5 tables which has more number of Seek:
/*change DB_NAME with respected database name*/
SELECT top 5 OBJECT_NAME(OBJECT_ID) AS Tablename,user_scansFROM sys.dm_db_index_usage_statsWHERE database_id = DB_ID('DB_name')and
object_id IN (select id from sysobjects where type like 'U')order by user_scans desc
Query to find out most number of times updated table:
/*change DB_NAME with respected database name*/
SELECT top 5 OBJECT_NAME(OBJECT_ID) AS Tablename,user_updatesFROM sys.dm_db_index_usage_statsWHERE database_id = DB_ID('DB_name') and last_user_update is not nulland
object_id IN (select id from sysobjects where type like 'U')order by user_updates desc
Query to find out most number of times updated table:
/*change DB_NAME with respected database name*/
SELECT top 5 OBJECT_NAME(OBJECT_ID) AS Tablename,last_user_updateFROM sys.dm_db_index_usage_statsWHERE database_id = DB_ID('DB_name') and last_user_update is not nulland
object_id IN (select id from sysobjects where type like 'U')order by last_user_update desc
Hope the above information helps
Thanks
