Database Engine Tuning Advisor

Original: Database Engine Tuning Advisor

Database performance is largely dependent on whether the table has a suitable index. However, workloads and data change at any time, and existing indexes may not be entirely appropriate and may require a new index.

However, it is very difficult to decide on an index because it can have a negative effect on other queries or other operations because it promotes a query.

SQL Server provides the Database Engine Advisor (DTA) tool to help confirm an optimal set of indexes for a given workload. Without the need for an in-depth understanding of database and SQL Server structures. It also provides recommended adjustment options for a small number of problem queries.

Database Engine Tuning Advisor mechanism

After you open the engine and log on:

The first thing to do is create a new session. You can add a callout to the session for archiving.

Then, you must select a workload (a file or a table)

In the third step, select the Tuning options (Adjust options/optimize options) tab.

Select Limit Tuning time (limit adjustment events) to define when you want the DTA to run. Then define the date and time of the stop. Because the longer the DTA runs, the better the recommendation. You can select options for the tab. Increase the reliability of the recommendations.

You can also click "Advanced Options" to see more options.

Finally, click (Start Analysis) to start analyzing after the definition is complete.

All sessions are saved in the msdb of the corresponding instance.

DTA Report

Report Name	Report description
Column access (columns)	List the columns and tables referenced in the workload
Database access	List each database referenced in the workload and the percentage of each database workload statement
Event Frequency (Incident frequency)	List all events in a workload by frequency of occurrence
Index Detail (current) (Index details (currently))	Defining properties for indexes and workload references
Index Detail (Recommended) (Index details (recommended))	Is the same as the index details (current), but displays the index information recommended by the Database Engine Tuning Advisor.
Index usage (current) (Index usage (currently))	List the percentage of indexes and workload references
Index usage (Recommended) (Index usage (recommended))	Same as index using (current) report, but for recommended indexes
Statement cost (statement overhead)	Performance improvements for each statement that are listed in the recommended implementation scenario
Statement cost Range (statement overhead)	Divide the cost improvement into percentages to show how much benefit you can get from each set of modifications given
Statement Detail (statement details)	List the statements, overhead, and how to propose a reduced overhead in a workload
Statement-to-index relationship (statement-to-index relationship)	Lists the indexes for a single statement reference. There are current and recommended versions
Table access (tables accessed)	List the tables for workload references
View-to-table relationship (View-to-table relationship)	List of incidents materialized index reference table
Workload Analysis (Workload analytics)	Gives details of the workload, including the number of statements, the number of statements with reduced overhead, and the number of statements in which the cost remains unchanged.

Database Engine Tuning Advisor instance

The following example illustrates:

1, adjust a query:

SELECT Soh . DueDate , Soh . CustomerID

from Sales . SalesOrderHeader as Soh

WHERE DueDate between ' 1/1/2002 ' and ' 1/1/2003 ' and Status >4

Right → "Analyze queries in Database Engine advisor"

The Configuration tab is as follows:

Then run and run the result as follows:

As you can see, many of the recommended indexes are recommended for deletion. This step can actually be performed on all databases to ensure the validity of the database indexes.

You can evaluate and choose whether to accept the proposal, and generate the corresponding statement

2. Adjust a tracking workload

You can use PowerShell or SQLProfiler to create a trace file and analyze it.

Limitations of Database Engine Advisor

DTA is based on the input workload, and if it is not the actual load, then the recommendation may have a negative impact.

In a production environment, you should ensure that the trace contains a complete representation of the database workload. It usually takes one day to track the time. There are other limitations and considerations:

L Use sql:batchcompleted Event Trace input: The trace entered into DTA must contain the sql:batchcompleted event, or the wizard will not be able to determine the query in the workload.

The query distribution in the workload: in one workload, the query may execute multiple times with the same parameter values. Priority should be given to improving the most frequently used queries.

Index hint: index hints in SQL queries may prevent DTA from choosing a better execution plan.

Database Engine Tuning Advisor