Home Forums SQL Server 2008 SQL Server 2008 - General I know which *file* is being hammered -- how do I find out *who* is doing the hammering? RE: I know which *file* is being hammered -- how do I find out *who* is doing the hammering?

  • Someone previously provided methods to return what sessions are currently accessing the database and what execution plans consume the most read and writes.

    Something else that might be useful is the dm_db_index_usage_stats management view, which returns total reads, writes, and last access date/time at the object level. The following example will sort the result by aggregating reads and writes so it's easier to identify what specific tables or indexes are most heavily hit.

    use <DBNAME>;

    select

    db_name(s.database_id)db_name,

    object_name(s.object_id)object_name,

    (i.name)index_name,

    (user_seeks + user_scans + user_lookups + user_updates)total_reads_writes,

    *

    from sys.dm_db_index_usage_stats s

    left join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id

    where db_name(s.database_id) = '<DBNAME>'

    order by total_reads_writes desc;

    http://msdn.microsoft.com/en-us/library/ms188755(v=sql.105).aspx

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho