Most recent write or read of tables in the database

Question

Most recent write or read of tables in the database

subhajeetsur

SSC-Addicted

Points: 453
More actions
May 14, 2013 at 1:15 am

#277722

The following query can be used to find out the most recent read or write of tables and procedures in the database.
USE test;
GO
SET ANSI_WARNINGS OFF;
SET NOCOUNT ON;
GO
WITH agg AS
(
SELECT
[object_id],
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update
FROM
sys.dm_db_index_usage_stats
WHERE
database_id = DB_ID()
)
SELECT
[Schema] = OBJECT_SCHEMA_NAME([object_id]),
[Table_Or_View] = OBJECT_NAME([object_id]),
last_read = MAX(last_read),
last_write = MAX(last_write)
FROM
(
SELECT [object_id], last_user_seek, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_scan, NULL FROM agg
UNION ALL
SELECT [object_id], last_user_lookup, NULL FROM agg
UNION ALL
SELECT [object_id], NULL, last_user_update FROM agg
) AS x ([object_id], last_read, last_write)
GROUP BY
OBJECT_SCHEMA_NAME([object_id]),
OBJECT_NAME([object_id])
ORDER BY 1,2;
The output of the query is:
Schema Table_Or_Viewlast_read last_write
dboabc 2011-05-09 12:35:55.8672011-05-09 12:35:18.477
dbocol1 2011-01-20 17:44:20.3572011-01-20 17:15:28.567
dboemp1 2011-01-20 18:31:39.5302011-01-20 17:13:05.910
dbotempo 2011-05-09 12:37:23.540NULL
syssyscolpars 2011-04-13 16:24:47.083NULL
Here use denotes the database name. It can be changed to find out information about the database that the user wants.
This query can be helpful in situation to verify whether loading of tables have happened at the specific time or knowing about usage of different tables in the database.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Minor correction.

That script shows the last read or write since the database last started. It shows no information at all prior to that. Hence if you reboot the server every week on Sunday and a particular table is only ever used on Fridays, then running that script on Thursday afternoon will indicate that the table has never been used.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

raadee SSC Eights! Points: 871 More actions · Answer 2

Looks like Aaron Bertrand's script from:

http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx

Credit where credit is due.