Database Engine Tuning Advisor

The Microsoft Database Engine Tuning Advisor (DTA) analyzes databases and makes recommendations that you can use to optimize query performance. You can use the Database Engine Tuning Advisor to select and create an optimal set of indexes, indexed views, or table partitions without having an expert understanding of the database structure or the internals of SQL Server. Using the DTA, you can perform the following tasks.

  • Troubleshoot the performance of a specific problem query
  • Tune a large set of queries across one or more databases
  • Perform an exploratory what-if analysis of potential physical design changes
  • Manage storage space

Database Engine Tuning Advisor Benefits

Optimizing query performance can be difficult without a full understanding the database structure and the queries that are run against the database. The Database Engine Tuning Advisor (DTA) can make this task easier by analyzing the current query plan cache or by analyzing a workload of Transact-SQL queries that you create and recommending an appropriate physical design. For more advanced database administrators, DTA exposes a powerful mechanism to perform exploratory what-if analysis of different physical design alternatives. The DTA can provide the following information.

  • Recommend the best mix of rowstore and columnstore indexes for databases by using the query optimizer to analyze queries in a workload.
  • Recommend aligned or non-aligned partitions for databases referenced in a workload.
  • Recommend indexed views for databases referenced in a workload.
  • Analyze the effects of the proposed changes, including index usage, query distribution among tables, and query performance in the workload.
  • Recommend ways to tune the database for a small set of problem queries.
  • Allow you to customize the recommendation by specifying advanced options such as disk space constraints.
  • Provide reports that summarize the effects of implementing the recommendations for a given workload.
  • Consider alternatives in which you supply possible design choices in the form of hypothetical configurations for Database Engine Tuning Advisor to evaluate.
  • Tune workloads from a variety of sources including SQL Server Query Store, Plan Cache, SQL Server Profiler Trace file or table, or a .SQL file.

The Database Engine Tuning Advisor is designed to handle the following types of query workloads:

  • Online transaction processing (OLTP) queries only
  • Online analytical processing (OLAP) queries only
  • Mixed OLTP and OLAP queries
  • Query-heavy workloads (more queries than data modifications)
  • Update-heavy workloads (more data modifications than queries)

DTA Components and Concepts

Database Engine Tuning Advisor Graphical User Interface
An easy-to-use interface in which you can specify the workload and select various tuning options.

dta Utility
The command prompt version of Database Engine Tuning Advisor. The dta utility is designed to allow you to use Database Engine Tuning Advisor functionality in applications and scripts.

workload
A Transact-SQL script file, trace file, or trace table that contains a representative workload for the databases you want to tune. Beginning with SQL Server 2012 (11.x), you can specify the plan cache as the workload. Beginning with SQL Server 2016 (13.x), you can specify the Query Store as the workload.

XML input file
A XML-formatted file that Database Engine Tuning Advisor can use to tune workloads. The XML input file supports advanced tuning options that are not available in either the GUI or dta utility.

Limitations and Restrictions

The Database Engine Tuning Advisor has the following limitations and restrictions.

  • It cannot add or drop unique indexes or indexes that enforce PRIMARY KEY or UNIQUE constraints.
  • It cannot analyze a database that is set to single-user mode.
  • If you specify a maximum disk space for tuning recommendations that exceeds the actual available space, Database Engine Tuning Advisor uses the value you specify. However, when you execute the recommendation script to implement it, the script may fail if more disk space is not added first. Maximum disk space can be specified with the -B option of the dta utility, or by entering a value in the Advanced Tuning Options dialog box.
  • For security reasons, Database Engine Tuning Advisor cannot tune a workload in a trace table that resides on a remote server. To work around this limitation, you can use a trace file instead of a trace table or copy the trace table to the remote server.
  • When you impose constraints, such as those imposed when you specify a maximum disk space for tuning recommendations (by using the -B option or the Advanced Tuning Options dialog box), Database Engine Tuning Advisor may be forced to drop certain existing indexes. In this case, the resulting Database Engine Tuning Advisor recommendation may produce a negative expected improvement.
  • When you specify a constraint to limit tuning time (by using the -A option with the dta utility or by checking Limit tuning time on the Tuning Options tab), Database Engine Tuning Advisor may exceed that time limit to produce an accurate expected improvement and the analysis reports for whatever portion of the workload has been consumed so far.
  • Database Engine Tuning Advisor might not make recommendations under the following circumstances:
    • The table being tuned contains less than 10 data pages.
    • The recommended indexes would not offer enough improvement in query performance over the current physical database design.
    • The user who runs Database Engine Tuning Advisor is not a member of the db_owner database role or the sysadmin fixed server role. The queries in the workload are analyzed in the security context of the user who runs the Database Engine Tuning Advisor. The user must be a member of the db_owner database role.
  • Database Engine Tuning Advisor stores tuning session data and other information in the msdb database. If changes are made to the msdb database you may risk losing tuning session data. To eliminate this risk, implement an appropriate backup strategy for the msdb database.

Performance Considerations

Database Engine Tuning Advisor can consume significant processor and memory resources during analysis. To avoid slowing down your production server, follow one of these strategies:

  • Tune your databases when your server is free. Database Engine Tuning Advisor can affect maintenance task performance.
  • Use the test server/production server feature.
  • Specify only the physical database design structures you want Database Engine Tuning Advisor to analyze. Database Engine Tuning Advisor provides many options, but specifies only those that are necessary.

Dependency on xp_msver Extended Stored Procedure

Database Engine Tuning Advisor depends on the xp_msver extended stored procedure to provide full functionality. This extended stored procedure is turned on by default. Database Engine Tuning Advisor uses this extended stored procedure to fetch the number of processors and available memory on the computer where the database that you are tuning resides. If xp_msver is unavailable, Database Engine Tuning Advisor assumes the hardware characteristics of the computer where Database Engine Tuning Advisor is running. If the hardware characteristics of the computer where Database Engine Tuning Advisor is running are not available, one processor and 1024 megabytes (MBs) of memory are assumed.

This dependency affects partitioning recommendations because the number of partitions recommended depends on these two values (number of processors and available memory). The dependency also affects your tuning results when you use a test server to tune your production server. In this scenario, Database Engine Tuning Advisor uses xp_msver to fetch hardware properties from the production server. After tuning the workload on the test server, Database Engine Tuning Advisor uses these hardware properties to generate a recommendation.

Reduce the Production Server Tuning Load

