SQL Index dm_db_index_xxx_stats

There are 3 dm_db_index_xxx_stats objects that can be used to check and investigate index information.

sys.dm_db_index_usage_stats can be used to see how beneficial the index is, and its related maintenance cost. Note that this information is reset upon instance restart.

The final two index stats objects provide information on the physical and operational statistics of each index. This can be used to check for fragmentation, page splits and can be useful when deciding on factors such as fill factor.

sys.dm_db_index_operational_stats
sys.dm_db_index_physical_stats
 

--http://jongurgul.com/blog/sql-index-stats-queries
SELECT
 SCHEMA_NAME(ao.[schema_id]) [SchemaName]
,ao.[object_id] [ObjectID]
,ao.[name] [ObjectName]
,ao.[is_ms_shipped] [IsSystemObject]
,i.[index_id] [IndexID]
,i.[name] [IndexName]
,i.[type_desc] [IndexType]
,ddius.[user_scans] [UserScans]
,ddius.[user_seeks] [UserSeeks]
,ddius.[user_lookups] [UserLookups]
,ddius.[user_updates] [UserUpdates]
FROM sys.all_objects ao
INNER JOIN sys.indexes i ON ao.[object_id] = i.[object_id]
LEFT OUTER JOIN sys.dm_db_index_usage_stats ddius ON i.[object_id] = ddius.[object_id] AND i.[index_id] = ddius.[index_id] AND ddius.[database_id] = DB_ID() 
--stats reset upon server restart
WHERE ao.[is_ms_shipped] = 0
--http://jongurgul.com/blog/sql-index-stats-queries
SELECT 
 SCHEMA_NAME(ao.[schema_id]) [SchemaName]
,ao.[object_id] [ObjectID]
,ao.[name] [ObjectName]
,ao.[is_ms_shipped] [IsSystemObject]
,i.[index_id] [IndexID]
,i.[name] [IndexName]
,ddios.[partition_number] [PartitionNumber]
,i.[type_desc] [IndexType]
,ddios.[leaf_insert_count]--Cumulative count of leaf-level inserts.
,ddios.[leaf_delete_count]--Cumulative count of leaf-level deletes. 
,ddios.[leaf_update_count]--Cumulative count of leaf-level updates. 
,ddios.[leaf_ghost_count]--Cumulative count of leaf-level rows that are marked as deleted, but not yet removed.
--These rows are removed by a cleanup thread at set intervals. This value does not include rows that are retained, because of an outstanding snapshot isolation transaction. 
,ddios.[nonleaf_insert_count] [NonleafInsertCount]--Cumulative count of inserts above the leaf level.
,ddios.[nonleaf_delete_count] [NonleafDeleteCount]--Cumulative count of deletes above the leaf level.
,ddios.[nonleaf_update_count] [NonleafUpdateCount]--Cumulative count of updates above the leaf level.
,ddios.[leaf_allocation_count] [LeafAllocationCount]--Cumulative count of leaf-level page allocations in the index or heap.For an index, a page allocation corresponds to a page split.
,ddios.[nonleaf_allocation_count] [NonLeafAllocationCount]--Cumulative count of page allocations caused by page splits above the leaf level. 
,ddios.[range_scan_count] [RangeScanCount]--Cumulative count of range and table scans started on the index or heap.
,ddios.[singleton_lookup_count] [SingletonLookupCount]--Cumulative count of single row retrievals from the index or heap. 
,ddios.[forwarded_fetch_count] [ForwardedFetchCount]--Count of rows that were fetched through a forwarding record. 
,ddios.[lob_fetch_in_pages] [LobFetchInPages]--Cumulative count of large object (LOB) pages retrieved from the LOB_DATA allocation unit.
,ddios.[row_overflow_fetch_in_pages] [RowOverflowFetchInPages]--Cumulative count of column values for LOB data and row-overflow data that is pushed off-row to make an inserted or updated row fit within a page. 
,ddios.[page_lock_wait_count] [PageLockWaitCount]--Cumulative number of times the Database Engine waited on a page lock.
,ddios.[page_lock_wait_in_ms] [PageLockWaitIn_ms]--Total number of milliseconds the Database Engine waited on a row lock.
,ddios.[row_lock_wait_count] [RowLockWaitCount]--Cumulative number of times the Database Engine waited on a page lock.
,ddios.[row_lock_wait_in_ms] [RowLockWaitIn_ms]--Total number of milliseconds the Database Engine waited on a page lock.
,ddios.[index_lock_promotion_attempt_count] [IndexLockPromotionAttemptCount]--Cumulative number of times the Database Engine tried to escalate locks.
,ddios.[index_lock_promotion_count] [IndexLockPromotionCount]--Cumulative number of times the Database Engine escalated locks.
FROM sys.all_objects ao 
INNER JOIN sys.indexes i ON ao.[object_id] = i.[object_id] 
LEFT OUTER JOIN sys.dm_db_index_operational_stats(DB_ID(),NULL,NULL,NULL) ddios ON i.[object_id] = ddios.[object_id] AND i.[index_id] = ddios.[index_id]
WHERE ao.[is_ms_shipped] = 0
--http://jongurgul.com/blog/sql-index-stats-queries
SELECT
 DB_NAME() [DatabaseName]
