The Optimizing Indexes module provides you with the instructions and devices to develop your hands-on skills in the following topics: Querying dynamic management objects to verify current index usage, Identifying missing indexes using dynamic management objects.
The Optimizing Indexes module provides you with the instructions and devices to develop your hands-on skills in the following topics.
- Querying dynamic management objects to verify current index usage
- Identifying missing indexes using dynamic management objects
Lab time: It will take approximately 40 minutes to complete this lab.
Two exam objectives are covered in this lab.
- Demonstrate how to query dynamic management objects to obtain information about the current index usage
- Demonstrate how to identify missing indexes using dynamic management objects
Exercise 1 - Querying Dynamic Management Objects to Verify Current Index Usage
Dynamic Management Objects (DMOs) are a set of SQL server objects. They are stored in the system schema. They help you monitor the activities of the server instances. They track the resources used by the server instances activities.
The DMOs provide information regarding the connections, sessions, transactions, and SQL statements, processes and so on of a database schema. In addition, the DMOs also track the server workload, workload distribution, high workload areas and so on. You can use the DMO provided information to troubleshoot in case of a breakdown in any of the server activities.
The troubleshooting process can include altering the query statement, gaining information about the indexes to optimize indexing strategy or terminating a blocked session.
The DMOs extract the information and display in the form of views known as Dynamic Management Views (DMVs). The DMOs also display the information as table-valued functions known as Dynamic Management Functions (DMFs).
In this exercise, you will learn to use DMOs to obtain information about the current index usage.
Exercise 2 - Identifying Missing Indexes Using Dynamic Management Objects
You can use two methods to identify missing indexes. The methods are as follows:
- Query the DMO to return the information on missing indexes
- Include the execution plan of the query to identify the missing indexes
You can use the following DMOs to help identify the missing indexes:
- sys.dbdmmissingindexgroup_stats: It provides summarized information about the missing index groups.
- sys.dbdmmissingindexgroups: It gives information about the missing indexes included in a specific group of missing indexes.
- sys.dbdmmissingindexdetails: It displays detailed information about the missing indexes.
- sys.dbdmmissingindexcolumns: It gives information about the database table fields that are not having an index.
In this exercise, you will learn to use execution plan and DMO to identify missing indexes.
IT & Cybersecurity certification hands on practice labs and practice exams for certifications and skill development.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.