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:
- Analyze indexes, Use Included Columns
- Use Indexing for Data Warehousing Data Sets to Optimize Query Performance
- 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:
- Creation of File Groups
- Creation of Partition function
- Creation of Partition Scheme
- Creation of table for the partition
See the full benefits of our immersive learning experience with interactive courses and guided career paths.