,ao.[object_id] [ObjectID]
,SCHEMA_NAME(ao.[schema_id]) [SchemaName]
,ao.[name] [ObjectName]
,ao.[is_ms_shipped] [IsSystemObject]
,i.[index_id] [IndexID]
,i.[name] [IndexName]
,i.[type_desc] [IndexType]
,au.[type_desc] [AllocationUnitType]
,p.[partition_number] [PartitionNumber]
,ds.[type] [IsPartition]
--,p.[data_compression_desc] [Compression]
,ds.[name] [PartitionName]
,fg.[name] [FileGroupName]
,p.[rows] [NumberOfRows]
,CASE WHEN pf.[boundary_value_on_right] = 1 AND ds.[type] = 'PS' THEN 'RIGHT'
 WHEN pf.[boundary_value_on_right] IS NULL AND ds.[type] = 'PS' THEN 'LEFT'
 ELSE NULL
 END [Range]
,prv.[value] [LowerBoundaryValue]
,prv2.[value] [UpperBoundaryValue]
,CONVERT(DECIMAL(15,3),(CASE WHEN au.[type_desc] = 'IN_ROW_DATA' AND p.[rows] >0 THEN p.[rows]/au.[data_pages] ELSE 0 END)) [RowsPerPage]
,(CASE WHEN au.[type_desc] = 'IN_ROW_DATA' AND i.[type_desc] = 'CLUSTERED' THEN au.[used_pages]*0.20 ELSE NULL END) [TippingPointLower_Rows]
,(CASE WHEN au.[type_desc] = 'IN_ROW_DATA' AND i.[type_desc] = 'CLUSTERED' THEN au.[used_pages]*0.30 ELSE NULL END) [TippingPointUpper_Rows]
,au.[used_pages][UsedPages]
,CONVERT(DECIMAL (15,3),(CASE WHEN au.[type] <> 1 THEN au.[used_pages] WHEN p.[index_id] < 2 THEN au.[data_pages] ELSE 0 END)*0.0078125) [DataUsedSpace_MiB]
,CONVERT(DECIMAL (15,3),(au.[used_pages]-(CASE WHEN au.[type] <> 1 THEN au.[used_pages] WHEN p.[index_id] < 2 THEN au.[data_pages] ELSE 0 END))*0.0078125) [IndexUsedSpace_MiB]
,au.[data_pages] [DataPages]
,ddips.[avg_fragmentation_in_percent] [AverageFragementationPercent]
FROM
sys.partition_functions pf
INNER JOIN sys.partition_schemes ps ON pf.[function_id] = ps.[function_id]
RIGHT OUTER JOIN sys.partitions p
INNER JOIN sys.indexes i ON p.[object_id] = i.[object_id] AND p.[index_id] = i.[index_id]
INNER JOIN sys.allocation_units au ON au.[container_id] = p.[partition_id] AND au.[type_desc] = 'IN_ROW_DATA' 
INNER JOIN sys.filegroups fg ON au.[data_space_id] = fg.[data_space_id]
INNER JOIN sys.data_spaces ds ON i.[data_space_id] = ds.[data_space_id]
INNER JOIN sys.all_objects ao ON i.[object_id] = ao.[object_id] ON ps.[data_space_id] = ds.[data_space_id]
LEFT OUTER JOIN sys.partition_range_values prv ON ps.[function_id] = prv.[function_id] AND p.[partition_number] - 1 = prv.[boundary_id]
LEFT OUTER JOIN sys.partition_range_values prv2 ON ps.[function_id] = prv2.[function_id] AND prv2.[boundary_id] = p.[partition_number]
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') ddips ON i.[object_id] = ddips.[object_id] AND i.[index_id] = ddips.[index_id] AND ddips.[alloc_unit_type_desc] = 'IN_ROW_DATA'
WHERE ao.[is_ms_shipped] = 0