Cómo generar una secuencia de comandos de los metadatos de la base de datos necesarios para crear una base de datos sólo estadísticas de SQL Server

INTRODUCCIÓN

El optimizador de consultas de Microsoft SQL Server utiliza los siguientes tipos de información para determinar un plan de consulta óptimo:
  • Los metadatos de la base de datos
  • El entorno de hardware
  • El estado de sesión de base de datos
Normalmente, se deben simular todos estos mismos tipos de información si desea reproducir el comportamiento del optimizador de consultas en un sistema de prueba.

Servicios de soporte técnico de Microsoft puede pedirle que para generar una secuencia de comandos de los metadatos de la base de datos. Servicios de soporte técnico de Microsoft utiliza esta secuencia de comandos de los metadatos de la base de datos para investigar un problema del optimizador. Este artículo describe los pasos para generar la secuencia de comandos estadísticas. El artículo también describe cómo el optimizador de consultas utiliza la información.

Más información

Si utiliza SQL Server 2005, antes de seguir los pasos para generar la secuencia de comandos, asegúrese de que SQL Server Management Studio es la versión de SQL Server 2005 Service Pack 2 o una versión posterior. Si utiliza versiones anteriores de SQL Server Management Studio en SQL Server 2005, el Asistente de Script no contiene todas las opciones necesarias para los pasos de este artículo funcione correctamente.

Secuencia de comandos de la base de datos completa

Al generar una base de datos del clon sólo las estadísticas, puede ser más fácil y más confiable a toda la base de datos en lugar de secuencias de comandos de objetos individuales de la secuencia de comandos. Cuando la secuencia de comandos toda la base de datos, recibirá los siguientes beneficios:
  • Para evitar problemas que le faltan objetos dependientes necesarios para reproducir el problema.
  • Requieren mucho menos pasos para seleccionar los objetos necesarios.
Nota: Si se genera un script para una base de datos y los metadatos de la base de datos contienen miles de objetos, el proceso de scripting consume muchos recursos de CPU. Recomendamos que genera la secuencia de comandos durante horas de poca actividad. O bien, puede utilizar la segunda opción para generar la secuencia de comandos para objetos individuales.

Para cada base de datos que hace referencia la consulta de la secuencia de comandos, siga estos pasos:
  1. Abra SQL Server Management Studio.
  2. En el Explorador de objetos, expanda bases de datosy, a continuación, busque la base de datos que desea que la secuencia de comandos.
  3. Haga clic en la base de datos, seleccione tareasy, a continuación, haga clic en Generar secuencias de comandos.
  4. En el Asistente para secuencias de comandos, compruebe que está activada la base de datos correcta. Haga clic para activar la casilla de verificación secuencias de comandos de todos los objetos en la base de datos seleccionada y, a continuación, haga clic en siguiente.
  5. En el cuadro de diálogo Elegir opciones de secuencia de comandos , cambiar la siguiente configuración el valor predeterminado para el valor que aparece en la tabla siguiente.
    Opción de secuencias de comandosValor para seleccionar
    Relleno ANSITrue
    Continuar en caso de Error de secuencias de comandosTrue
    Generar secuencia de comandos para objetos dependientesTrue
    Incluir nombres de restricción del sistemaTrue
    Intercalación de secuencia de comandosTrue
    Crear base de datos de secuencias de comandosTrue
    Incluir inicios de sesiónTrue
    Permisos de nivel de objeto de secuencia de comandosTrue
    Secuencia de comandos estadísticasIncluir estadísticas e histogramas
    Incluir los índicesTrue
    Incluir desencadenadoresTrue
    Nota: La opción Incluir inicios de sesión y la opción de Permisos de nivel de objeto de secuencia de comandos no podrá exigirse a menos que el esquema contiene objetos que son propiedad de inicios de sesión distinto de dbo.
  6. Haga clic en Siguiente.
  7. Haga clic en la opción de secuencia de comandos al archivo y, a continuación, escriba un nombre de archivo.
  8. Haga clic en Finalizar.

Objetos de secuencias de comandos individuales

