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 realizar 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.
También puede reducir el efecto del Asistente para la optimización Motor de base de datos en la carga del servidor utilizando SQL Server 2008. Las versiones anteriores del Asistente para la optimización de Motor de base de datos pueden hacer que el optimizador de consultas vuelva a compilar los planes que hacen referencia a las tablas optimizadas. La nueva compilación se produce cuando el Asistente para la optimización de Motor de base de datos invalida los planes en la memoria caché de procedimientos durante una sesión de optimización. No tiene lugar hasta que la consulta se ejecuta por primera vez después de una sesión de optimización. El Asistente para la optimización de motor de base de datos de SQL Server 2008 no invalida ningún plan en la memoria caché de procedimientos, lo que evita por tanto estas nuevas compilaciones.
La información siguiente describe cómo utilizar un servidor de pruebas con el Asistente para la optimización de Motor de base de datos.
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 pruebas no es idéntico al del servidor de producción, disminuirá la calidad de las recomendaciones que efectúe el 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 lleva a cabo la optimización de una base de datos en un servidor de producción procediendo a la descarga de gran parte de la carga de optimización en el 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. De hecho, 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. Sólo 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:
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 de la función fija de servidor sysadmin de ambos equipos no es necesario que lleve a cabo este paso.
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:
El Asistente para la optimización de Motor de base de datos importa los metadatos de la base de datos de producción en 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.
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 prueba.
El Asistente para la optimización de Motor de base de datos importa los parámetros de hardware y especifica el número de procesadores y memoria disponible del servidor de producción a fin de suministrar al optimizador de consultas la información que requiere para generar un plan de consultas.
Después de que el Asistente para la optimización de Motor de base de datos termina de optimizar la base de datos de shell del servidor de pruebas, genera una recomendación de optimización.
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:
.gif "Uso del servidor de prueba del Asistente para la optimización de motor de base de datos")
Nota
La característica de optimización del servidor de prueba no se admite en 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.
Después de copiar la información del usuario al servidor de pruebas, puede definir la sesión de optimización del mismo en el archivo de entrada XML del Asistente para la optimización de Motor de base de datos. El siguiente archivo de entrada XML de ejemplo muestra cómo especificar un servidor de pruebas para optimizar 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. Como carga de trabajo se utiliza un script Transact-SQL, MyWorkloadScript.sql. 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 la recomendación de optimización con este archivo de entrada XML, éste debería considerar únicamente los índices (<FeatureSet>IDX</FeatureSet>), pero no las particiones y tampoco precisa mantener ninguna de las estructuras de diseño físicas 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>