April 9, 2010 at 7:50 am
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.
April 9, 2010 at 8:05 am
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
April 9, 2010 at 8:09 am
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
April 9, 2010 at 8:36 am
Thanks Gianluca, I will try use this dmv...
Greetings
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply