减轻生产服务器优化负荷Reduce the Production Server Tuning Load

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database

数据库引擎Database Engine 优化顾问依赖于查询优化器分析工作负荷并提供优化建议。Tuning Advisor relies on the query optimizer to analyze a workload and to make tuning recommendations. 在生产服务器上执行此分析会增加服务器负荷,并且可能会在优化会话过程中影响服务器的性能。Performing this analysis on the production server adds to the server load and can hurt server performance during the tuning 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 Database Engine Tuning Advisor Uses a Test Server

使用测试服务器的传统方法是将所有数据从生产服务器复制到测试服务器,优化测试服务器,然后在生产服务器上实现建议。The traditional way to use a test server is to copy all of the data 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 on your production server, but nevertheless is 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 is seldom as powerful as the hardware that is deployed for production servers. 优化进程依赖于查询优化器,而它生成的建议部分依赖于基础硬件。The tuning process relies on the query optimizer, and the recommendations it generates are based in part on the underlying hardware. 如果测试服务器的硬件和生产服务器的硬件不相同, 数据库引擎Database Engine 优化顾问建议的质量就会降低。If the test and production server hardware are not identical, the 数据库引擎Database Engine Tuning Advisor recommendation quality is diminished.

为避免出现此类问题, 数据库引擎Database Engine 优化顾问通过将大部分优化负荷转移到测试服务器来优化生产服务器上的数据库。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:

  1. 确保两台服务器上都存在要使用测试服务器的用户。Make sure that the user who wants to use the test server exists on both servers.

    开始之前,请确保两台服务器上都存在要使用测试服务器来优化生产服务器上的数据库的用户。Before you start, make sure that the user who wants to use the test server to tune a database on the production server exists on both servers. 这就需要您在测试服务器上创建用户及其登录帐户。This requires that you create the user and his or her login on the test server. 如果您在两台计算机上都是 sysadmin 固定服务器角色成员,将不需要执行此步骤。If you are a member of the sysadmin fixed server role on both computers, this step is not necessary.

  2. 优化测试服务器上的工作负荷。Tune the workload on the test server.

    若要优化测试服务器上的工作负荷,必须通过 dta 命令行实用工具使用 XML 输入文件。To tune a workload on a test server, you must use an XML input file with the dta command-line utility. 在 XML 输入文件中,在 TuningOptions 父元素下使用 TestServer 子元素指定测试服务器的名称,并为其他子元素指定值。In the XML input file, specify the name of your test server with the TestServer subelement in addition to specifying the values for the other subelements under the TuningOptions parent element.

    在优化进程中,数据库引擎优化顾问将在测试服务器上创建 Shell 数据库。During the tuning process, Database Engine Tuning Advisor creates a shell database on the test server. 若要创建此 Shell 数据库并对其进行优化,数据库引擎优化顾问需要在下列情况下调用生产服务器:To create this shell database and tune it, Database Engine Tuning Advisor makes calls to the production server for the following:

    1. 数据库引擎Database Engine 优化顾问将元数据从生产数据库导入到测试服务器 Shell 数据库。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. 这使得对测试服务器 Shell 数据库执行工作负荷查询成为可能。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 optimize queries on the test server.

    3. 数据库引擎Database Engine 优化顾问从生产服务器导入指定处理器数和可用内存量的硬件参数,为查询优化器提供生成查询计划所需的信息。Tuning Advisor imports hardware parameters specifying the number of processors and available memory from the production server to provide the query optimizer with the information it needs to generate a query plan.

  3. 数据库引擎Database Engine 优化顾问完成优化测试服务器 Shell 数据库后,将生成优化建议。After 数据库引擎Database Engine Tuning Advisor finishes tuning the test server shell database, it generates a tuning recommendation.

  4. 将通过优化测试服务器得到的建议应用于生产服务器。Apply the recommendation received from tuning the test server to the production server.

下图显示了测试服务器和生产服务器方案:The following illustration shows the test server and production server scenario:

数据库引擎优化顾问测试服务器用法Database Engine Tuning Advisor test server usage

备注

数据库引擎Database Engine 优化顾问图形用户界面 (GUI) 不支持测试服务器优化功能。The test server tuning feature is not supported in the 数据库引擎Database Engine Tuning Advisor graphical user interface (GUI).

示例Example

首先,请确保测试服务器和生产服务器上都存在要执行优化的用户。First, make sure that the user who wants to perform the tuning exists on both the test and production servers.

将用户信息复制到测试服务器后,您即可在 数据库引擎Database Engine 优化顾问 XML 输入文件中定义测试服务器优化会话。After the user information is copied over to your test server, you can define your test server tuning session in the 数据库引擎Database Engine Tuning Advisor XML input file. 下面的 XML 输入文件示例演示如何使用 数据库引擎Database Engine 优化顾问来指定测试服务器优化数据库。The following example XML input file illustrates how to specify a test server to tune a database with 数据库引擎Database Engine Tuning Advisor.

在此示例中, MyDatabaseName 数据库在 MyServerName上进行优化。In this example, the MyDatabaseName database is being tuned on MyServerName. Transact-SQLTransact-SQL 脚本(即 MyWorkloadScript.sql)用作工作负荷。The Transact-SQLTransact-SQL script, MyWorkloadScript.sql, is used as the workload. 此工作负荷包含对 MyDatabaseName执行的事件。This workload contains events that execute against MyDatabaseName. 查询优化器对此数据库的大部分调用操作(作为优化进程的一部分发生)是由驻留在 MyTestServerName上的 Shell 数据库实现的。Most of the query optimizer calls to this database, which occur as part of the tuning process, are handled by the shell database that resides on MyTestServerName. Shell 数据库由元数据和统计信息构成。The shell database is composed of metadata and statistics. 此进程会将优化开销转移到测试服务器。This process results in the tuning overhead being offloaded to the test server. 数据库引擎Database Engine 优化顾问使用此 XML 输入文件生成其优化建议时,它应只考虑索引 (<FeatureSet>IDX</FeatureSet>) 而不考虑分区,并且不需要在 MyDatabaseName中保留任何现有的物理设计结构。When 数据库引擎Database Engine Tuning Advisor generates its tuning recommendation using this XML input file, it should consider indexes only (<FeatureSet>IDX</FeatureSet>), no partitioning, and need not keep any of the existing physical design structures in MyDatabaseName.

<?xml version="1.0" encoding="utf-16" ?>  
<DTAXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="https://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>  

另请参阅See Also

使用测试服务器的注意事项 Considerations for Using Test Servers
XML 输入文件引用(数据库引擎优化顾问)XML Input File Reference (Database Engine Tuning Advisor)