Optimizing Indexes

Practice Labs Module
Time
37 minutes
Difficulty
Intermediate

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.

Join over 3 million cybersecurity professionals advancing their career
Sign up with
Required fields are marked with an *
or

Already have an account? Sign In »

Overview

Introduction

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.

Exam Objectives

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.db_dm_missing_index_group_stats: It provides summarized information about the missing index groups.
  • sys.db_dm_missing_index_groups: It gives information about the missing indexes included in a specific group of missing indexes.
  • sys.db_dm_missing_index_details: It displays detailed information about the missing indexes.
  • sys.db_dm_missing_index_columns: 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.

Learning Partner
Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.