Share via

Considerations for Using Test Servers

  • Article

Using a test server to tune a database on a production server is an important benefit of Database Engine Tuning Advisor. Using this feature, you can offload tuning overhead to a test server without copying the actual data over to the test server from the production server.

Note

The test server tuning feature is not supported in the Database Engine Tuning Advisor graphical user interface (GUI).

To use this feature successfully, review the considerations listed in the following sections.

Setting Up the Test Server/Production Server Environment

  • The user who wants to use a test server to tune a database on a production server must exist on both servers, or this scenario will not work.

  • The extended stored procedure, xp_msver, must be enabled to use the test server/production server scenario. Database Engine Tuning Advisor uses this extended stored procedure to fetch the number of processors and the available memory of the production server to use while tuning the test server. If xp_msver is not enabled, Database Engine Tuning Advisor assumes the hardware characteristics of the computer where Database Engine Tuning Advisor is running. If the hardware characteristics of the computer where Database Engine Tuning Advisor is running are not available, one processor and 1024 megabytes (MBs) of memory are assumed. This extended stored procedure is turned on by default when you install SQL Server. For more information, see Surface Area Configuration and xp_msver (Transact-SQL).

  • Database Engine Tuning Advisor expects the editions of SQL Server to be the same on both the test server and the production server. If there are two different editions, the edition on the test server takes precedence. For example, if the test server is running SQL Server Standard, Database Engine Tuning Advisor will not include indexed views, partitioning, and online operations in its recommendations even if the production server is running SQL Server Enterprise.

About Test Server/Production Server Behavior

  • Database Engine Tuning Advisor takes into account hardware differences between the production and the test server when creating recommendations. The recommendation is the same as though the tuning was done on the production server alone.

  • Database Engine Tuning Advisor may impose some load on the production server for gathering metadata as well as creation of statistics necessary for tuning.

  • Database Engine Tuning Advisor does not copy actual data from the production server to the test server. It only copies the metadata of the databases and the necessary statistics.

  • All session information is stored in msdb on the production server. This allows you to exploit any available test server for tuning, and information about all sessions is available in one place (the production server).

  • After tuning, Database Engine Tuning Advisor should remove any metadata that it created on the test server during the tuning process. This includes the shell database. If you are performing a series of tuning sessions with the same production and test servers, you may want to retain this shell database to save time. In the XML input file, specify the RetainShellDB subelement with the other sub elements under the TuningOptions parent element. Using these options causes Database Engine Tuning Advisor to retain the shell database. For more information, see XML Input File Reference (Database Engine Tuning Advisor).

  • Shell databases may be left behind on the test server after a successful test server/production server tuning session even if you have not specified the RetainShellDB subelement. These unwanted shell databases may interfere with subsequent tuning sessions and should be dropped before performing another test server/production server tuning session. In addition, if a tuning session exits unexpectedly, the shell databases on the test server and the objects within those databases may be left behind on the test server. You should also delete these databases and objects before starting a new test server/production server tuning session.

  • The user must check the tuning log for tuning errors that result from differences between the production and test servers, and for errors that result from copying metadata from the production to the test server. For example, a user login may not exist on the test server. If a user login does not exist on the test server, those events in the workload that are issued by that user login may not be tunable. Use the Database Engine Tuning Advisor GUI to view the tuning log. For more information, see View and Work with the Output from the Database Engine Tuning Advisor

  • If Database Engine Tuning Advisor cannot tune many events because objects are missing in the shell database that Database Engine Tuning Advisor creates on the test server, the user must check the tuning log. Events that cannot be tuned are listed in the log. To successfully tune the database on the test server, the user must create the missing objects in the shell database, and then start a new tuning session.

  • If a database with the same name already exists on the test server, Database Engine Tuning Advisor does not copy metadata, but continues tuning and gathers statistics as necessary. This is useful if the user has already created a database on the test server and has copied the appropriate metadata before invoking Database Engine Tuning Advisor.

  • If the DATE_CORRELATION_OPTIMIZATION option is turned on for a database on the production server, metadata and the data associated with this option are not completely scripted while tuning the test server. When tuning is performed for a test server/production server scenario, the following issues may apply:

    • Users can have different query plans on the servers for queries that use the DATE_CORRELATION_OPTIMIZATION option.

    • Database Engine Tuning Advisor may suggest dropping indexed views that enforce the DATE_CORRELATION_OPTIMIZATION option in the recommendation script.

    Therefore, you may want to ignore any recommendations that Database Engine Tuning Advisor makes about the indexed views that hold correlation statistics because Database Engine Tuning Advisor knows their costs but not their benefits. Database Engine Tuning Advisor may not recommend selection of certain indexes such as clustered indexes on datetime columns, which could be beneficial when DATE_CORRELATION_OPTIMIZATION is enabled.

    To determine if a view is based on correlation statistics, select the is_date_correlation_view column of the sys.views catalog view.