Database Server Load Analysis and Optimization

A large workload will produce a great deal of overhead for the optimized server. The overhead is caused by the database engine optimization consultant calling the query optimizer multiple times in the optimization process. This overhead can be eliminated if a test server is used in addition to the production server.

　Database Engine Optimization advisor how to use the test Server

The traditional method of using the test server is to copy all data from the production server to the test server, optimize the test server, and then implement suggestions on the production server. This process can eliminate the performance impact on production servers, but this is not the best solution. For example, copying a large amount of data from the production server to the test server may consume a lot of time and resources. In addition, the test server hardware is rarely as powerful as the hardware deployed on the production server. The optimization process depends on the query optimizer, and the suggestions it generates depend on the basic hardware. If the hardware of the test server is different from that of the production server, the quality recommended by the database engine optimization consultant will decrease.

To avoid these problems, the database engine optimization consultant transfers most of the optimization load to the test server to optimize the database on the production server. It performs this operation by using the hardware configuration information of the production server instead of copying data from the production server to the test server. The database engine optimization consultant does not copy the actual data from the production server to the test server. It only copies metadata and necessary statistics.

The following steps describe how to optimize the production database on the test Server:

1. Ensure that there are users on both servers who want to use the test server.

Before you begin, make sure that both servers have users who want to use test servers to optimize databases on production servers. This requires you to create a user and Its Logon account on the test server. If you are a member of the sysadmin fixed server on both computers, you do not need to perform this step.

2. Optimize the workload on the test server.

To optimize the workload on the test server, you must use the XML input file using the dta command line utility. In the XML input file, use the TestServer sub-element under the TuningOptions parent element to specify the name of the test server and the value for other sub-elements.

During the optimization process, the database engine optimization consultant will create a Shell database on the test server. To create and optimize the Shell database, the database engine optimization consultant must call the production server in the following situations:

A. The database engine optimization consultant imports metadata from the production database to the Shell database of the test server. This metadata includes empty tables, indexes, views, stored procedures, and triggers. This makes it possible to perform workload queries on the Shell database of the test server.

B. The database engine optimization consultant imports statistics from the production server so that the query optimizer can accurately optimize the query on the test server.

C. The database engine optimization consultant imports hardware parameters of the specified number of processors and available memory from the production server to provide the query optimizer with the information required to generate the query plan.

3. The database engine optimization consultant will generate optimization suggestions After optimizing the Shell database of the test server.

4. Apply the suggestions obtained by optimizing the test server to the production server.

Note: you cannot test the server optimization function on the graphic user interface (GUI) of the database engine optimization consultant.