Reducir la carga de optimización del servidor de producción

El Asistente para la optimización de motor de base de datos se basa en el optimizador de consultas para analizar una carga de trabajo y para realizar recomendaciones de optimización. Realizar este análisis en el servidor de producción agrega a la carga del servidor y puede afectar al rendimiento del servidor durante la sesión de optimización. Puede reducir el impacto en la carga del servidor durante una sesión de optimización mediante un servidor de prueba además del servidor de producción.

Cómo usa el Asistente para la optimización de motor de base de datos un servidor de prueba

La manera tradicional de usar un servidor de prueba consiste en copiar todos los datos del servidor de producción en el servidor de prueba, ajustar el servidor de pruebas y, a continuación, implementar la recomendación en el servidor de producción. Este proceso elimina el impacto en el rendimiento en el servidor de producción, pero no obstante no es la solución óptima. Por ejemplo, copiar grandes cantidades de datos de la producción al servidor de prueba puede consumir cantidades considerables de tiempo y recursos. Además, el hardware de servidor de prueba rara vez es tan eficaz como el hardware que se implementa para los servidores de producción. El proceso de optimización se basa en el optimizador de consultas y las recomendaciones que genera se basan en parte en el hardware subyacente. Si el hardware del servidor de prueba y producción no son idénticos, se reduce la calidad de la recomendación del Asistente para la optimización de motor de base de datos.

Para evitar estos problemas, el Asistente para la optimización de motor de base de datos ajusta una base de datos en un servidor de producción descargando la mayor parte de la carga de optimización en un servidor de prueba. Para ello, usa la información de configuración del hardware del servidor de producción y sin copiar realmente los datos del servidor de producción al servidor de prueba. El Asistente para la optimización del motor de la base de datos no copia datos reales del servidor de producción al servidor de prueba. Solo copia los metadatos y las estadísticas necesarias.

En los pasos siguientes se describe el proceso para ajustar una base de datos de producción en un servidor de prueba:

1. Asegúrese de que el usuario que quiere usar el servidor de prueba existe en ambos servidores.

   Antes de empezar, asegúrese de que el usuario que quiere usar el servidor de prueba para ajustar una base de datos en el servidor de producción existe en ambos servidores. Esto requiere que cree el usuario y su inicio de sesión en el servidor de prueba. Si es miembro del rol fijo de servidor sysadmin en ambos equipos, no es necesario realizar este paso.

2. Ajuste la carga de trabajo en el servidor de prueba.

   Para ajustar una carga de trabajo en un servidor de prueba, debe usar un archivo de entrada XML con la utilidad de línea de comandos dta . En el archivo de entrada XML, especifique el nombre del servidor de pruebas con el subelemento TestServer además de especificar los valores de los otros subelementos en el elemento primario TuningOptions .

   Durante el proceso de optimización, el Asistente para la optimización de motor de base de datos crea una base de datos de shell en el servidor de prueba. Para crear esta base de datos de shell y optimizarla, el Asistente para la optimización de motor de base de datos realiza llamadas al servidor de producción para lo siguiente:

   1. El Asistente para la optimización de motor de base de datos importa metadatos de la base de datos de producción a la base de datos del shell del servidor de prueba. Estos metadatos incluyen tablas vacías, índices, vistas, procedimientos almacenados, desencadenadores, etc. Esto permite que las consultas de carga de trabajo se ejecuten en la base de datos del shell del servidor de prueba.

   2. El Asistente para la optimización de motor de base de datos importa las estadísticas del servidor de producción para que el optimizador de consultas pueda optimizar con precisión las consultas en el servidor de pruebas.

   3. El Asistente para la optimización de motor de base de datos importa parámetros de hardware que especifican el número de procesadores y memoria disponible del servidor de producción para proporcionar al optimizador de consultas la información que necesita para generar un plan de consulta.

3. Una vez que el Asistente para la optimización del motor de base de datos termine de ajustar la base de datos del shell del servidor de prueba, genera una recomendación de optimización.

4. Aplique la recomendación recibida al ajustar el servidor de prueba al servidor de producción.

En la ilustración siguiente se muestra el escenario de servidor de prueba y servidor de producción:

Ejemplo

En primer lugar, asegúrese de que el usuario que desea realizar la optimización existe en los servidores de prueba y producción.

Una vez copiada la información del usuario en el servidor de pruebas, puede definir la sesión de optimización del servidor de prueba en el archivo de entrada XML del Asistente para la optimización de motor de base de datos. En el siguiente archivo de entrada XML de ejemplo se muestra cómo especificar un servidor de prueba para ajustar una base de datos con el Asistente para la optimización de motor de base de datos.

En este ejemplo, la MyDatabaseName base de datos se está optimizando en MyServerName. El script Transact-SQL, MyWorkloadScript.sql, se usa como carga de trabajo. Esta carga de trabajo contiene eventos que se ejecutan en MyDatabaseName. La mayoría de las llamadas del optimizador de consultas a esta base de datos, que se producen como parte del proceso de optimización, se controlan mediante la base de datos de shell que reside en MyTestServerName. La base de datos de shell se compone de metadatos y estadísticas. Este proceso da como resultado que la sobrecarga de ajuste es trasladada al servidor de prueba. Cuando el Asistente para la optimización de motor de base de datos genera su recomendación de optimización mediante este archivo de entrada XML, debe tener en cuenta solo índices (<FeatureSet>IDX</FeatureSet>), sin particiones y no necesita mantener ninguna de las estructuras de diseño físico existentes en 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>

Véase también

Consideraciones para usar servidores de pruebaReferencia de archivo de entrada XML (Asistente para la optimización del motor de base de datos)