This object provides very useful stats on how many times an index has been used, locked, waited on, etc. An index in this context can mean several things: a clustered index, heap, index, or a partition of either of these. The grain of the function is down to the partition level, so if you are working with a table that is partitioned into five parts, it will return 5 rows (In the previous section, sys.dm_db_index_usage_stats would have seen the object as only a single row)

This object will give you a deep feel for how indexes are being used, and just exactly how much the index is costing you. It does this in part by telling you how often the index is modified at the Leaf level or non-leaf level, as well as how often users waited on blocks associated with the object, which can mean SQL Server locks, or even hardware or I/O latches.

Type: function

Parameters: database_id , object_id, index_id, partition_number (each parameter can be NULL or DEFAULT if you want to return all rows)

Data: accumulating refreshed when server is restarted or (perhaps obviously) when the index is dropped and recreated. Statistics live on when the index is rebuild, reorganized, and even when it is disabled and rebuilt.

Columns:

• database_id, object_id, index_id -identifies the index in question
• partition_number – 1 for non-partitioned tables. For partitioned tables will be the 1 – based number of the partition.
• leaf_insert_count – the number of times a new row was added to the leaf of the index
• leaf_delete_count – the number of times a row was deleted from the leaf
• leaf_update_count – the number of changes to index keys for this index
• leaf_ghost_count – the number of ghost records that have been marked as deleted but not cleaned up by SQL Server (http://www.sql-server-performance.com/tips/glossary_p1.aspx)
• nonleaf_insert_count, nonleaf_delete_count, nonleaf_update_count – Same as the leaf page values, except for the b-tree index pages
• leaf_allocation_count, nonleaf_allocation_count – The number of page allocated to the leaf and non-leaf pages of the index, respectively.
• leaf_page_merge_count, nonleaf_page_merge_count – The number of pages in the index that have been merged into a single page.
• range_scan_count – number of times the index has been used in a range scan operator
• singleton_lookup_count – number of times the index has been used to fetch a single row
• forwarded_fetch_count – for a heap, shows the number of forward pointers have been used to resolve a query (http://sqlblog.com/blogs/hugo_kornelis/archive/2006/11/03/The-table-scan-from-hell.aspx)
• lob_fetch_in_pages, lob_fetch_in_bytes – Quantifies the number of large object (varchar(max), varbinary(max), text, etc) have been retrieved using this index
• lob_orphan_create_count, lob_orphan_insert_count – total number of large object pages orphaned by bulk operations
• row_overflow_fetch_in_pages , row_overflow_fetch_in_bytes – Quantifies the amount of data retrieved from row overflow pages for rows greater than one page in size
• column_value_push_off_row_count, column_value_pull_in_row_count – Number of pages of large object or row overflow pages that have been moved by an in-row data operation
• row_lock_count – number of row locks that have been requested against this index
• row_lock_wait_count – number of times a process has waited on a row lock against this index
• row_lock_wait_in_ms – amount of time spent waiting on a row lock against this index
• page_lock_count, page_lock_wait_count, page_lock_wait_in_ms – same as row_lock values at the page grain
• index_lock_promotion_attempt_count, index_lock_promotion_count – number of times the lock grain for an operation using this index was attempted or granted to be escalated (like from row to page)
• page_latch_wait_count , page_latch_wait_in_ms – number of waits and time waited on the physical page of the object to have the latch removed
• page_io_latch_wait_count, page_io_latch_wait_in_ms – number of waits and time waiting on and I/O operation on a physical page of the object to be completed
• This object is related to the sys.dm_db_index_usage_stats in that they both provide information about how the index is used. This object, however, gives more detailed information about the inner workings of how the index is used at a physical level, whereas the usage stats is more of a feel for how it is used by the optimizer to satisfy the needs of certain queries. Use usage stats if you want counts of each usage, as each usage counts as 1. The operational stats object may have multiple values set for each type of activity recorded. (http://blogs.msdn.com/sqlcat/archive/2005/12/12/502735.aspx)

Examples:

Tables where the most latch contention is occurring

select object_schema_name(ddios.object_id) + ‘.’ + object_name(ddios.object_id) as objectName,
          indexes.name, case when is_unique = 1 then ‘UNIQUE ‘ else ” end + indexes.type_desc as index_type,
          page_latch_wait_count , page_io_latch_wait_count
from  sys.dm_db_index_operational_stats(db_id(),null,null,null) as ddios
             join sys.indexes
                        on indexes.object_id = ddios.object_id
                             and indexes.index_id = ddios.index_id
order by page_latch_wait_count + page_io_latch_wait_count desc

Note:  This is part of an ongoing project to write a book about all of the dynamic management views for Red-Gate. It will be freely distributable once complete as an ebook. Any examples, suggestions, corrections are desired and will certainly be mentioned in the final book.  Also, I will begin maintaining the following web page once the book is closer to completion: http://drsql.org/dmvbook.aspx.