Report of tables more used

  • 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.

  • 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

  • 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

  • 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