Database Engine Tuning Advisor __ Database

Source: Internet
Author: User


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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.