Working With SQL Server Columnstore Indexes (RTM Update)

In this lab, you will learn to use SQL Server 2012 new columnstore index feature to accelerate the performance of typical data warehouse queries involving a centralized fact table and associated dimension tables.

Overview

In this lab, you will learn to use SQL Server 2012 new columnstore index feature to accelerate the performance of typical data warehouse queries involving a centralized fact table and associated dimension tables. You will start with measuring the performance of a specific query against a fact table and its associated dimensions without and then with a columnstore index defined on the fact table. You will learn how to create a columnstore index and how to examine the statistics and metadata associated with it. While the query optimizer will most often suffice in selecting the appropriate index, this lab will also teach you how to use hints to explicitly bypass or force the use of a specific columnstore index for a query. You will then walk through two techniques for updating data for a table with a columnstore index, including how to use partitioning to switch in and out large data sets.

You can find all the code for this lab in the Source\Assets folder, one file for each exercise.

Objectives

After completing the exercises in this lab, you will be able to:

• Create a columnstore index
• Measure the performance impact of queries both before and after the use of a columnstore index
• Learn how to identify if a columnstore index is being used for a query and if that query is using either the batch or row execution mode
• Explore statistics and metadata related to the columnstore index
• Use hints to bypass or force the use of a specific columnstore index
• Use techniques to update data in a table that uses a columnstore index

System Requirements

You must have the following items to complete this lab:

• Microsoft SQL Server 2012
• Microsoft SQL Server Management Studio
• The AdventureWorksDW2012 database

Setup

This section is required to make changes to the schema of the AdventureWorksDW2012 database. After completing the lab, it can be restored following the steps in the cleanup section. This process may take up to 5 minutes to complete.

1. Open a Windows Explorer window and browse to the lab’s folder.
2. Right click on it, and click Properties.
3. In the Security tab, click Edit.
4. In the opened window, click Add.
5. Add Everyone role to the folder’s permissions and click Ok.
6. Grant Full Control to it by checking the Full Control Checkbox and click Ok.
7. Click Ok.
8. Browse to the lab’s Source folder.
9. Double-click the Setup.cmd file in this folder to launch the setup script and run all setup tasks for this lab.
10. If the User Account Control dialog is shown, confirm the action to proceed.
11. In the Server Alias dialog that pops up, select the name of the SQL instance to be used, and click OK.
12. Click Close once the alias is created, and wait for the setup process to complete.

Cleanup

There is no need to cleanup if you don’t intend to continue working with another Hands-On Labs. If you want to make the cleanup, after completing lab reproduce the following steps in order to restore your database:

1. Open a Windows Explorer window and browse to the lab’s Source\Setup folder.
2. Double-click the Cleanup.cmd file in this folder to launch the setup script and run all setup tasks for this lab.
3. If the User Account Control dialog is shown, confirm the action to proceed.

Exercises

This Hands-On Lab comprises the following exercises:

• Creating a columnstore index and measuring the performance impact
• Using hints to bypass or force the use of columnstore indexes
• Exploring columnstore index metadata and statistics
• Using two techniques for updating data for a table that uses a columnstore index

Estimated time to complete this lab: 45 minutes.