SQL Server Database Engine Tuning Advisor tuning Database

Now has been doing the project, the amount of data is relatively small, at the beginning of the time did not feel, because it is just beginning, the amount of data is very small, in the process of the use of speed is very fast, but with the constant growth of data volume, found that the program is more and more slow, even the problem of timeouts, so to the program Early project is relatively tight, not for the Big Data volume business analysis design, so the index and other related optimization did not do in place, through the late large data volume caused by errors using the following methods.

Here's a brief talk: How to tune a database using Database Engine Tuning Advisor

Simply optimize the database.

First, start Microsoft SQL Server Management Studio (SQL Management tool)

Second, tool->sql Server Profiler

C. New trace, SQL Server profiler-> file--run a little longer, stop and then save this trace SQL Server profiler-> file--save name.

Select the monitored statement, beginning with the%select%,%update%,%delete% statement.

View MORE:

How to create a trace (SQL Server Profiler)

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

Iv. SQL Server profiler-> Tools-Database Engine Tuning Advisor

Workload Select the file that you just saved.

Select one of the databases and tables you want to optimize (not all selected, will be stuck)

The options are in Chinese, configure them yourself.

Start the analysis at the last point.

There will be suggestions after completion

The query is actually very fast, but the function module is used very frequently, and it is optimized according to its recommendations. Really improved a lot.

One is statistics, one is index

Here are the online search:

1.STATISTICS is a statistic for several columns in a table, such as a table that is the score of an exam for a school student, the type of score column is int, and the value range is 0 to 100 integers, then STATISTC is how many people each score. In a particular query, using an index can speed up or slow down, so SQL Server makes a prediction of the effect of using the index in advance, based on statistics.
2. By default, the table or index changes, and the statistics are automatically updated accordingly to keep the statistics up-to-date. However, you can turn off this automatic Update feature in the database options to improve the speed of updating the data table. However, you need to manually update the statistics regularly. Because outdated statistics can result in a miscarriage of judgment about whether or not to use an index. There is no difference between the statistics of SQL2000 and SQL2005.
3.index relies on the right statistics to work. Your SQL2005 optimization tool automatically generates a CREATE statement for columns that lack statistic.

Indexes have many advantages and bad, the following is a little bit of information about the index,

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

V. View the report

There are many reports to view.

Here you can see how often the table is accessed, and the recommended optimizations above are very correct.

Here are the indexed reports. When the index is very large, according to their own actual situation to optimize

How to tune a database using Database Engine Tuning Advisor

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

How to create a workload

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

Reference Address: https://www.cnblogs.com/zhouchaoyi/articles/2088026.html

SQL Server Database Engine Tuning Advisor tuning Database