Use tools to optimize database design (Database Engine Tuning Advisor)

Data Engine Tuning Advisor is one of the database performance tuning tools provided by sqlserver. Let's take a look at how to use this tool to optimize database design.

Database Engine Tuning Advisor Tuning

• Recommend the best mix of indexes for databases by using the query optimizer to analyze queries in a workload.
  [Translation] using the query optimizer to analyze the queries under the actual workload, we recommend the best index combination.
• Recommend aligned or non-aligned partitions for databases referenced in a workload.
  [Translation] recommended aligned and Non-Aligned partitions for databases used at work
• Recommend indexed views for databases referenced in a workload.
  [Translation] attempts to recommend indexes for databases used in working conditions
• Analyze
  The effects of the proposed changes, including index usage, query
  Distribution among tables, and query performance in the workload.
  [Translation] The results of a change are evaluated during analysis, including index usage, query distribution between tables, and query results.
• Recommend ways to tune the database for a small set of problem queries.
  [Translation] optimizing and recommending problematic queries
• Allow you to customize the recommendation by specifying advanced options such as disk space constraints.
  [Translation] You can use advanced settings, such as the disk space limit, to influence the recommendation solution.
• Provide reports that summarize the effects of implementing the recommendations for a given workload.
  [Translation] provides results reports on the specific implementation of the recommended solution in the working status
• Consider
  Alternatives in which you supply possible design choices in the form
  Hypothetical invocations for Database Engine Tuning Advisor
  Evaluate.
  [Translation] alternative analysis solution, you can provide design solutions under different assumptions for Database Engine Tuning Advisor Analysis

 

INDEX OPTIMIZATION

Let's take a look at how to use the tuning tool to optimize the index:

Preparations

We use a missing index test table to check the recommended solution provided by the tool under simple query conditions.

There is an orders table in my database. Its fields include about rows of data. Here I did not create an index for the info field.

Create an SQL statement to simulate the actual query. In this example, we use a simple query statement.

Use [zhuyitestdatabase2] -- Remember to add the database with use
Select * from orders where info <> 'abc' -- we use a non-indexed field as the query condition, and the performance is definitely not high. Let's see what the DETA will do.

And saved as a. SQL file (in actual use, a query may be more complex)

Start Optimization

1) Run Database Engine Tuning Advisor and create a session

2) configure the session

On the configuration tab of the newly created session, click the General tab () to perform some common configurations. You need to configure the following items:

• Session name: A name can be set for the session. By default, the user is added with the creation time.
• Workload: the workload, which can be obtained from a file or table. In this example, the SQL file we just created is used. DETA analyzes the execution of this statement and provides optimization suggestions.
• Select database and table to tune: select the database and table to be optimized. In this example, we just saw the orders table
• Save tuning log: whether to save the optimization log

Click the tuningoption tab to configure some advanced options ()

• First
  First, we need to select which configurations need to be optimized. In "physical design structures to use in
  In the check box group of database, we can optimize the specified index, index view, focusing index, or non-focusing index. In this example, I select index. This option includes focusing and non-clustering.
  Focal index.
• Then there is the choice of partitions. This check box group is very useful in practical use. Through queries, you can find out which Data Access frequency is high and which is low, so as to provide a partition solution, in this example, we simply choose no partition.

3) Running Analysis

Click Start analysis under the Action menu, or run start analysis () in the shortcut bar ()

4) View analysis results

Wait for a while,ProgramThe analysis result is given.

We can see that the smart DETA finds that the Info Field Used as the condition in the query has no index. We recommend that you create an index and click here to see its creation script ()

Summary

Database Engine Tuning Advisor has more than these functions.ArticleI will talk about other usage in succession. Here we will provide some resource connections for you to learn.

Reference on technet

Http://technet.microsoft.com/en-us/library/ms166805 (SQL .90). aspx

Traditional Chinese Version

Http://technet.microsoft.com/zh-tw/library/ms173448 (SQL .90). aspx