How to Determine the Number of Reads and Writes to a Table in SQL Server 2005

Here are a few more useful DMV queries that will give you a better idea which tables in your database are getting the most read and write activity. You need to have VIEW SERVER STATE permission to access the sys.dm_db_index_usage_stats DMV. Also, don’t forget that these stats are cleared out when SQL Server is restarted, when a database is detached, and also when a server is rebooted.

 

-- Return number of reads and writes for a given table
    DECLARE @dbid int
    SELECT @dbid = db_id()

    SELECT TableName = object_name(s.object_id),
           Reads = SUM(user_seeks + user_scans + user_lookups), Writes =  SUM(user_updates)
    FROM sys.dm_db_index_usage_stats AS s
    INNER JOIN sys.indexes AS i
    ON s.object_id = i.object_id
    AND i.index_id = s.index_id
    WHERE objectproperty(s.object_id,'IsUserTable') = 1
    AND s.database_id = @dbid
    AND object_name(s.object_id) = 'YourTableName' -- Table Name
    GROUP BY object_name(s.object_id)
 
-- Tables with the most writes
    DECLARE @dbid int
    SELECT @dbid = db_id()

    SELECT TableName = object_name(s.object_id),
           Reads = SUM(user_seeks + user_scans + user_lookups), Writes =  SUM(user_updates)
    FROM sys.dm_db_index_usage_stats AS s
    INNER JOIN sys.indexes AS i
    ON s.object_id = i.object_id
    AND i.index_id = s.index_id
    WHERE objectproperty(s.object_id,'IsUserTable') = 1
    AND s.database_id = @dbid
    GROUP BY object_name(s.object_id)
    ORDER BY writes DESC

 

-- Tables with the most reads
    DECLARE @dbid int
    SELECT @dbid = db_id()

    SELECT TableName = object_name(s.object_id),
           Reads = SUM(user_seeks + user_scans + user_lookups), Writes =  SUM(user_updates) 
            
    FROM sys.dm_db_index_usage_stats AS s
    INNER JOIN sys.indexes AS i
    ON s.object_id = i.object_id
    AND i.index_id = s.index_id
    WHERE objectproperty(s.object_id,'IsUserTable') = 1
    AND s.database_id = @dbid
    GROUP BY object_name(s.object_id)
    ORDER BY reads DESC
 
Technorati Tags: ,

This entry was posted in SQL Server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s