Index Related Dynamic Management Views and Functions

Dynamic Management Views (DMV) was introduced in SQL Server 2005 and every SQL Server release Microsoft introduces additional DMVs, which helps to provide information about health of a Server instance, performance and Server issues.

There are two types of DMV, the first type is server-scoped DMV which requires VIEW SERVER STATE permission on the Server and the second type is database-scoped DMV which requires VIEW DATABASE STATE permission on the Database.

In this post I will give an overview of Index Related Dynamic Management Views, this is part of my study to Exam 70-473, that requires a good knowledge on all DMVs.

DMV works like a view where you can select data and DMF works like a function where you need to pass values.

sys.dm_db_column_store_row_group_physical_stats: (DMV) You can use this DMV to check the physical structure of a columnstore row group in the database. Using this DMV you can easily calculate fragmentation.
Only applicable to SQL Server 2016+ and Azure SQL DB

sys.dm_db_index_operational_stats: (DMF) You can use this DMF to get information on how long end users are waiting to read or write to a table, index or partition. Also, identify performance issues caused by locks and latches.
Only applicable to SQL Server 2008+, Azure SQL DB, Azure SQL DW and APS (PDW)

sys.dm_db_index_operational_stats (
{ database_id | NULL | 0 | DEFAULT }
, { object_id | NULL | 0 | DEFAULT }
, { index_id | 0 | NULL | -1 | DEFAULT }
, { partition_number | NULL | 0 | DEFAULT }
)

sys.dm_db_index_usage_stats: (DMV) You can use this DMV to get information about indexes usage and it can helps to identify not used indexes. In Azure SQL Data Warehouse or APS/PDW you should use the following DMV sys.dm_pdw_nodes_db_index_usage_stats.
Only applicable to SQL Server 2008+, Azure SQL DB, Azure SQL DW and APS (PDW)

sys.dm_db_missing_index_details: (DMV) You can use this DMV to get detailed information about indexes that would have been used if they exist, excluding spatial indexes.
Only applicable to SQL Server 2008+ & Azure SQL DB

sys.dm_db_missing_index_groups: (DMV) You can use this DMV to get information about missing indexes contained in a missing index group, excluding spatial indexes.
Only applicable to SQL Server 2008+ & Azure SQL DB

sys.dm_db_missing_index_group_stats: (DMV) You can use this DMV to get summary information about missing index groups, excluding spatial indexes.
Returns summary information about groups of missing indexes, excluding spatial indexes.
Only applicable to SQL Server 2008+ & Azure SQL DB

sys.dm_db_missing_index_columns: (DMF) You can use this DMF to get information about database table columns that are missing an index, excluding spatial indexes.
Only applicable to SQL Server 2008+ & Azure SQL DB

sys.dm_db_index_physical_stats: (DMF) You can use this DMF to get information about indexes size and fragmentation. This DMF does not return information about memory-optimized indexes.
Only applicable to SQL Server 2008+ & Azure SQL DB