Use SQL Server Database Tuning Advisor (DTA)

Document directory

• Webcast

Preface

I have learned the importance of the index from the importance of the index of the last returned data, but my experience may not be clear about the indexes that need to be created, in addition to the local method, Microsoft has a DTA tool (Oracle does not know it) to help you find the indexes that need to be created.

 

Features

• The query optimization tool is used to analyze the query results in the workflow, so as to build the optimal hybrid condition of the index.
• Based on the information obtained from the job evaluation team, the Team recommends the Information Division of the team or non-team members.
• The proposed work area refers to the index indexes of the materials used in the examination.
• The analysis results show more results, including index usage, query score between data tables, and query efficiency in work efficiency.
• It is suggested to look up the micro-indexing method for a small group of questions.
• Allows you to specify the disk space conditions, such as the upgrade and selection from the original suggestions.
• Provide a summary table to summarize the effect of the suggestions on the implementation of the prepared statement.
• Design alternative, which allows you to provide possible design options in the form of a hypothetical structure for the Database Engine Tuning Advisor to carry out the evaluation.

 

Security guard

The SQL Server Database Tuning Advisor is attached to the management tool that uses Terminal components. Generally, it is installed during security (you can also worry about the information, only the security tool ).

 
Audit 1 Security Audit SQL Server Database Tuning Advisor

Appendix:
Except SQL Express, Dat can be used in other versions (SQL 2000 is unknown ).

 

 

 

Operation

Synchronous DTA has several methods

1. Open the [SQL Server Management Studio] and check the startup dialog box (either by yourself or by starting the case ), right-click analyze query in Database Engine Tuning Advisor (I prefer this method)
    
   2. Check the dynamic DTA in the Navigation Pane.
2. On the [start] menu of windows, point to [program set], [Microsoft SQL Server 2005], and [performance tool] in sequence, and then click [Database Engine Tuning Advisor].

 

The main differences between these two methods are as follows: Method 1: Query and Method 2: Case or table analysis.

There are three or two ways to improve the efficiency of motion.
 

It's a little trick. Press the arrow next to the item list to list the item columns for each table.

Item 4 lists the data columns of each table.

 

Rows

After the condition is set (please refer to the attached file for future settings. Generally, you only need to select the dynamic table in the same way)

• On the [actions] toolbar, click begin [start analysis] or press F5.
• The [SS] Mark will be added when the row is closed, indicating the result of the current row.
• Two [recommendations] and [Reports] are added when the primary row is completed.

Suggestions for completion of five rows

 

Suggestions

When the primary row is completed, DTA analyzes the percentage of improvements and two suggestions.

• Patitiion
• Index

If you think there are no problems with the proposal, you can apply the proposal or inventory.

• On the [actions] toolbar, click [Apply recommendations] or [Save recommendations].

Contents of proposal 6

 

Orders table

Let's take a look at some information.

 

Remarks

DTA calls each analysis [session], and each [session] can only be committed once. If you want to merge the same [session], the method I used is to export and then import data, which is not very convenient. I have analyzed the information about the currently being developed case, an SQL statement is used to list 7 and 8 statements. One of the tables needs to create 5 and 6 indexes, which is a bit silly. I really don't know if I want to fully trust it.

 

Exam Upload files

Database Engine Tuning Advisor Certification
Http://technet.microsoft.com/zh-tw/library/ms173494.aspx

Database Engine Tuning Advisor tutorial

Http://technet.microsoft.com/zh-tw/library/ms166575.aspx

Zookeeper

Virtual Labs

SQL Server 2005 SQL query Tuning

Http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx? Eventid = 1032290418 & eventcategory = 3 & Culture = en-US & countrycode = us

Note: Because my personal computer has not been connected, I tried it on virtual labs.

Webcast

SQL Server 2005 Database Tuning Advisor)

Http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx? Culture = en-US & eventid = 1032275655 & countrycode = us