Database Engine Tuning Advisor(DTA) in SQL Server

Database Engine Tuning Advisor (DTA) examines how queries are running in the database and provides us with suggestions. It has its own interface. You can open it via SSMS as follows.

You should select the instance you will analyze on the connection screen that appears.

You can open DTA via Windows Search as follows.

If the Database Engine Tuning Advisor is being opened for the first time, only a sysadmin can open it. For later use, db_owners can open DTA to monitor their database. This is because some system tables are created in the msdb database during initial startup. If you try to open DTA while SQL Server is in single user mode, you will receive an error.

You can analyze the following with DTA;

• SQL Statements
• Trace Tables
• Top 1000 queries in the plan cache (to analyze the database using dmws without any trace)

In this article, we will analyze a trace table where long running queries are recorded.

To analyze long running queries with profiler or extended events and save it as a table, I recommend that you read the articles below.

“How To Identify Long Running Queries Using SQL Server Profiler“,
“How To Identify Long Running Queries Using Extended Events”

After connecting DTA and selecting the corresponding instance from the connection screen, we select Table as follows.

Then click on the box with the binoculars icon on the right and select the related trace table that we have previously saved.

We select the related database from the “Database for workload analysis and Select databases and tables to tune” sections.

In the Tuning Options section, we select the options we need and click Start Analysis.

Below you can find explanation of the options in the Tuning Options section.

Limit tuning time

Limits the time Tune operation runs. Remove this limit if you want to get a healthy tune recommendation. If you still want to set a limit, you can specify the stop time of the DTA from “Stop at”.

Advanced Options

If you enter a value in MB after you select “Define max. Space for recommendations” below, you specify the maximum size of the suggestions. Do not place this limit if you want to get a healthy tune recommendation.

In the Online Index recommendations section;

If you select All recommendations are offline, it gives all index recommendations offline. But this option cause interruption in your database.

If you select “Generate online recommendations where possible”, it will give you index suggestions online if possible. Index recommendations cannot be performed online in every case. Thats why you can select this option for less interruption. But this option may cause interruption in your database.

If you do not want any interruptions in the database, you can select option 3. In this case, the script may receive an error. But the safest way is option three.

As a result, you can try option 3 at first. If you encounter with a problem, you can try option 2. But you must be ready for interruption before you start analysis.

Indexes and indexed views

You can select this option for suggestions to adding Clustered Index, Nonclustered Index, and Indexed View.

Indexed views

You can select this option only for suggestions to adding Indexed View.

Include filtered indexes

You can select this option for suggestions to adding Filtered Index. To select this option, you must choose on of the below options:

• Indexes and indexed views
• Indexes
• Nonclustered indexes

Indexes

You can select this option for suggestions to adding Clustered Index and Nonclustered Index.

Nonclustered indexes

You can select this option only for suggestions to adding Nonclustered Index.

Evaluate utilization of existing PDS only

You can select this option to evaluate whether existing indexes are useful. If you select this option, it does not recommend new index or indexed view.

No partitioning

You can choose this option if you do not want Partitioning suggestions. You can find details about the Partition concept in the article “How To Create Partition On SQL Server“.

Full partitioning

You can select this option if you want Partitioning suggestions. You can find details about the Partition concept in the article “How To Create Partition On SQL Server“.

Aligned partitioning

You should select this option if you want indexes to be aligned according to the partition when Partition is recommended. For information about the details of the Align process, please read to the article named “Can Not Switch The Partition On SQL Server“.

Do not keep any existing PDS

You should select this option for DTA’s delete recommendation if existing indexes, indexed views and partitions are unnecessary. It will not recommend delete if it is not unnecessary.

Keep indexes only

Unlike an item above, you should select this option if you want to keep indexes even if unnecessary. It will recommend deletion of indexed views and partitions.

Keep all existing PDS

If you do not want delete recommendation you should select this option.

Keep clustered indexes only

Unlike “keep indexes only”, you should select this option if you want to keep only clustered indexes, even if they are unnecessary.

Keep aligned partitioning

If you do not want delete recommedation of only aligned partitions, you should select this option. It will recommend other delete recommendations.

When the analysis is finished, it will list the suggestions from the Recommendations section as below. It didn’t suggest anything in my scenario.

With SQL Server 2017, DTA is now able to analyze Query Store data. You can find detailed information about the Query Store in the article “What is Query Store in SQL Server“.