Database Engine Tuning Advisor relies on the query optimizer to analyze a workload and to make tuning recommendations. Performing this analysis on the production server adds to the server load and can hurt server performance during the tuning session. You can reduce the impact to the server load during a tuning session by using a test server in addition to the production server.

How Database Engine Tuning Advisor Uses a Test Server

The traditional way to use a test server is to copy all of the data from your production server to your test server, tune the test server, and then implement the recommendation on your production server. This process eliminates the performance impact on your production server, but nevertheless is not the optimal solution. For example, copying large amounts of data from the production to the test server can consume substantial amounts of time and resources. In addition, test server hardware is seldom as powerful as the hardware that is deployed for production servers. The tuning process relies on the query optimizer, and the recommendations it generates are based in part on the underlying hardware. If the test and production server hardware are not identical, the Database Engine Tuning Advisor recommendation quality is diminished.

To avoid these problems, Database Engine Tuning Advisor tunes a database on a production server by offloading most of the tuning load onto a test server. It does this by using the production server hardware configuration information and without actually copying the data from the production server to the test server. Database Engine Tuning Advisor does not copy actual data from the production server to the test server. It only copies the metadata and necessary statistics.

The following steps outline the process for tuning a production database on a test server:

  1. Make sure that the user who wants to use the test server exists on both servers.

Before you start, make sure that the user who wants to use the test server to tune a database on the production server exists on both servers. This requires that you create the user and his or her login on the test server. If you are a member of the sysadmin fixed server role on both computers, this step is not necessary.

  • Tune the workload on the test server.

To tune a workload on a test server, you must use an XML input file with the dta command-line utility. In the XML input file, specify the name of your test server with the TestServer subelement in addition to specifying the values for the other subelements under the TuningOptions parent element.

During the tuning process, Database Engine Tuning Advisor creates a shell database on the test server. To create this shell database and tune it, Database Engine Tuning Advisor makes calls to the production server for the following:

  1. Database Engine Tuning Advisor imports metadata from the production database to the test server shell database. This metadata includes empty tables, indexes, views, stored procedures, triggers, and so on. This makes it possible for the workload queries to execute against the test server shell database.
    1. Database Engine Tuning Advisor imports statistics from the production server so the query optimizer can accurately optimize queries on the test server.
    1. Database Engine Tuning Advisor imports hardware parameters specifying the number of processors and available memory from the production server to provide the query optimizer with the information it needs to generate a query plan.
  2. After Database Engine Tuning Advisor finishes tuning the test server shell database, it generates a tuning recommendation.
  3. Apply the recommendation received from tuning the test server to the production server.

The following illustration shows the test server and production server scenario:

Example

First, make sure that the user who wants to perform the tuning exists on both the test and production servers.

After the user information is copied over to your test server, you can define your test server tuning session in the Database Engine Tuning Advisor XML input file. The following example XML input file illustrates how to specify a test server to tune a database with Database Engine Tuning Advisor.

In this example, the MyDatabaseName database is being tuned on MyServerName. The Transact-SQL script, MyWorkloadScript.sql, is used as the workload. This workload contains events that execute against MyDatabaseName. Most of the query optimizer calls to this database, which occur as part of the tuning process, are handled by the shell database that resides on MyTestServerName. The shell database is composed of metadata and statistics. This process results in the tuning overhead being offloaded to the test server. When Database Engine Tuning Advisor generates its tuning recommendation using this XML input file, it should consider indexes only (<FeatureSet>IDX</FeatureSet>), no partitioning, and need not keep any of the existing physical design structures in MyDatabaseName.

<?xml version=”1.0″ encoding=”utf-16″ ?> 

<DTAXML xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns=”https://schemas.microsoft.com/sqlserver/2004/07/dta”> 

  <DTAInput> 

    <Server> 

      <Name>MyServerName</Name> 

      <Database> 

        <Name>MyDatabaseName</Name> 

      </Database> 

    </Server> 

    <Workload> 

      <File>MyWorkloadScript.sql</File> 

    </Workload> 

    <TuningOptions> 

      <TestServer>MyTestServerName</TestServer> 

      <FeatureSet>IDX</FeatureSet> 

      <Partitioning>NONE</Partitioning> 

      <KeepExisting>NONE</KeepExisting> 

    </TuningOptions> 

  </DTAInput> 

</DTAXML> 

Performance Improvements using DTA

The performance of data warehousing and analytic workloads can greatly benefit from columnstore indexes, particularly for queries that need to scan large tables. Rowstore (B+-tree) indexes are most effective for queries that access relatively small amounts of data searching for a particular value or range of values. Since rowstore indexes can deliver rows in sorted order, they can also reduce the cost of sorting in query execution plans. Therefore, the choice of which combination of rowstore and columnstore indexes to build is dependent on your application’s workload.

The Database Engine Tuning Advisor (DTA), starting in SQL Server 2016, can recommend a suitable combination of rowstore and columnstore indexes by analyzing a given database workload.

To demonstrate the benefits of DTA’s recommendations on workload performance, we experimented with several real customer workloads. For each customer workload, we let DTA analyze individual queries as well as the full workload of queries. We consider three alternatives:

  1. Columnstore only: Build only columnstore indexes for all tables without using DTA.
  2. DTA (rowstore only): Run DTA with the option to recommend rowstore indexes only.
  3. DTA (rowstore + columnstore): Run DTA with the option to recommend both rowstore and columnstore indexes.

In each case, we then implemented the recommended indexes. We report the CPU Time (in milliseconds) averaged over multiple runs of the query or the workload. The figure below plots the CPU time in milliseconds for workloads across two different customer databases. Note that the y-axis (CPU Time) uses a logarithmic scale.

Need for mixed physical designs: The first set of bars corresponding to Customer 1 Query 1. DTA (rowstore + columnstore) recommends a set of four columnstore and six rowstore indexes which results in 2.5X – 4X lower CPU time compared to columnstore index only and DTA (rowstore only). This demonstrates the benefits of mixed physical designs consisting of rowstore and columnstore indexes even for a single query.

Effectiveness of rowstore index recommendations: The second and third set of bars (corresponding to Customer 1 Query 2 and Customer 2 Query 1) are cases where the queries have selective filter predicates that benefit from suitable rowstore indexes. For both these queries, DTA (rowstore only) and DTA (rowstore + columnstore) recommends rowstore indexes only. These examples also show that even when DTA is invoked with the option to recommend columnstore indexes, its cost-based approach ensures that it recommends a columnstore index only if the workload can actually benefit from it.

