Last Table READ and WRITE

Question

Post reply

Last Table READ and WRITE

arrjay

Hall of Fame

Points: 3455
More actions
February 26, 2015 at 5:07 am

#398085

Hi there - How would I find the last read/write dates for all the tables within a database. Thanks!

--------------------------------------------

Laughing in the face of contention...

Viewing 7 posts - 1 through 6 (of 6 total)

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 1

It's not a perfect solution because if you have heaps it won't work, but you can look to the sys.dm_index_usage_stats for everything else.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

spaghettidba SSC Guru Points: 105712 More actions · Answer 2

Grant Fritchey (2/26/2015)

It's not a perfect solution because if you have heaps it won't work, but you can look to the sys.dm_index_usage_stats for everything else.

Something like this:



SELECT DB_NAME(database_id) AS database_name,

	OBJECT_NAME(object_id, database_id) AS object_name,

	MAX(reads.last_read) AS last_read,

	MAX(last_user_update) AS last_write

FROM sys.dm_db_index_usage_stats

CROSS APPLY (

	VALUES(last_user_seek),(last_user_scan),(last_user_lookup)

) AS reads(last_read)

GROUP BY DB_NAME(database_id),

	OBJECT_NAME(object_id, database_id)

-- Gianluca Sartori

arrjay Hall of Fame Points: 3455 More actions · Answer 3

arrjay

Hall of Fame

Points: 3455

February 26, 2015 at 6:51 am

#1780228

Perfect! Thanks.

The results don't include objects which have not been written/read to/from i.e. nulls. Is there a way of returning these stats? Thanks again.

--------------------------------------------

Laughing in the face of contention...

Lowell SSC Guru Points: 323482 More actions · Answer 4

arrjay (2/26/2015)

Perfect! Thanks.

The results don't include objects which have not been written/read to/from i.e. nulls. Is there a way of returning these stats? Thanks again.

pretty sure you have to join against sys.tables first,and left join to the index stats in order to get all tables. index stats is server wide, so if you need it for anything other than one specific database, it's not going to work, or require a boatload of additional logic.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

spaghettidba SSC Guru Points: 105712 More actions · Answer 5

Lowell (2/26/2015)

arrjay (2/26/2015)

Perfect! Thanks.

The results don't include objects which have not been written/read to/from i.e. nulls. Is there a way of returning these stats? Thanks again.

pretty sure you have to join against sys.tables first,and left join to the index stats in order to get all tables. index stats is server wide, so if you need it for anything other than one specific database, it's not going to work, or require a boatload of additional logic.

Completely agree.

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 6

spaghettidba (2/26/2015)

Lowell (2/26/2015)

arrjay (2/26/2015)

Perfect! Thanks.

The results don't include objects which have not been written/read to/from i.e. nulls. Is there a way of returning these stats? Thanks again.

pretty sure you have to join against sys.tables first,and left join to the index stats in order to get all tables. index stats is server wide, so if you need it for anything other than one specific database, it's not going to work, or require a boatload of additional logic.

Completely agree.

+1

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software