ColumnStore Index Recommendations using SQL Server 2016 Upgrade Advisor

By:   |   Comments   |   Related: > SQL Server 2016


This tip, Prepare for an Upgrade with the SQL Server 2016 Upgrade Advisor described the new SQL Server 2016 Upgrade Advisor. Can we use the new SQL Server 2016 Upgrade Advisor to get recommendations on creating column store indexes in a database?


You can download the latest SQL Server 2016 Upgrade Advisor from this link. This is still a preview version, so you may not want to install it directly on a production or important server as this product is subject to change. After you install, launch the SQL Server 2016 Upgrade Advisor.

Once you launch the Upgrade Advisor, you will see the below screen. The scenarios tab shows the options that are available. Note the options are named differently as compared to the ones we had earlier and described in this tip Prepare for an Upgrade with the SQL Server 2016 Upgrade Advisor.

SQL Server 2016 Upgrade Advisor Launch Screen

In this tip, we will learn how to use the ColumnStore Advisor feature of this tool. In order to try this, we can first download the sample "AdventureWorks" databases that are available from Microsoft. The sample databases can be downloaded from this link. For this tip, we can download the items checked as shown below.

SQL Server 2016 Sample Database Download

Restore the backup file AdventureWorksDW2016CTP3.bak to a SQL Server 2016 instance. Connect to the SQL Server 2016 Upgrade Advisor and click on the "Run In-Memory OLTP and ColumnStore Advisor". Once done, connect to the SQL Server instance as shown.

Run the SQL Server 2016 Column Store Advisor

After the connection is made to the SQL Server 2016 instance, select the database for which the columnstore advisor needs to be run.

Select the SQL Server Database to Connect to

In our case, it is the AdventureWorksDW2016CTP3 database. After selecting the database, click on the "Run" option which will perform the analysis. Once the analysis is done, results of the recommendations will be displayed as shown.

In-Memory OLTP and Column Store Advisor Results Without a Workload

In our case, there are 0 recommendations as there was no workload for the Upgrade Advisor to analyze. However, we can simulate a scenario by which we can verify if the columnstore Upgrade Advisor will provide recommendations. From the restored database AdventureWorksDW2016CTP3, we can see that there is a table with the name "FactResellerSalesXL_CCI" as shown in the screenshot. 

dbo.FactResellerSalesXL_CCI Table in SQL Server Management Studio

In order to test our scenario (since there is no workload at the moment), right click and delete the Clustered ColumnStore index [IndFactResellerSalesXL_CCI] as shown in the screenshot below. A word of caution, try this experiment on a test server using the AdventureWorksDW2016CTP3 database as dropping this clustered columnstore index will take quite a while and it is resource intensive.

Delete a SQL Server ColumnStore Index

After the Clustered ColumnStore Index is deleted, run the scenario once again as described above to get recommendations from the columnstore advisor. Once done, you will get a recommendation from the Columnstore advisor as shown.

In-Memory OLTP and Column Store Advisor Results With a Missing ColumnStore Index

Once you click on the highlighted section, it will show the full details of the recommendations for creating the clustered columnstore index as shown below. From the "Suggested Actions" section, you can see the actual queries that can be used as per the recommendations.

SQL Server Clustered ColumnStore Index Scripts

When you downloaded the sample databases from this link, you will have also downloaded the file w which contains a number of sample scripts. Once the contents of the folder are extracted, you will see a folder named "In-Memory Analytics". You can use these scripts from this folder to walk through some more examples using the ColumnStore scripts.

In Memory Analytics Folder for SQL Server ColumnStore scripts
Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms