By: Mohammed Moinudheen | Comments | Related: > SQL Server 2016
Problem
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?
Solution
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.
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.
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.
After the connection is made to the SQL Server 2016 instance, select the database for which the columnstore advisor needs to be run.
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 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.
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.
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.
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.
When you downloaded the sample databases from this link, you will have also downloaded the file SQLServer2016CTP3Samples.zip 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.
Next Steps
- Download the latest SQL Server 2016 Upgrade Advisor from this link.
- Try testing this tip using the steps described above.
- Refer to these useful tips on SQL Server migrations and upgrades.
About the author
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