Effectiveness of columnstore index recommendations: The fourth set of bars corresponding to Customer 2 Query 2 represents a case where the query scans large tables which would benefit from columnstore indexes. DTA (rowstore only) generates a recommendation whose CPU Time is higher compared to when columnstore indexes are present. DTA (rowstore + columnstore) recommends suitable columnstore indexes, thus matching the query execution performance of the columnstore only option.

Effectiveness of recommendations for workload with multiple queries: The final set of bars corresponding to the full workload for Customer 2 exemplifies DTA’s ability to analyze multiple queries in the workload to recommend a suitable set of rowstore and columnstore indexes which can improve the overall workload’s execution cost. DTA (rowstore + columnstore) recommends four columnstore indexes and tens of rowstore indexes that result in over an order of magnitude improvement for the workload when compared to the option that builds only columnstore indexes; and about 4X-5X improvement when compared to DTA (rowstore only).

In summary, the above examples illustrate DTA’s ability to suitably leverage both rowstore and columnstore indexes supported in the SQL Server Database Engine, and recommend an appropriate combination of indexes that can significantly reduce CPU Time for the workload.

Start and Use the Database Engine Tuning Advisor

This topic describes how to start and use Database Engine Tuning Advisor in SQL Server 2019.

Initialize the Database Engine Tuning Advisor

On first use, a user who is member of the sysadmin fixed server role must initialize the Database Engine Tuning Advisor. This is because several system tables must be created in the msdb database to support tuning operations. Initialization also enables users that are members of the db_owner fixed database role to tune workloads on tables in databases that they own.

A user that has system administrator permissions must perform either of the following actions:

  • Use the Database Engine Tuning Advisor graphical user interface to connect to an instance of SQL Server 2019.
  • Use the dta utility to tune the first workload.

Start the Database Engine Tuning Advisor

You can start the Database Engine Tuning Advisor graphical user interface (GUI) in several different ways to support database tuning in a variety of scenarios. The different ways to start Database Engine Tuning Advisor include: from the Start menu, from the Tools menu in SQL Server Management Studio, from the Query Editor in SQL Server Management Studio, and from the Tools menu in SQL Server Profiler. When you first start Database Engine Tuning Advisor, the application displays a Connect to Server dialog box where you can specify the SQL Server instance to which you want to connect.

To start Database Engine Tuning Advisor from the Windows Start menu

  • On the Start menu, point to All Programs, point to Microsoft SQL Server, point to Performance Tools, and then click Database Engine Tuning Advisor.

To start the Database Engine Tuning Advisor in SQL Server Management Studio

  • On the SQL Server Management Studio Tools menu, click Database Engine Tuning Advisor.

To start the Database Engine Tuning Advisor from the SQL Server Management Studio Query Editor

  1. Open a Transact-SQL script file in SQL Server Management Studio.
  2. Select a query in the Transact-SQL script, or select the entire script, right-click the selection, and choose Analyze Query in Database Engine Tuning Advisor. The Database Engine Tuning Advisor GUI opens and imports the script as an XML file workload. You can specify a session name and tuning options to tune the selected Transact-SQL queries as your workload.

To start the Database Engine Tuning Advisor in SQL Server Profiler

  • On the SQL Server Profiler Tools menu, click Database Engine Tuning Advisor.

Create a Workload

A workload is a set of Transact-SQL statements that execute against a database or databases that you want to tune. Database Engine Tuning Advisor analyzes these workloads to recommend indexes or partitioning strategies that will improve your server’s query performance.

You can create a workload by using one of the following methods.

  • Use the Query Store as a workload. By doing this, you can avoid having to manually create a workload.
  • Use the plan cache as a workload. By doing this, you can avoid having to manually create a workload.
  • Use the Query Editor in SQL Server Management Studio or your favorite text editor to manually create Transact-SQL script workloads.
  • Use SQL Server Profiler to create trace file or trace table workloads
  • Workloads can also be embedded in an XML input file, where you can also specify a weight for each event.

To create Transact-SQL script workloads

  1. Launch the Query Editor in SQL Server Management Studio.
  2. Type your Transact-SQL script into the Query Editor. This script should contain a set of Transact-SQL statements that execute against the database or databases that you want to tune.
  3. Save the file with a .sql extension. The Database Engine Tuning Advisor GUI and the command-line dta utility can use this Transact-SQL script as a workload.

To create trace file and trace table workloads

  1. Launch SQL Server Profiler by using one of the following methods:
    1. On the Start menu, point to All ProgramsMicrosoft SQL ServerPerformance Tools, and then click SQL Server Profiler.
    1. In SQL Server Management Studio, click the Tools menu, and then click SQL Server Profiler.
  2. Create a trace file or table as described in the following procedures that uses the SQL Server Profiler Tuning template:
    1. Create a Trace (SQL Server Profiler)
    1. Save Trace Results to a File (SQL Server Profiler)

Database Engine Tuning Advisor assumes that the workload trace file is a rollover file.

  • Save Trace Results to a Table (SQL Server Profiler)

Make sure that tracing has stopped before using a trace table as a workload.

We recommend that you use the SQL Server Profiler Tuning template for capturing workloads for Database Engine Tuning Advisor.

If you want to use your own template, ensure that the following trace events are captured:

  • RPC:Completed
  • SQL:BatchCompleted
  • SP:StmtCompleted

You can also use the Starting versions of these trace events. For example, SQL:BatchStarting. However, the Completed versions of these trace events include the Duration column, which allows Database Engine Tuning Advisor to more effectively tune the workload. Database Engine Tuning Advisor does not tune other types of trace events.

Trace File or Trace Table Workloads That Contain the LoginName Data Column

Database Engine Tuning Advisor submits Showplan requests as part of the tuning process. When a trace table or file that contains the LoginName data column is consumed as a workload, Database Engine Tuning Advisor impersonates the user specified in LoginName. If this user has not been granted the SHOWPLAN permission, which enables the user to execute and produce Showplans for the statements contained in the trace, Database Engine Tuning Advisor will not tune those statements.

To avoid granting the SHOWPLAN permission to each user specified in the LoginName column of the trace
  1. Tune the trace file or table workload.
  2. Check the tuning log for statements that were not tuned due to inadequate permissions.
  3. Create a new workload by deleting the LoginName column from the events that were not tuned, and then save only the untuned events in a new trace file or table.
  4. Resubmit the new workload without the LoginName column to Database Engine Tuning Advisor.

Database Engine Tuning Advisor will tune the new workload because login information is not specified in the trace. If the LoginName does not exist for a statement, Database Engine Tuning Advisor tunes that statement by impersonating the user who started the tuning session (a member of either the sysadmin fixed server role or the db_owner fixed database role).

Tune a Database

To tune a database, you can use the Database Engine Tuning Advisor GUI or the dta utility.

Use the Database Engine Tuning Advisor Graphical User Interface

On the Database Engine Tuning Advisor GUI, you can tune a database by using the plan cache, workload files, or workload tables. You can use the Database Engine Tuning Advisor GUI to easily view the results of your current tuning session and results of previous tuning sessions.

To tune a database by using the plan cache

  1. Launch Database Engine Tuning Advisor, and log into an instance of SQL Server.
  2. On the General tab, type a name in Session name to create a new tuning session. You must configure the fields in the General tab before starting a tuning session. It is not necessary to modify the settings of the Tuning Options tab before starting a tuning session.
  3. Select Plan Cache as the workload option. Database Engine Tuning Advisor selects the top 1,000 events from the plan cache to use for analysis.
  4. Select the database or databases that you want to tune, and optionally from Selected Tables, choose one or more tables from each database. To include cache entries for all databases, from Tuning Options, click Advanced Options and then check Include plan cache events from all databases.
  5. Check Save tuning log to save a copy of the tuning log. Clear the check box if you do not want to save a copy of the tuning log.

You can view the tuning log after analysis by opening the session and selecting the Progress tab.

  • Click the Tuning Options tab and select from the options listed there.
  • Click Start Analysis.

If you want to stop the tuning session after it has started, choose one of the following options on the Actions menu:

  • Stop Analysis (With Recommendations) stops the tuning session and prompts you to decide whether you want Database Engine Tuning Advisor to generate recommendations based on the analysis done up to this point.
    • Stop Analysis stops the tuning session without generating any recommendations.

To tune a database using a workload file or table as input

  1. Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis.
  2. Create a workload.
  3. Launch Database Engine Tuning Advisor, and log into an instance of Microsoft SQL Server.
  4. On the General tab, type a name in Session name to create a new tuning session.
  5. Choose either a Workload File or Table and type either the path to the file, or the name of the table in the adjacent text box.

The format for specifying a table is database_name.schema_name.table_name 

To search for a workload file or table, click Browse. Database Engine Tuning Advisor assumes that workload files are rollover files.

When using a trace table as a workload, that table must exist on the same server that Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, move it to the server that Database Engine Tuning Advisor is tuning before using it as your workload.

  • Select the databases and tables against which you wish to run the workload that you selected in step 5. To select the tables, click the Selected Tables arrow.
  • Check Save tuning log to save a copy of the tuning log. Clear the check box if you do not want to save a copy of the tuning log.

You can view the tuning log after analysis by opening the session and selecting the Progress tab.

  • Click the Tuning Options tab and select from the options listed there.
  • Click the Start Analysis button in the toolbar.

If you want to stop the tuning session after it has started, choose one of the following options on the Actions menu:

  • Stop Analysis (With Recommendations) stops the tuning session and prompts you to decide whether you want Database Engine Tuning Advisor to generate recommendations based on the analysis done up to this point.
    • Stop Analysis stops the tuning session without generating any recommendations.

Use the dta Utility

The dta utility provides a command prompt executable file that you can use to tune databases. It enables you to use Database Engine Tuning Advisor functionality in batch files and scripts. The dta utility takes plan cache entries, trace files, trace tables, and Transact-SQL scripts as workloads. It also takes XML input that conforms to the Database Engine Tuning Advisor XML schema, which is available at this Microsoft Web site.

Consider the following before you begin tuning a workload with the dta utility:

  • When using a trace table as a workload, that table must exist on the same server that Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, then move it to the server that Database Engine Tuning Advisor is tuning.
  • Make sure that tracing has stopped before using a trace table as a workload for Database Engine Tuning Advisor. Database Engine Tuning Advisor does not support using a trace table to which trace events are still being written as a workload.
  • If a tuning session continues running longer than you had anticipated it would run, you can press CTRL+C to stop the tuning session and generate recommendations based on the analysis dta has completed up to this point. You will be prompted to decide whether you want to generate recommendations or not. Press CTRL+C again to stop the tuning session without generating recommendations.

To tune a database by using the plan cache

  1. Specify the -ip option. The top 1,000 plan cache events for the selected databases are analyzed.

From a command prompt, enter the following:

dta -E -D DatabaseName -ip -s SessionName  
  • To modify the number of events to use for analysis, specify the -n option. The following example increases the number of cache entries to 2,000.
dta -E -D DatabaseName -ip -n 2000-s SessionName1  
  • To analyze events for all databases in the instance, specify the -ipf option.
dta -E -D DatabaseName -ip -ipf -n 2000 -s SessionName2  
 

To tune a database by using a workload and dta utility default settings

  1. Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis.
  2. Create a workload.
  3. From a command prompt, enter the following:
dta -E -D DatabaseName -if WorkloadFile -s SessionName  

where -E specifies that your tuning session uses a trusted connection (instead of a login ID and password), -D specifies the name of the database you want to tune. By default, the utility connects to the default instance of SQL Server on the local computer. (Use the -S option to specify a remote database as shown in the following procedure, or to specify a named instance.) The -if option specifies the name and path to a workload file (which can be a Transact-SQL script or a trace file), and -s specifies a name for your tuning session.

The four options shown here (database name, workload, connection type, and session name) are mandatory.

To tune a remote database or a named instance for a specific duration

  1. Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis.
  2. Create a workload.
  3. From a command prompt, enter the following:
dta -S ServerName\Instance -D DatabaseName -it WorkloadTableName   
-U LoginID -P Password -s SessionName -A TuningTimeInMinutes  

where -S specifies a remote server name and instance (or a named instance on the local server) and -D specifies the name of the database you want to tune. The -it option specifies the name of the workload table, -U and -P specify the login ID and password to the remote database, -s specifies the tuning session name, and -A specifies the tuning session duration in minutes. By default, the dta utility uses an 8-hour tuning duration. If you would like Database Engine Tuning Advisor to tune a workload for an unlimited amount of time, specify 0 (zero) with the -A option.

To tune a database using an XML input file

  1. Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis.
  2. Create a workload.
  3. Create an XML input file.
  4. From a command prompt, enter the following:
dta -E -S ServerName\Instance -s SessionName -ix PathToXMLInputFile  

where -E specifies a trusted connection, -S specifies a remote server and instance, or a named instance on the local server, -s specifies a tuning session name, and -ix specifies the XML input file to use for the tuning session.

  • After the utility finishes tuning the workload, you can view the results of tuning sessions with the Database Engine Tuning Advisor GUI. As an alternative, you can also specify that the tuning recommendations be written to an XML file with the -ox option.

