Database Engine Tuning Advisor

SQL Server provides a tool called Database Engine Tuning Advisor. This tool helps to confirm an optimal set of indexes for a given workload without requiring an in-depth understanding of the database structure or the internal structure of SQL Server. It can also suggest tuning options for a small subset of problematic queries. Besides the benefits the tool also has disadvantages. should be used correctly.

I. Database Engine Tuning Advisor mechanism

You can directly select SQL Server 2008 = "Performance tool =" Database Engine Tuning Advisor to use it.

Run a query from management Studio (select the query you want, select query = To parse the query in Database Engine Tuning Advisor), or run it from management studio= select = Database Engine Tuning Advisor. Once the tool is opened and connected to a server, you will see the following window:

　　

The Data Engine Tuning Advisor is already connected to a server. From there, start outlining the workload and the objects you want to adjust. Creating a session name is necessary to add a callout for the session to the archive. Then, you must select a workload (a file or a table) and browse to the appropriate location. Workloads are defined according to how the Database Tuning Advisor is started. If you start from the Query window, you will see a "Query" wireless button, and the "File" and "table" buttons will be disabled. You must also define the database for the workload analysis settings and eventually select a database that needs to be adjusted.

　　

Also, through the "Optimization options" tab, as shown in:

　　

Select Limit tuning time to define how long you want the database engine to adjust the advisor run, and then define the date and time that the adjustment stopped. The longer the Database Engine Tuning Advisor runs, the better recommendations are available. You can choose to consider the physical design structure type created by the Database Engine Tuning Advisor, and you can set the partitioning policy so that the Tuning Advisor can know whether tables and index partitions should be considered as part of the analysis. Partitioning is not necessarily an ideal result if the data and structure are not guaranteed. Finally, you can define the physical design structure that you want to maintain in the database.

Changing these options causes the Database Engine Tuning Advisor to widen or narrow the selection to improve performance.

There are more options for clicking the Advanced Options button.

　　

In this dialog box, you can limit the recommended space and the number of columns contained in an index. Finally, you can determine whether the changes in the new index or index are completed as an online or offline index operation.

Once you have defined all of these settings appropriately, you can click the Start Analysis button to start the Database Tuning Advisor. The session that is created is saved in the msdb database for all server instances that are targeted, and it shows the details of what is being analyzed and the progress made.

　　

Database Engine Tuning Advisor report:

Report Name	Report description
Column access (columns)	List the columns and tables referenced in the workload
Database access	List the percentage of each database and each database workload referenced in the workload
Event Frequency (Incident frequency)	List all events in a workload by frequency of occurrence
Index Detail details (current)	Defining properties for indexes and workload references
Index Detail (Recommended) indexing details (recommended)	Same as index details, but displays information about indexes recommended by the Database Engine Tuning Advisor
Index usage (current) indexes used (currently)	List the percentage of indexes and workload references
Index usage (Recommended) indexes use (recommended)	Same as using the index report, but from the recommended index
Statement cost (statement overhead)	List performance improvements for each statement in the case of recommended implementations
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 in the workload, the cost, and the cost to reduce if the recommendations are implemented
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 the tables referenced by the physical index
Workload Analysis (Workload analytics)	Gives the details of the workload, including the number of statements, the number of statements with reduced overhead, and the statement that the overhead remains constant.

Second, Database Engine Tuning Advisor instance

　　1, adjust a query

Select a query statement, right-click = "Database Engine Tuning Advisor" to parse the query.

The settings are based on the following:

　　

Set Time:

　　

Although the default setting is 1 hours, this recommendation is completed in 1 minutes.

As a final recommendation, click the rightmost definition and the SQL Server Tuning Advisor recommends the following optimizations:

　　

SQL Server Tuning Advisor recommends that I establish a nonclustered index on the name column. This is a perfectly correct recommendation for this query.

Sometimes, for an optimization of a single query, SQL Server recommends unloading some indexes that are not used. However, optimization should be optimized for the entire database.

Therefore, in the optimization project, the Data Engine Tuning Advisor should be set to not unload any existing structures, and this optimization option will "keep the physical design structure in the database" setting "never keep any current PDS" into the existing PDS. In this way, the Optimization advisor will not recommend uninstalling the existing object.

　　

In addition, after being advised, you can work directly on the operation, evaluate recommendations, save suggestions, and apply recommendations.

　　

Click Apply suggestions:

　　

After you click OK:

Database Engine Tuning Advisor will automatically execute the recommendations to SQL Server:

　　

2. Adjust a tracking workload

Adjusting a tracking workload first you have to have a trace file or a trace table:

　　

Unfortunately, the generated recommendations are actually empty:

　　

This does not mean that there are no other possible improvements, just means that the Tuning Advisor is not always able to discover all possible improvements.

III. Limitations of Database Engine Tuning Advisor

Database Engine Tuning Advisor recommendations are based on input workloads. If the input workload is not the real performance of the actual workload, then the recommended index may sometimes have a negative impact on some of the queries lost in this workload.

For a production server, you should ensure that the SQL Trace contains a complete representation of the database workload. For most database applications, a trace that captures a full day typically contains queries that are executed on most databases. Some other database Engine tuning consultants need to consider the following issues/limitations:

• Use sql:batchcompleted Event Trace input: The SQL Trace entered into the Database Engine Tuning Advisor must contain the sql:batchcompleted event; otherwise, the wizard will not be able to determine the query in the workload;
• Query distribution in workloads: In one workload, the query may execute multiple times with the same parameter values. The most frequently used queries, even small performance improvements, make a greater contribution to the performance of the entire workload compared to a larger improvement in the query that executes only once.
• Index hint: An index hint in a SQL query might prevent the Database Engine Tuning Advisor from choosing a better execution plan. The wizard uses the index hints in all SQL queries as part of the recommendation. Because these indexes may not be optimal for the table, delete the index hints from all queries before committing the workload to the wizard;

Database Engine Tuning Advisor