Exercise 2: Using Hints to Bypass or Force Columnstore Index Usage

In this exercise, you will learn how to use hints to bypass or explicitly forcecolumnstore index usage. Ideally, you will let the SQL Server query optimizer automatically select indexes based on query cost optimization; however, there may be circumstances where you choose to bypass the automatic index selection choice.

At the end of this exercise, you will understand:

How to have a query ignore the availability of a columnstore index and use an alternative index (or heap)

How to force a query to use a columnstore index

Task 1 – Forcing a Query not to Use the Available Columnstore Index

1. In order to see the actual graphical execution plan of the query, enable this option by going to Query | Include Actual Execution Plan

2. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to execute the following query that uses the IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX hint:

   SQL

   SELECT d.CalendarYear, MAX(fpi.UnitsOut) FROM dbo.FactProductInventory as fpi INNER JOIN dbo.DimDate as d ON fpi.DateKey = d.FullDateAlternateKey WHERE d.CalendarYear >= 2000 GROUP BY d.CalendarYear OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

You should see the following execution plan and confirmation that the columnstore index was not used:

Task 2 – Forcing a Query to use the Available Columnstore Index

1. Copy and paste the following code into the New Query area of the SQL Management and press F5 in order to execute the following query that uses the INDEX hint:

   SQL

   SELECT d.CalendarYear, MAX(fpi.UnitsOut) FROM dbo.FactProductInventory as fpi WITH (INDEX(IX_CS_FactProductInventory)) INNER JOIN dbo.DimDate as d ON fpi.DateKey = d.FullDateAlternateKey WHERE d.CalendarYear >= 2000 GROUP BY d.CalendarYear

   You should see the following execution plan and confirmation that the columnstore index was used:

   Figure 2

   Columnstore Index Scan

2. Disable viewing the actual execution plan by going to Query | Include Actual Execution Plan.