Create an XML Input File

If you are an experienced XML developer, you can create XML-formatted files that Database Engine Tuning Advisor can use to tune workloads. To create these XML files, use your favorite XML tools to edit a sample file or to generate an instance from the Database Engine Tuning Advisor XML schema.

The Database Engine Tuning Advisor XML schema is available in your Microsoft SQL Server installation in the following location:

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\dta\dtaschema.xsd

This URL takes you to a page where many SQL Server XML schemas are available. Scroll down the page until you reach the row for Database Engine Tuning Advisor.

To create an XML input file to tune workloads

  1. Create a workload. You can use a trace file or table by using the tuning template in SQL Server Profiler, or create a Transact-SQL script that reproduces a representative workload for SQL Server.
  2. Create an XML input file by one of the following methods:
    1. Copy and paste one of the XML Input File Samples (DTA) into your favorite XML editor. Change the values to specify the appropriate arguments for your SQL Server installation, and save the XML file.
    1. Using your favorite XML tool, generate an instance from the Database Engine Tuning Advisor XML schema.
  3. After creating the XML input file, use it as input to the dta command-line utility to tune the workload.

User Interface Descriptions

Tools Menu/Options Page

Use this dialog box to specify general configuration parameters for the Database Engine Tuning Advisor.

On startup
Specify what Database Engine Tuning Advisor should do when it is started: open without a database connection, show a New Connection dialog box, show a new session, or load the last loaded session.

Change font
Specify the display font used by Database Engine Tuning Advisor tables.

Number of items in most recently used lists
Specify the number of sessions or files to display under Recent Sessions or Recent Files in the File menu.

Remember my last tuning options
Retain tuning options between sessions. Selected by default. Clear this check box to always start with the Database Engine Tuning Advisor defaults.

Ask before permanently deleting sessions
Display a confirmation dialog box before deleting sessions.

Ask before stopping session analysis
Display a confirmation dialog box before stopping analysis of a workload.

General Tab Options

You must configure the fields in the General tab before starting a tuning session. You do not have to modify the settings of the Tuning Options tab before starting a tuning session.

Session name
Specify a name for the session. The session name associates a name with a tuning session. You can refer to this name to review the tuning session later.

File
Specify a .sql script or trace file for a workload. Specify the path and filename in the associated text box. Database Engine Tuning Advisor assumes that the workload trace file is a rollover file.

Table
Specify a trace table for a workload. Specify the fully qualified name of the trace table in the associated text box as follows:

database_name.owner_name.table_name  
  • Make sure that tracing has stopped before using a trace table as a workload.
  • The trace table must exist on the same server that Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, then move it to the server that Database Engine Tuning Advisor is tuning.

Plan Cache
Specify the plan cache as a workload. By doing this, you can avoid having to manually create a workload. Database Engine Tuning Advisor selects the top 1,000 events to use for analysis.

Xml
This does not appear unless you import a workload query from SQL Server Management Studio.

To import a workload query from SQL Server Management Studio:

  1. Type a query into Query Editor and highlight it.
  2. Right-click the highlighted query and click Analyze Query in Database Engine Tuning Advisor.

Browse for a workload [file or table]
When File or Table is selected as the workload source, use this browse button to select the target.

Preview the XML workload
View an XML-formatted workload that has been imported from SQL Server Management Studio.

Database for workload analysis
Specify the first database to which Database Engine Tuning Advisor connects when tuning a workload. After tuning begins, Database Engine Tuning Advisor connects to the databases specified by the USE DATABASE statements contained in the workload.

Select databases and tables to tune
Specify the databases and tables to be tuned. To specify all of the databases, select the check box in the Name column heading. To specify certain databases, select the check box next to the database name. By default, all of the tables for selected databases are automatically included in the tuning session. To exclude tables, click the arrow in the Selected Tables column, and then clear the check boxes next to the tables that you do not want to tune.

Selected Tables down arrow
Expand the tables list to allow selecting individual tables for tuning.

Save tuning log
Create a log and record errors during the session.

Tuning Tab Options

Use the Tuning Options tab to modify default settings of general tuning options. You do not have to modify the settings of the Tuning Options tab before starting a tuning session.

Limit tuning time
Limits the time for the current tuning session. Providing more time for turning improves the quality of the recommendations. To ensure the best recommendations, do not select this option.

Advanced Options
Use the Advanced Tuning Options dialog box to configure the maximum space, maximum key columns, and online index recommendations.

Define max. space for recommendations (MB)
Type the maximum amount of space to be used by physical design structures recommended by Database Engine Tuning Advisor.

If no value is entered here, Database Engine Tuning Advisor assumes the smaller of the following space limits:

  • Three times the current raw data size, which includes the total size of heaps and clustered indexes on tables in the database.
  • The free space on all attached disk drives plus the raw data size.

Include plan cache events from all databases
Specify that plan cache events from all databases are analyzed.

Max. columns per index
Specify the maximum number of columns to include in any index. The default is 1023.

All recommendations are offline
Generate the best recommendations possible, but do not recommend that any physical design structures be created online.

Generate online recommendations where possible
When creating Transact-SQL statements to implement the recommendations, choose methods that can be implemented with the server online, even if a faster offline method is available.

Generate only online recommendations
Only make recommendations that allow the server to stay online.

Stop at
Provide the date and time when Database Engine Tuning Advisor should stop.

Indexes and indexed views
Check this box to include recommendations for adding clustered indexes, nonclustered indexes, and indexed views.

Indexed views
Only include recommendations for adding indexed views. Clustered and nonclustered indexes will not be recommended.

Include filtered indexes
Include recommendations for adding filtered indexes. This option is available if you select one of these physical design structures: Indexes and indexed viewsIndexes, or Nonclustered indexes.

Indexes
Only include recommendations for adding clustered and nonclustered indexes. Indexed views will not be recommended.

Nonclustered indexes
Include recommendations for only nonclustered indexes. Clustered indexes and indexed views will not be recommended.

Evaluate utilization of existing PDS only
Evaluate the effectiveness of the current indexes but do not recommend additional indexes or indexed views.

No partitioning
Do not recommend partitioning.

Full partitioning
Include recommendations for partitioning.

Aligned partitioning
New recommended partitions will be aligned to make partitions easy to maintain.

