Optimize Indexing Strategies

Practice Labs Module
Time
50 minutes
Difficulty
Intermediate

The "Optimize Indexing Strategies" module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises: 1) Analyze indexes, Use Included Columns, 2) Use Indexing for Data Warehousing Data Sets to Optimize Query Performance, 3) Use Indexing for OLTP Data...

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 Optimize Indexing Strategies module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

  1. Analyze indexes, Use Included Columns

  2. Use Indexing for Data Warehousing Data Sets to Optimize Query Performance

  3. Use Indexing for OLTP Data Sets to Optimize Query Performance

Exercise 1- Analyze indexes, Use Included Columns

In this exercise, you will learn the following in Microsoft SQL Server 2012 R2:

  • Analyze the usage of current indexes on a table
  • Demonstrate creating indexes with included columns

Exercise 2 - Use Indexing for Data Warehousing Data Sets to Optimize Query Performance

In this exercise, you will use columnstore index for data warehousing data sets to optimize query performance.

Exercise 3 - Use Indexing for OLTP Data Sets to Optimize Query Performance

In this exercise, you will create partitioned tables and indexes. The data in partitioned table or index is divided into smaller units and are spread across more than one file group in a database. This enables you to handle large tables and indexes efficiently.

You can optimize OLTP data sets query performance by creating partitions on the data range of a specified column or index. By doing so, depending upon the value specified in the partitioned column or index, the groups or rows that are mapped into individual partitions are only accessed while querying. This helps us in improving query performance. Typically the tasks involved in partitioning include:

  1. Creation of File Groups

  2. Creation of Partition function

  3. Creation of Partition Scheme

  4. Creation of table for the partition

Learning Partner
Comprehensive Learning

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