Database Engine Tuning Advisor __ Database

Database performance depends to a large extent on whether there is an appropriate index on the table. But workloads and data change at any time, and existing indexes may not be entirely appropriate, and new indexes may be required.

However, it is very difficult to determine the index, because it can be promoted to a query, but it will have a negative effect on other queries or other operations.

SQL Server provides the Database Engine Advisor (DTA) tool to help validate a set of optimal indexes for a given workload. There is no need for a deeper understanding of the database and SQL Server structure. It also provides a recommended adjustment option for a small number of problem queries.

Database Engine Tuning Advisor mechanism

After you turn on the engine and log on:

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

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

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

Select Limit tuning time (limiting adjustment events) to define when you want DTA to run. Then define the date and time of the stop. Because the longer the DTA is running, the better it is recommended. You can select the options for the tab. Increase the reliability of the recommendations.

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

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

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

DTA Report

Report Name	Report description
Column access (column accesses)	Lists the columns and tables referenced in the workload
Database access (databases access)	List the percentages of each database and each database workload statement referenced in the workload
Event Frequency (Incident frequency)	List all events in the workload by occurrence frequency
Index Detail (current) (indexing details (currently))	Defining properties for indexes and workload references
Index Detail (recommended) (indexed details (recommended))	Is the same as the index details (current), but displays the index information suggested by the Database Engine Tuning Advisor.
Index Usage (current) (indexed)	List the percentages of indexes and workload references
Index Usage (recommended) (recommended)	Same as index use (current) report, but for suggested indexes
Statement cost (statement overhead)	Performance improvements for each statement listed under the recommended implementation
Statement cost Range (statement overhead range)	Divide the cost improvements into percentages to show how much benefit can be derived from each given set of changes
Statement Detail (statement details)	Lists statements in workloads, overhead, and how to recommend reduced overhead
Statement-to-index relationship (Relation of statement to index)	Lists the indexes referenced by a single statement. There are current and suggested versions
Table access (table accessible)	Lists the tables that the workload references
view-to-table relationship (Relationship of view to table)	List of accidents in a row materialized index reference
Workload analyses (workload analysis)	Give details of the workload, including the number of statements, the number of statements that have been lowered, and the number of statements that have remained unchanged.

Database Engine Tuning Advisor instance

Here is an example to illustrate:

1, adjust a query:

SELECT Soh. Duedate,soh. CustomerID

From Sales.salesorderheaderas Soh

WHERE duedatebetween ' 1/1/2002 ' and ' 1/1/2003 ' and status>4

Right → "Parse query in Database engine advisor"

The Configuration tab is as follows:

Then run and run the results 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 index.

You can evaluate and choose whether to accept the suggestions and generate the corresponding statements

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 you keep track of a complete performance that contains your database workload. It usually takes a day of tracking time. There are other limitations and considerations:

L Use sql:batchcompleted Event Trace input: The trace entered to DTA must contain sql:batchcompleted events, otherwise the wizard cannot determine the query in the workload.

L Workload Query Distribution: In a workload, queries may be executed multiple times with the same parameter values. Priority should be given to improving the most frequently used queries.

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