Skip to content

Latest commit

 

History

History
55 lines (34 loc) · 3.18 KB

columnstore-index-recommendations-in-database-engine-tuning-advisor-dta.md

File metadata and controls

55 lines (34 loc) · 3.18 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Columnstore index recommendations-Database Engine Tuning Advisor (DTA)
Learn how the Database Engine Tuning Advisor can analyze your workload and recommend rowstore and columnstore indexes to build on the database in SQL Server.
MikeRayMSFT
mikeray
01/09/2017
sql
performance
conceptual
Database Engine Tuning Advisor, columnstore index
Database Engine Tuning Advisor, columnstore and rowstore indexes

Columnstore index recommendations in Database Engine Tuning Advisor (DTA)

[!INCLUDE SQL Server]

Data warehousing and analytic workloads can greatly benefit from columnstore indexes as well as traditional rowstore indexes. The choice of which rowstore and columnstore indexes to build for your database is dependent on your application's workload. In SQL Server 2016, the Database Engine Tuning Advisor (DTA) can analyze your workload and recommend an appropriate combination of rowstore and columnstore indexes to build on the database.

This feature is available with SQL Server Management Studio version 16.4 or higher.

How to enable columnstore index recommendations in Database Engine Tuning Advisor GUI

  1. Launch Database Engine Tuning Advisor and open a new tuning session.

  2. Select database(s) and workload for tuning in the General pane.

  3. In the Tuning Options pane select the checkbox Recommend columnstore indexes (see figure below). DTA Columnstore indexes Tuning Option

  4. Select other tuning options and click on Start Analysis button.

  5. Once tuning is complete, view all recommendations including any columnstore indexes in the Recommendations pane (see figure below).
    DTA columnstore index recommendation

  6. Select the Definition hyperlink to view the SQL Data Definition Language (DDL) statement that can create the recommended index. By default, DTA uses the suffix col in the name of columnstore indexes to make it easier to identify columnstore indexes (see figure below). DTA columnstore index definition

How to enable columnstore index recommendations in dta.exe Utility

To enable columnstore recommendations when using dta.exe command line utility, use the -fc command line parameter.

For more information on dta.exe command line utility, see dta Utility

See Also

Columnstore Indexes Guide
Database Engine Tuning Advisor
Tutorial: Database Engine Tuning Advisor