Optimize the SQL Server engine using the optimization Consultant

In addition to relying on DBA's personal experience, database optimization also requires the help of some small tools. 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.

If you select either the database or table to be optimized, 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.

Original article title: How to Use Database Engine Optimization consultants to optimize Databases

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

1. Introduction to the advantages and disadvantages of Oracle Database Indexes
2. The actual Stored Procedure of Oracle using a cursor trigger
3. Oracle uses relevant triggers to implement auto-incremental ID
4. Oracle views and modifies its maximum number of cursors
5. Solution to the tablespace release problem after Oracle delete data