Report of tables more used

Question

Report of tables more used

leonardo_gt

Old Hand

Points: 382
More actions
April 9, 2010 at 7:50 am

#146409

Hello...
I'm trying to make a report of tables more used in a periodo of time (numbers of acces of a table: insert, select or update)
It's posible obtain this information? Some one can guide me to do this?
Thanks a lot.

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

You can look at the index usage stats in sys.dm_db_index_usage_stats, but you can only detect index updates, you can't determine if the write operation comes from an INSERT, UPDATE or DELETE command.

You can obtain something like this:

SELECT object_name(s.object_id) AS ObjectName,

s.object_id,

i.name as IndexName,

i.index_id,

user_seeks,

user_scans,

user_lookups,

user_updates

FROM sys.dm_db_index_usage_stats s

INNER JOIN sys.indexes i

ON i.object_id = s.object_id

AND i.index_id = s.index_id

WHERE database_id = db_id()

AND objectproperty(s.object_id, 'IsUserTable') = 1

ORDER BY ( user_seeks + user_scans + user_lookups + user_updates ) DESC

-- Gianluca Sartori

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

leonardo_gt (4/9/2010)
... in a periodo of time...

The above query returns data since last service/system restart, but it can't be limited to a specific date range.

Maybe there's some other DMV to do the trick, but none that I'ma aware of.

Hope this helps

Gianluca

-- Gianluca Sartori

leonardo_gt Old Hand Points: 382 More actions · Answer 3

Thanks Gianluca, I will try use this dmv...

Greetings