Es posible que sólo secuencias de comandos objetos individuales que se hace referencia en una consulta determinada en lugar de secuencias de comandos de la base de datos completa. Sin embargo, a menos que todos los objetos de base de datos creados mediante la cláusula WITH SCHEMABINDING, la información de dependencia de la tabla de sistema sys.depends no siempre sean precisa. Esta inexactitud puede ocasionar uno de los siguientes problemas:
  • El proceso de scripting no secuencias de comandos un objeto dependiente.
  • El proceso de scripting puede incluir objetos en un orden incorrecto. Para ejecutar correctamente la secuencia de comandos, debe editar manualmente el script generado.
Por lo tanto, es recomendable incluir los objetos individuales, a menos que la base de datos tiene un montón de objetos y secuencias de comandos de lo contrario puede tardar mucho tiempo. Si debe usar objetos de secuencias de comandos individuales, siga estos pasos:
  1. En SQL Server Management Studio, expanda bases de datosy, a continuación, busque la base de datos que desea que la secuencia de comandos.
  2. (Ratón) en la base de datos, elija Base de datos de secuencias de comandos como, seleccione CREATE ay, a continuación, haga clic en archivo.
  3. Escriba un nombre de archivo y, a continuación, haga clic en Guardar.

    El contenedor de base de datos principal se escribirán. Este contenedor incluye archivos, grupos de archivos, la base de datos y propiedades.
  4. Haga clic en la base de datos, seleccione tareasy, a continuación, haga clic en Generar secuencias de comandos.
  5. Asegúrese de que está seleccionada la base de datos correcta y, a continuación, haga clic en siguiente.
  6. En el cuadro de diálogo Elegir opciones de secuencia de comandos , cambiar la siguiente configuración el valor predeterminado para el valor que aparece en la tabla siguiente.
    Opción de secuencias de comandosValor para seleccionar
    Relleno ANSITrue
    Continuar en caso de Error de secuencias de comandosTrue
    Incluir nombres de restricción del sistemaTrue
    Generar secuencia de comandos para objetos dependientesTrue
    Intercalación de secuencia de comandosTrue
    Incluir inicios de sesiónTrue
    Permisos de nivel de objeto de secuencia de comandosTrue
    Secuencia de comandos estadísticasIncluir estadísticas e histogramas
    Usar base de datos de la secuencia de comandosTrue
    Incluir los índicesTrue
    Incluir desencadenadoresTrue
    Nota: La opción Incluir inicios de sesión y la opción de Permisos de nivel de objeto de secuencia de comandos no podrá exigirse a menos que el esquema contiene objetos que son propiedad de inicios de sesión distinto de dbo.
  7. En el cuadro de diálogo Elegir tipos de objeto , seleccione todos los tipos de objeto de base de datos que hace referencia la consulta problemática.

    Por ejemplo, si la consulta hace referencia sólo a tablas, seleccione las tablas. Si la consulta hace referencia a una vista, seleccionar tablas y vistas. Si la consulta problemática utiliza una función definida por el usuario, seleccione funciones.
  8. Cuando haya seleccionado todos los tipos de objeto que hace referencia la consulta, haga clic en siguiente.
  9. Aparecerá un cuadro de diálogo para cada tipo de objeto de base de datos que seleccionó en el paso 7. En cada cuadro de diálogo, seleccione las tablas específicas, vistas, funciones u otros objetos de base de datos y, a continuación, haga clic en siguiente.
  10. Haga clic en la opción de secuencia de comandos al archivo y, a continuación, especifique el mismo nombre de archivo que especificó en el paso 3.
  11. Haga clic en Finalizar para iniciar la secuencia de comandos.
Cuando haya finalizado la secuencia de comandos, envíe el archivo de secuencia de comandos para el ingeniero de soporte técnico de Microsoft. El ingeniero de soporte técnico de Microsoft también puede solicitar la siguiente información:
  • La configuración de hardware, incluido el número de procesadores y la cantidad de memoria física existe
  • Las opciones SET que estaban activas cuando se ejecutó la consulta
Nota: Puede haber proporcionado esta información mediante el envío de un informe SQLDiag o una traza del analizador de SQL. Puede haber también utilizó otro método para proporcionar esta información.

Utilización de la información

