You have Microsoft SQL Server on a Microsoft Azure virtual machine.
You suspect that the current SQL Server indexes cause queries to execute slowly.
You need to identify which indexes must be created to reduce the query execution time. Which three dynamic management views should you use? Each correct answer presents part of the solution.
NOTE:
Each correct selection is worth one point.
A. sys.dm_db_index_physical_stats
B. sys.dm_db_missing_index_group_stats
C. sys.indexes
D. sys.dm_db_index_usage_stats
E. sys.dm_db_missing_index_groups
F. sys.dm_db_index_operational_stats
G. sys.dm_db_missing_index_details
H. sys.sysindexkeys
Explanation:
The missing indexes feature consists of the following components:
A set of dynamic management objects that can be queried to return information about missing indexes.
The MissingIndexes element in XML Showplans, which correlate indexes that the query optimizer considers missing with the queries for which they are missing.
Dynamic Management Objects
After running a typical workload on SQL Server, you can retrieve information about missing indexes by querying the dynamic management objects listed in the following table. These dynamic management objects are stored in the master database.
- sys.dm_db_missing_index_group_stats
Returns summary information about missing index groups, for example, the performance improvements that could be gained by implementing a specific group of missing indexes.
- sys.dm_db_missing_index_groups
Returns information about a specific group of missing indexes, such as the group identifier and the identifiers of all missing indexes that are contained in that group.
- sys.dm_db_missing_index_details
Returns detailed information about a missing index; for example, it returns the name and identifier of the table where the index is missing, and the columns and column types that should make up the missing index.
- sys.dm_db_missing_index_columns
Returns information about the database table columns that are missing an index.
References: https://technet.microsoft.com/en-us/library/ms345524(v=sql.105).aspx