Database Engine Tuning Advisor

Source: Internet
Author: User

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

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.