How to Use Database Engine Optimization consultants to optimize Databases

Well, recently I have used this tool to optimize data, so I am too lazy to write it. I copied this old man'sArticle.

Address: http://www.cnblogs.com/hsj2010/archive/2010/07/15/1777823.html

Recently, the database server is a little slow. Let's see which aspects of the pressure is relatively high!

In fact, this lucky28 database does not affect the entire server.
But it may have a slight impact, and each small impact will have a great impact if it accumulates!

Here is a brief introduction: how to use the database engine optimization consultant to optimize the database

Simply optimize the database.

1. Start Microsoft SQL Server Management Studio (SQL management tool)

Ii. Tools-> SQL Server Profiler

Iii. SQL Server Profiler-> file-> New Trail-> RUN

A little longer. Click Stop and save the trail.

SQL Server Profiler-> file-> Save-> name.

For details, see:

How to Create a trail (SQL Server Profiler)

Http://technet.microsoft.com/zh-cn/library/ms175047.aspx

Iv. SQL Server Profiler-> Tools-database engine optimization Consultant

Select the file you saved for the workload.

Select one in the database and table to be optimized (do not select all, it will be stuck)

The options are all in Chinese. Configure them yourself.

Start analysis at the end. Wait. I waited for 10 minutes.

Suggestions

Take a look at the suggestions

The table tblucky28account has been queried quickly, but it is frequently used. Follow the suggestions to optimize it. It has indeed improved a lot.

One is statistics and the other is index.

The following information is found online:

1. statistics is the statistical information of several columns in a table. For example, if a table is the score of a certain test for all students, the score column type is int and the value range is an integer ranging from 0 to 100, then statistc is the number of people each score has. In a specific query, using an index may speed up or slow down. Therefore, SQL Server must make a prediction on the effect of using the index in advance. The prediction is based on statistics.
2. By default, when the table or index is changed, the statistics are automatically updated accordingly to keep the statistics updated. However, you can disable the automatic update function in the database option to speed up data table updates. However, statistics must be updated manually on a regular basis. Because the expired statistical information may lead to misjudgment on whether to use the index. No difference was found between SQL2000 and sql2005.
3. indexes must rely on the correct statistics to play a role. Your sql2005 optimization tool automatically generates creation statements for columns without statistic.

 

The index has many advantages and disadvantages. The following is a little bit of information about the index,

Http://www.cnblogs.com/Mattcoder/archive/2007/02/08/644824.html

 

5. View reports

You can view many reports.

 

Here we can see the Access Frequency of the account table tblucky28account. The preceding optimization is very correct.

Here is an index report. The two indexes I circled here are extremely large.
The largest pk_tbuserwagertaskhistory is historical data for future reference.
However, the above pk_tbgamepriceaccountlogs_xx has more than 32 m and needs to be optimized according to your actual situation.

 

 

 

 

How to Use Database Engine Optimization consultants to optimize Databases

Http://technet.microsoft.com/zh-cn/library/ms186354.aspx

How to Create a workload

Http://technet.microsoft.com/zh-cn/library/ms190957.aspx