Do not keep any existing PDS
Recommend dropping unnecessary existing indexes, views, and partitioning. If an existing physical design structure (PDS) is useful to the workload, Database Engine Tuning Advisor does not recommend dropping it.

Keep indexes only
Keep all existing indexes but recommend dropping unnecessary indexed views, and partitioning.

Keep all existing PDS
Keep all existing indexes, indexed views, and partitioning.

Keep clustered indexes only
Keep all existing clustered indexes but recommend dropping unnecessary indexed views, partitions, and nonclustered indexes.

Keep aligned partitioning
Keep partitioning structures that are currently aligned, but recommend dropping unnecessary indexed views, indexes, and non-aligned partitioning. Any additional partitioning recommended will align with the current partitioning scheme.

Progress Tab Options

The Progress tab of Database Engine Tuning Advisor appears after Database Engine Tuning Advisor begins analyzing a workload.

If you want to stop the tuning session after it has started, choose one of the following options on the Actions menu:

  • Stop Analysis (With Recommendations) stops the tuning session and prompts you to decide whether you want Database Engine Tuning Advisor to generate recommendations based on the analysis done up to this point.
  • Stop Analysis stops the tuning session without generating any recommendations.

Tuning Progress
Indicates the current status of the progress. Contains the number of actions performed, and the number of error, success, and warning messages received.

Details
Contains an icon indicating status.

Action
Displays the steps being performed.

Status
Displays the status of the action step.

Message
Contains any messages returned by the action steps.

Tuning Log
Contains information regarding this tuning session. To print this log, right-click the log, and then click Print.

View and Work with the Output from the Database Engine Tuning Advisor (DTA)

When the Database Engine Tuning Advisor tunes databases, it creates summaries, recommendations, reports, and tuning logs. You can use the tuning log output to troubleshoot Database Engine Tuning Advisor tuning sessions. You can use the summaries, recommendations, and reports to determine whether you want to implement tuning recommendations or continue tuning until you achieve the query performance improvements that you need for your Microsoft SQL Server installation.

View Tuning Output

The following procedures describe how to view tuning recommendations, summaries, reports, and tuning logs by using the Database Engine Tuning Advisor GUI.

You can also use the GUI to view tuning output that is generated by the dta command-line utility.

To view tuning recommendations with the Database Engine Tuning Advisor GUI

  1. Tune a database using the Database Engine Tuning Advisor GUI or the dta command-line utility. If you want to use an existing tuning session, skip this step and proceed to Step 2.
  2. Start the Database Engine Tuning Advisor GUI. If you want to view tuning recommendations for an existing tuning session, open it by double-clicking the session name in the Session Monitor window.

After the new tuning session has finished, or after the tool has loaded the existing session, the Recommendations page is displayed.

  • On the Recommendations page, click Partition Recommendations and Index Recommendations to view panes that display the tuning session results. If you did not specify partitioning when you set the tuning options for this session, the Partition Recommendations pane is empty.
  • In either the Partition Recommendations or the Index Recommendations pane, use the scroll bars to view all the information displayed in the grid.
  • Uncheck Show existing objects at the bottom of the Recommendations tabbed page. This causes the grid to display only those database objects that are referenced in the recommendation. Use the bottom scroll bar to view the right-most column in the recommendations grid, and click an item in the Definition column to view or copy the Transact-SQL script that creates that object in your database.
  • If you want to save all of the Transact-SQL scripts that create or drop all database objects in this recommendation into one script file, click Save Recommendations on the Actions menu.

To view the tuning summary and reports with the Database Engine Tuning Advisor GUI

  1. Tune a database using the Database Engine Tuning Advisor GUI or the dta command-line utility. If you want to use an existing tuning session, skip this step and proceed to step 2.
  2. Start the Database Engine Tuning Advisor GUI. If you want to view tuning summaries and reports for an existing tuning session, open it by double-clicking the session name in the Session Monitor.
  3. After the new tuning session has finished, or after the tool has loaded the existing session, click the Reports tab.
  4. The Tuning Summary pane contains information about the tuning session. The information provided by the Expected percentage improvement and the Space used by recommendation items can be especially useful to decide whether you want to implement the recommendation.
  5. In the Tuning Reports pane, click Select report to choose a tuning report to view.

To view tuning logs with the Database Engine Tuning Advisor GUI

  1. Tune a database using the Database Engine Tuning Advisor GUI or the dta command-line utility. Make sure that you check Save tuning log on the General tab when you tune the workload. If you want to use an existing tuning session, skip this step and proceed to Step 2.
  2. Start the Database Engine Tuning Advisor GUI. If you want to view tuning summaries and reports for an existing tuning session, open it by double-clicking the session name in the Session Monitor window.
  3. After the new tuning session has finished, or after the tool has loaded the existing session, click the Progress tab. The Tuning Log pane displays the contents of the log. The log contains information about workload events that Database Engine Tuning Advisor was unable to analyze.

If all events in the tuning session were analyzed by Database Engine Tuning Advisor a message indicating that the tuning log is empty for the session displays. If Save tuning log was not checked on the General tab when the tuning session was originally run, a message displays indicating that.

Implement Tuning Recommendations

You can implement the Database Engine Tuning Advisor recommendations manually or automatically as part of the tuning session. If you want to examine the tuning results first before implementing them, use the Database Engine Tuning Advisor GUI. You can then use SQL Server Management Studio to manually run the Transact-SQL scripts that Database Engine Tuning Advisor generates as a result of analyzing a workload to implement the recommendations. If you do not need to examine the results before implementing them, you can use the -a option with the dta command prompt utility. This causes the utility to automatically implement the tuning recommendations after it analyzes your workload. The following procedures explain how to use both Database Engine Tuning Advisor interfaces to implement tuning recommendations.

To manually implement tuning recommendations with the Database Engine Tuning Advisor GUI

  1. Tune a database using the Database Engine Tuning Advisor GUI or the dta command prompt utility. If you want to use an existing tuning session, skip this step and proceed to Step 2.
  2. Start the Database Engine Tuning Advisor GUI. If you want to implement tuning recommendations for an existing tuning session, open it by double-clicking the session name in Session Monitor.
  3. After the new tuning session has finished, or after the tool has loaded the existing session, click Apply Recommendations on the Actions menu.
  4. In the Apply Recommendations dialog box choose from Apply now or Schedule for later. If you choose Schedule for later, select the appropriate date and time.
  5. Click OK to apply the recommendations.

To automatically implement tuning recommendations using the dta command prompt utility

  1. Determine the database features (indexes, indexed views, partitioning) you want Database Engine Tuning Advisor to consider adding, removing, or retaining during analysis.

