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

Se aplica a: SQL Server Azure SQL Database

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 hacer recomendaciones de optimización. La realización de este análisis en el servidor de producción le agrega carga y puede perjudicar su rendimiento durante la sesión de optimización. Puede reducir el efecto en la carga del servidor durante una sesión de optimización utilizando un servidor de pruebas además del servidor de producción.

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

El modo tradicional de utilizar un servidor de prueba consiste en copiar todos los datos del servidor de producción al servidor de prueba, optimizar el servidor de prueba y luego implementar la recomendación en el servidor de producción. Este proceso elimina el impacto negativo de rendimiento que sufriría el servidor de producción, pero no es la solución idónea. Por ejemplo, cuando sea necesario copiar grandes volúmenes de datos del servidor de producción al de prueba, el consumo de tiempo y recursos se dispara. Además, el hardware del servidor de prueba no suele ser tan eficaz como el hardware implementado en los servidores de producción. El proceso de optimización se basa en el optimizador de consultas y las recomendaciones que éste genera se basan en parte del hardware subyacente. Si el hardware del servidor de prueba y el de producción no son idénticos, la calidad de la recomendación del Asistente para la optimización de motor de base de datos disminuye.

Para evitar estos problemas, Asistente para la optimización de motor de base de datos optimiza 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. Esto lo realiza gracias a la información de configuración del hardware del servidor de producción y sin tener que copiar los datos de dicho servidor al de prueba. El Asistente para la optimización de motor de base de datos no copia datos reales del servidor de producción al servidor de prueba. Solo copia los metadatos y las estadísticas que necesita.

Los siguientes pasos describen el proceso de optimización de una base de datos en un servidor de prueba:

1. Asegúrese de que el usuario que desea utilizar el servidor de prueba existe en ambos servidores.

   Antes de empezar, compruebe que el usuario que quiere utilizar el servidor de prueba para optimizar una base de datos del servidor de producción existe en ambos servidores. Para ello deberá crear el usuario y su inicio de sesión correspondiente en el servidor de prueba. Si es miembro del rol fijo de servidor sysadmin de ambos equipos no es necesario que lleve a cabo este paso.

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

   Para optimizar la carga de trabajo en un servidor de prueba se necesita un archivo de entrada XML que debe ejecutarse con la utilidad de la línea de comandos dta . En el archivo de entrada XML, especifique el nombre del servidor de prueba mediante el subelemento TestServer además de especificar los valores del resto de los subelementos en el elemento principal 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 y optimizar esta base de datos, el Asistente para la optimización de motor de base de datos efectúa llamadas al servidor de producción para poder llevar a cabo los pasos que se indican a continuación:

   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 de shell del servidor de pruebas. Los metadatos incluyen tablas vacías, índices, vistas, procedimientos almacenados, desencadenadores, etc. Este paso permite ejecutar las consultas de carga de trabajo en la base de datos de shell del servidor de prueba.

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

   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 la memoria disponible del servidor de producción para proporcionar al optimizador de consultas la información que necesita para generar un plan de consultas.

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

4. Aplique la recomendación recibida del servidor de prueba en el servidor de producción.

La siguiente ilustración representa el escenario de los servidores de prueba y producción:

Nota:

La característica de optimización del servidor de prueba no es compatible con la interfaz gráfica de usuario (GUI) del Asistente para la optimización de motor de base de datos.

Ejemplo

Asegúrese primero de que el usuario que desea realizar la optimización existe tanto en el servidor de prueba como en el de producción.

Una vez copiada la información de usuario en el servidor de prueba, puede definir la sesión de ajuste del servidor de prueba en el archivo de entrada XML del Asistente para la optimización de motor de base de datos. El siguiente ejemplo de archivo de entrada XML ilustra 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 se optimiza la base de datos MyDatabaseName en MyServerName. El script Transact-SQL, MyWorkloadScript.sql, se usa como carga de trabajo. Esta carga de trabajo contiene eventos que se ejecutarán en MyDatabaseName. La mayoría de las llamadas del optimizador de consultas a esta base de datos, llamadas que forman parte del proceso de optimización, se gestionan desde la base de datos de shell en el servidor MyTestServerName. La base de datos de shell se compone de metadatos y estadísticas. Al aplicar el proceso se descarga al servidor de prueba el impacto en el rendimiento que causa la optimización. 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, solo debe tener en cuenta los índices (<FeatureSet>IDX</FeatureSet>), sin particionar, 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="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>  

Consulte también

Consideraciones acerca del uso de servidores de prueba
Referencia del archivo de entrada XML (Asistente para la optimización de motor de base de datos)