Wednesday, October 7, 2009

Dynamic Management Views

As with a lot of the new features we have seen with SQL Server 2005 and now with SQL Server 2008, Microsoft introduced a few more dynamic management views to also assist with identifying possible index candidates based on query history.

The dynamic management views are:
• sys.dm_db_missing_index_details - Returns detailed information about a missing index
• sys.dm_db_missing_index_group_stats - Returns summary information about missing index groups
• sys.dm_db_missing_index_groups - Returns information about a specific group of missing indexes
• sys.dm_db_missing_index_columns(index_handle) - Returns information about the database table columns that are missing for an index. This is a function and requires the index_handle to be passed.


How to get index usage information in SQL Server
sys.dm_db_index_operational_stats
This view gives you information about insert, update and delete operations that occur on a particular index. In addition, this view also offers data about locking, latching and access methods. There are several columns that are returned from this view, but these are some of the more interesting columns:
• leaf_insert_count - total count of leaf level inserts
• leaf_delete_count - total count of leaf level inserts
• leaf_update_count - total count of leaf level updates
sys.dm_db_index_usage_stats
This view gives you information about overall access methods to your indexes. There are several columns that are returned from this DMV, but here are some helpful columns about index usage:
• user_seeks - number of index seeks
• user_scans- number of index scans
• user_lookups - number of index lookups
• user_updates - number of insert, update or delete operations