Keep the following considerations in mind before you begin tuning:

  • When using a trace table as a workload, that table must exist on the same server where Database Engine Tuning Advisor is tuning. If you create the trace table on a different server, move it to the server where Database Engine Tuning Advisor is tuning.
    • If a tuning session continues running longer than you had anticipated it would run, you can press CTRL+C to end the tuning session. Pressing CTRL+C under these circumstances forces dta to produce the best recommendation possible based on how much of the workload it has consumed, and does not waste the time that the tool has already used to tune the workload.
  • From a command prompt, enter the following:
dta -E -D DatabaseName -if WorkloadFile -s SessionName -a  

where -E specifies that your tuning session uses a trusted connection (instead of a login ID and password), -D specifies the name of the database you want to tune or a comma-delimited list of multiple databases that the workload uses, -if specifies the name and path to a workload file, -s specifies a name for your tuning session, and -a specifies that you want the dta command prompt utility to automatically apply the tuning recommendations after the workload is analyzed without prompting you.

  • Press ENTER.

Perform Exploratory Analysis

The user-specified configuration feature of Database Engine Tuning Advisor enables database administrators to perform exploratory analysis. Using this feature, database administrators specify a desired physical database design to Database Engine Tuning Advisor, and then they can evaluate the performance effects of that design without implementing it. User-specified configuration is supported by both the Database Engine Tuning Advisor graphical user interface (GUI) and the command-line utility. However, the command-line utility provides the greatest flexibility.

If you use the Database Engine Tuning Advisor GUI, you can evaluate the effects of implementing a subset of a Database Engine Tuning Advisor tuning recommendation, but you cannot add hypothetical physical design structures for Database Engine Tuning Advisor to evaluate.

The following procedures explain how to use the user-specified configuration feature with both tool interfaces.

Using Database Engine Tuning Advisor GUI to Evaluate Tuning Recommendations

The following procedure describes how to evaluate a recommendation that is generated by Database Engine Tuning Advisor, but the GUI does not enable you to specify new physical design structures for evaluation.

To evaluate tuning recommendations with the Database Engine Tuning Advisor GUI
  1. Use the Database Engine Tuning Advisor GUI to tune a database. If you want to evaluate an existing tuning session, double-click it in Session Monitor.
  2. On the Recommendations tab, clear the recommended physical design structures that you do not want to use.
  3. On the Actions menu, click Evaluate Recommendations. A new tuning session is created for you.
  4. Type the new Session name. To view the physical database design structure configuration that you are evaluating, choose Click here to see the configuration section, in the Description area at the bottom of the Database Engine Tuning Advisor application window.
  5. Click the Start Analysis button on the toolbar. When Database Engine Tuning Advisor is finished, you can view the results on the Recommendations tab.

Using Database Engine Tuning Advisor GUI to Export Tuning Session Results for “What-if” Tuning Analysis

The following procedure describes how to export Database Engine Tuning Advisor tuning session results to an XML file, which you can edit, and then tune it with the dta command-line utility. This enables you to perform tuning analysis on hypothetical new physical design structures without incurring the overhead of implementing them in your database before you find out whether they produce the performance improvements that you need. Using the Database Engine Tuning Advisor GUI to initially tune your database and then exporting the tuning results to an .xml file is a good way for users who are new to XML to use the flexibility of the Database Engine Tuning Advisor XML schema to perform “what-if” analysis.

To export tuning session results from the Database Engine Tuning Advisor GUI for “what-if” analysis with the dta command-line utility
  1. Use the Database Engine Tuning Advisor GUI to tune a database. If you want to evaluate an existing tuning session, double-click it in the Session Monitor.
  2. On the File menu, click Export Session Results and save it as an XML file.
  3. Open the XML file created in Step 2 in your favorite XML editor, text editor, or in SQL Server Management Studio. Scroll down to the Configuration element. Copy and paste the Configuration element section into an XML input file template after the TuningOptions element. Save this XML input file.
  4. In the new XML input file that you created in Step 3, specify any tuning options you want in the TuningOptions element, edit the Configuration element section (add or delete the physical design structures as appropriate for your analysis), save the file, and validate it against the Database Engine Tuning Advisor XML schema.
  5. Use the XML file that you created in Step 4 as input to the dta command line utility.

Using the User-specified Configuration Feature with the dta Command Line Utility

If you are an experienced XML developer, you can create a Database Engine Tuning Advisor XML input file in which you can specify a workload and a hypothetical configuration of physical database design structures, such as indexes, indexed views, or partitioning. Then you can use the dta command-line utility to analyze the effects this hypothetical configuration has on query performance for your database. The following procedure explains this process step by step:

To use the user-specified configuration feature with the dta command line utility
  1. Create a tuning workload.
  2. Copy and paste the XML Input File Sample with User-specified Configuration (DTA) into your XML editor or a text editor. Use this sample to create an XML input file for your tuning session.
  3. Edit the TuningOptions and the Configuration elements in the sample XML input file. In the TuningOptions element, specify what physical design structures you want Database Engine Tuning Advisor to consider during the tuning session. In the Configuration element, specify the physical design structures that match the hypothetical configuration of physical database design structures that you want Database Engine Tuning Advisor to analyze. For information about what attributes and child elements you can use with the TuningOptions and the Configuration parent elements.
  4. Save the input file with an .xml extension.
  5. Validate the XML input file you saved in Step 4 against the Database Engine Tuning Advisor XML schema. This schema is installed at the following location when you install Microsoft SQL Server:
C:\Program Files\Microsoft SQL Server\100\Tools\Binn\schemas\sqlserver\2004\07\dta\dtaschema.xsd  
  • After creating a workload and an XML input file, you are ready to submit the input file to the dta command-line utility for analysis. Make sure that you specify an XML output file name for the -ox utility argument. This creates an XML output file with a recommended configuration specified in the Configuration element. If you want to run Database Engine Tuning Advisor again to check another hypothetical configuration that is based on the output, you can copy and paste the Configuration element contents from the output file into a new or your original XML input file.

After tuning is finished, either use the Database Engine Tuning Advisor GUI to view the tuning reports, or open the XML output file to view the TuningSummary and the Configuration elements to view the Database Engine Tuning Advisor recommendations.

  • Repeat steps 6 and 7 until you create the hypothetical configuration that produces the query performance improvement that you need. Then you can implement the new configuration.

Review, Evaluate, and Clone Tuning Sessions

