Dta3:reduce the Production Server Tuning Load

Tuning recommendations is an optimization recommendation provided by DTA.

Reference msdn:reduce the Production Server Tuning Load

DTA relies on the query optimizer to analyze a workload and to make tuning recommendations. Performing the production server adds to the server load and can hurt server performance during the Tunin G session. You can reduce the impact to the server load during a tuning session by using a test server in addition to the production Server.

How DTA Uses a Test Server

The traditional-on-use-a test server is-copy all-from-your production server to your test server, tune The test server, and then implement the recommendation on your production server. This process eliminates the performance impact to your production server, but nevertheless was not the optimal solution.

For example, copying large amounts of data from the production to the test server can consume substantial amounts of time and resources. In addition, test server hardware are seldom as powerful as the hardware that's deployed for production servers. The tuning process relies on the query optimizer, and the recommendations it generates is based in part on the underlying Hardware. If the test and production server hardware is not a identical, the Database Engine Tuning Advisor recommendation Quality is Diminished.

To avoid these problems, database Engine Tuning Advisor tunes a database on a production server by offloading most of the Tuning load onto a test server. It does this by using the production server hardware configuration information and without actually copying the data from The production server to the test server. Database Engine Tuning Advisor does not copy actual data from the production server to the test server. It only copies the metadata and necessary statistics.

The following steps outline the process for tuning a production database on a test server:

2. Make sure, the user wants to use the test server exists on both servers.

   Before you start, make sure, the user who wants to use the test server to tune a database on the production server Exi STS on both servers. This requires, the user and his or her login to the test server. If you were a member of the sysadmin fixed server role on both computers, this step was not necessary.

3. Tune The workload on the test server.

   To tune a workload in a test server, you must use an XML input file with the dta command-line utility. In the XML input file, specify the name of your test server with the testserver subelement In addition to specify ing the values for the other subelements under the tuningoptions parent element.

   During the tuning process, Database Engine tuning Advisor creates a shell Database on the test server. To create this shell database and tune it, database Engine Tuning Advisor makes calls to the production server for the FOL Lowing:

   1. Database Engine Tuning Advisor imports metadata from the production database to the test server shell database. This metadata includes empty tables, indexes, views, stored procedures, triggers, and so on. This makes it possible for the workload queries to execute against the test server shell database.

   2. Database Engine Tuning Advisor Imports statistics from the production server so the query optimizer can accurately Optimiz e queries on the test server.

   3. Database Engine Tuning Advisor Imports hardware parameters specifying the number of processors and available memory from T He production server to provide the query optimizer and the information it needs to generate a query plan.

5. After Database Engine Tuning Advisor finishes Tuning the test server Shell Database, it generates a Tuning recommendation.

7. Apply the recommendation received from tuning the test server to the production server.

The following illustration shows the test server and production server scenario:

Note:the test Server Tuning feature is not supported in the Database Engine tuning Advisor Graphical user interface (GUI).

Example

First, make sure then the user wants to perform the tuning exists on both the test and production servers.

After the user information are copied over to your test server, you can define your test server tuning session in the Datab ASE Engine Tuning Advisor XML input file. The following example XML input file illustrates how to specify a test server to tune a database with database Engine Tuni Ng Advisor.

In this example, the MYDATABASENAME database was being tuned on myservername. The Transact-SQL script, myworkloadscript.sql, is used as the workload. This workload contains events, execute against mydatabasename. Most of the query optimizer calls to this database, which occur as part of the tuning process, is handled by the Shell da Tabase that resides on mytestservername. The shell database is composed of metadata and statistics. This process results in the tuning overhead being offloaded to the test server. When the Database Engine Tuning Advisor generates its Tuning recommendation using the This XML input file, it should consider Inde Xes only (<featureset>idx</featureset>), no partitioning, and need not keep any of the Exis Ting physical design structures in mydatabasename.

<?XML version= "1.0" encoding= "utf-16"?><DtaxmlXmlns:xsi= "Http://www.w3.org/2001/XMLSchema-instance"xmlns= "Http://schemas.microsoft.com/sqlserver/2004/07/dta">  <DTAInput>    <Server>      <Name>MyServerName</Name>      <Database>        <Name>MyDatabaseName</Name>      </Database>    </Server>    <Workload>      <File>Myworkloadscript.sql</File>    </Workload>    <tuningoptions>      <TestServer>Mytestservername</TestServer>      <FeatureSet>IDX</FeatureSet>      <Partitioning>NONE</Partitioning>      <keepexisting>NONE</keepexisting>    </tuningoptions>  </DTAInput></Dtaxml>

Recommended reading: Considerations for Using Test Servers

Dta3:reduce the Production Server Tuning Load