Las siguientes tablas ayudan a explicar cómo el optimizador de consultas utiliza esta información para seleccionar un plan de consulta.

Metadatos

RestriccionesCon frecuencia, el optimizador de consultas utiliza restricciones para detectar las contradicciones entre la consulta y el esquema subyacente. Por ejemplo, si la consulta tiene un "col WHERE = 5" cláusula y una restricción de comprobación "CHECK (col < 5)" existe, el optimizador sabe que no coincidirá con ninguna fila.

El optimizador de consultas toma tipos similares de deducciones sobre la aceptación de valores NULL. Por ejemplo, la cláusula "Donde col es NULL" se conoce como true o false dependiendo de la aceptación de valores NULL de la columna, y si es la columna de la tabla exterior de una combinación externa. La presencia de restricciones de clave externa es útil para determinar el orden de combinación adecuada y cardinalidad. El optimizador de consultas puede utilizar la información de restricción para eliminar las combinaciones o simplificar predicados. Estos cambios pueden quitar el requisito para tener acceso a las tablas base.
EstadísticasLa información de estadísticas contiene densidad y un histograma que muestra la distribución de la columna izquierda de la clave de índice y estadísticas. Según la naturaleza del predicado, el optimizador de consultas puede utilizar densidad, el histograma o ambos para estimar la cardinalidad de un predicado. Las estadísticas actualizadas son necesarias para las estimaciones de cardinalidad exacta. Las estimaciones de cardinalidad se utilizan como entrada al estimar el costo de un operador. Por lo tanto, debe tener las estimaciones de cardinalidad buena para obtener planes de consultas óptimos.
Tamaño de la tabla (número de filas y páginas)El optimizador de consultas utiliza los histogramas y densidad para calcular la probabilidad de que un predicado especificado sea true o false. El cálculo de cardinalidad final se calcula multiplicando la probabilidad por el número de filas que se devuelven por el operador secundario. El número de páginas en la tabla o el índice es un factor para estimar el costo de IO. Tamaño de la tabla se utiliza para calcular el costo de un análisis y es útil al estimar el número de páginas que se tendrá acceso durante una búsqueda de índice.
Opciones de base de datosVarias opciones de base de datos pueden afectar a la optimización. Las opciones AUTO_CREATE_STATISTICS y AUTO_UPDATE_STATISTICS afectan a si el optimizador de consultas creará nuevas estadísticas o actualizar estadísticas que están actualizadas. El nivel de PARAMETRIZACIÓN afecta a cómo se parametriza la consulta de entrada antes de la consulta de entrada se pasa al optimizador de consultas. Parametrización puede afectar al cálculo de cardinalidad y también puede evitar la comparación con las vistas indizadas y otros tipos de optimizaciones. La opción DATE_CORRELATION_OPTIMIZATION hace que el optimizador buscar correlaciones entre las columnas. Esta configuración afecta a la estimación de coste y cardinalidad.

Entorno de

ESTABLECER las opciones de sesiónEl ANSI_NULLS configuración afecta a si el "NULL = NULL" expresión se evalúa como true. Cálculo de cardinalidad para las combinaciones externas puede cambiar dependiendo de la configuración actual. Además, también pueden cambiar las expresiones ambiguas. Por ejemplo, el "col = NULL" expresión se evalúa como varían en función de la configuración. Sin embargo, la "col IS NULL" expresión siempre evalúa de la misma manera.
Recursos de hardwareEl costo para los operadores de ordenación y hash depende de la cantidad relativa de memoria disponible para SQL Server. Por ejemplo, si el tamaño de los datos es mayor que la memoria caché, el optimizador sabe que los datos siempre deben estar en cola en el disco. Sin embargo, si el tamaño de los datos es mucho menor que la memoria caché, la operación es probable que se realiza en la memoria. SQL Server también tiene en cuenta diferentes optimizaciones si el servidor tiene más de un procesador y si no se ha deshabilitado el paralelismo con una sugerencia "MAXDOP" o la opción de configuración de la opción max degree of parallelism .
Propiedades

Id. de artículo: 914288 - Última revisión: 17 ene. 2017 - Revisión: 1

Comentarios