The Database Engine Tuning Advisor creates a new tuning session each time that you start analyzing the effects of a workload on your database or databases. You can use the Session Monitor in the Database Engine Tuning Advisor GUI to view or reload all tuning sessions that have run on a given instance of Microsoft SQL Server. Having all the existing tuning sessions available to review makes it easy to: clone sessions based on existing ones, edit existing tuning recommendations and then use Database Engine Tuning Advisor to evaluate the edited session, or perform tuning at regular intervals to monitor the physical design of your databases. For example, you may decide to tune database on a monthly schedule.

Before you can review any tuning sessions for an instance of SQL Server, you must create tuning sessions on the server instance by tuning workloads with the Database Engine Tuning Advisor.

Review Existing Tuning Sessions

Use the following steps to browse the existing tuning sessions on a given instance of SQL Server.

To review existing tuning sessions
  1. Start the Database Engine Tuning Advisor GUI.
  2. All existing tuning sessions are displayed in the upper half of the Session Monitor window. The number of sessions displayed depends on how many times you have tuned databases on this SQL Server instance. Use the scroll bars to view all tuning sessions.
  3. Click a tuning session name once and its details appear in the lower half of the Session Monitor window.
  4. Double-click a tuning session name and its information is loaded into Database Engine Tuning Advisor. After the session information loads, you can choose any of the tabs to view information about this tuning session.

Evaluate Existing Tuning Sessions As Hypothetical Configurations

Use the following steps to evaluate an existing tuning session. Evaluating an existing tuning session involves viewing and editing its recommendations, and then re-tuning. For example, you decide that you want to only create indexes on table1, so you delete the creation of indexed views and partitioning from an existing tuning recommendation. Then Database Engine Tuning Advisor creates a new tuning session and tunes the workload against your databases using the edited recommendations as a hypothetical configuration. This means that Database Engine Tuning Advisor tunes the workload against the databases as if the edited recommendations have been implemented, enabling you to perform limited “what-if” analysis. It is limited what-if analysis because you can only choose a subset of an existing recommendation when you use the Database Engine Tuning Advisor GUI. To perform full what-if analysis, specifying a completely new hypothetical configuration that is not a subset of any previous tuning session, you must use the Database Engine Tuning Advisor XML input file with the dta command-line utility.

To evaluate an existing tuning session
  1. After starting Database Engine Tuning Advisor, double-click a tuning session in the upper half of the Session Monitor, which loads the session information into Database Engine Tuning Advisor.
  2. Click the Progress tab to check the tuning log, which contains error information about any events in the workload that Database Engine Tuning Advisor could not tune. This information can help you evaluate the effectiveness of the workload.
  3. If you would like to review the tuning results for this session further, click the Reports tab. There you can view the tuning summary or choose a tuning report from the Select report list.
  4. Click the Recommendations tab to view the tuning recommendations.
  5. If there are any recommendations that you are unsure about implementing, uncheck them.
  6. On the Actions menu, click Evaluate Recommendations. Database Engine Tuning Advisor creates a new tuning session that uses the edited recommendation as a hypothetical configuration. To view the hypothetical configuration in XML, choose Click here to see the configuration section.
  7. On the General tab, type a Session name, and make sure the correct Workload is specified.
  8. On the Tuning Options tab, you can specify a tuning time, or any of the Advanced Options.
  9. Click the Start Analysis button on the toolbar. Database Engine Tuning Advisor starts tuning the databases using the hypothetical configuration. When Database Engine Tuning Advisor finishes, you can view the results of this session as you normally would for any session.

Clone Existing Tuning Sessions

You can create new tuning sessions based on existing sessions by choosing the cloning option in Database Engine Tuning Advisor. When you use the cloning option, you base a new tuning session on an existing session. Then you can change the tuning options for the new session as needed. When you evaluate an existing session as described in the previous procedure, Database Engine Tuning Advisor also creates a new tuning session, but you cannot change the tuning options.

To create new tuning sessions by cloning existing sessions
  1. After starting Database Engine Tuning Advisor, double-click a tuning session in the upper half of the Session Monitor, which loads the session information into Database Engine Tuning Advisor.
  2. On the Actions menu, click Clone Session.
  3. On the General tab, type a Session name, and make sure the correct Workload is specified.
  4. On the Tuning Options tab, you can specify a tuning time, the physical design structures Database Engine Tuning Advisor should consider creating, and what it should consider dropping in its recommendation.
  5. Click Advanced Options if you want to set a space limit for recommendations, a maximum number of columns per index, and whether you want Database Engine Tuning Advisor to generate recommendations that can be implemented while SQL Server is online.
  6. Click the Start Analysis button on the toolbar to analyze the effects of the workload like any other tuning session. When Database Engine Tuning Advisor finishes, you can view the results of this session as you normally would for any session.

Columnstore index recommendations in DTA

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. 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.

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).
  • Select other tuning options and click on Start Analysis button.
  • Once tuning is complete, view all recommendations including any columnstore indexes in the Recommendations pane (see figure below).
  • Click on 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).

Tuning Database Using Workload from Query Store

The Query Store feature in SQL Server automatically captures a history of queries, plans, and runtime statistics, and persists this information in the database. The Database Engine Tuning Advisor (DTA) supports a new option to use the Query Store to automatically select an appropriate workload for tuning. For many users, this can take away the need to explicitly collect a workload for tuning. This feature is only available if the database has the Query Store feature turned on.

How To Tune a Workload from Query Store in Database Engine Tuning Advisor GUI

From the DTA GUI, select the radio button Query Store in the General pane to enable this feature (see figure below).

How To Tune a Workload from Query Store in dta.exe command line Utility

From the command line (dta.exe), choose the -iq option to select the workload from Query Store.

There are two additional options available via the command line that helps tune the behavior of DTA when selecting the workload from Query Store. These options not available via the GUI:

  1. Number of workload events to tune: This option, specified using -n command line argument, allows the user to control how many events from the Query Store are tuned. By default, DTA uses a value of 1000 for this option. DTA always chooses the most expensive events by total duration.
  2. Time windows of events to tune: Since the Query Store may contain queries that have executed a long time ago, this option allows the user to specify a past time window (in hours) when a query must have executed for it to be considered by DTA for tuning. This option is specified using -I command line argument.

Difference between using Workload from Query Store and Plan Cache

The difference between the Query Store and Plan Cache options is that the former contains a longer history of queries that have executed against the database, persisted across server restarts. On the other hand, the Plan Cache only contains a subset of recently executed queries whose plans are cached in memory. When the server restarts, the entries in the Plan Cache are discarded.