A DMV a Day – Day 17

The DMV for Day 17 is sys.dm_db_index_usage_stats, which is described by BOL as:

Returns counts of different types of index operations and the time each type of operation was last performed.

This particular DMV works with SQL Server 2005, 2008, and 2008 R2. It requires VIEW SERVER STATE permission.

    --- Index Read/Write stats (all tables in current DB)
    SELECT OBJECT_NAME(s.[object_id]) AS [ObjectName], i.name AS [IndexName], i.index_id,
           user_seeks + user_scans + user_lookups AS [Reads], user_updates AS [Writes],
           i.type_desc AS [IndexType], i.fill_factor AS [FillFactor]
    FROM sys.dm_db_index_usage_stats AS s
    INNER JOIN sys.indexes AS i
    ON s.[object_id] = i.[object_id]
    WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
    AND i.index_id = s.index_id
    AND s.database_id = DB_ID()
    ORDER BY OBJECT_NAME(s.[object_id]), writes DESC, reads DESC;

This query will list all of your heap tables, clustered indexes, and non-clustered indexes, along with the number of reads, writes, and the fill factor for each index. It is very useful for better understanding your workload. You can use it to help determine how volatile a particular index is, and the ratio of reads to writes. This can help you better tune your indexing strategy. For example, if you had a table that was pretty static (very few writes on any of the indexes), you could feel more confident about adding more indexes that were are listed in your missing index queries. If you have SQL Server 2008 Enterprise Edition, this query could help you decide whether it would be a good idea to enable Page Compression.

This entry was posted in SQL Server 2008. 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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s