Cómo generar un script de estadísticas para crear una base de datos de solo estadísticas en SQL Server

  • Artículo

En este artículo, aprenderá a generar un script de estadísticas mediante metadatos de base de datos para crear una base de datos de solo estadísticas en SQL Server.

Versión original del producto: SQL Server 2014, SQL Server 2012, SQL Server 2008

Número de KB original: 914288

Introducción

DBCC CLONEDATABASE es el método preferido para generar un clon de solo esquema de una base de datos para investigar problemas de rendimiento. Use el procedimiento de este artículo solo cuando no pueda usar DBCC CLONEDATABASE.

El optimizador de consultas de Microsoft SQL Server usa los siguientes tipos de información para determinar un plan de consulta óptimo:

  • metadatos de base de datos
  • entorno de hardware
  • estado de sesión de la base de datos

Normalmente, debe simular todos estos mismos tipos de información para reproducir el comportamiento del optimizador de consultas en un sistema de prueba.

Microsoft Customer Support Services podría pedirle que genere un script de los metadatos de la base de datos para investigar un problema del optimizador de consultas. En este artículo se describen los pasos para generar el script de estadísticas y también se describe cómo el optimizador de consultas usa la información.

Nota:

Las claves guardadas dentro de estos datos pueden contener información de PII. Por ejemplo, si la tabla contiene una columna Número de teléfono con una estadística, el valor de clave alta de cada paso estará en el script de estadísticas generado.

Script de toda la base de datos

Al generar una base de datos clonada solo de estadísticas, es posible que sea más fácil y confiable crear scripts en toda la base de datos en lugar de crear scripts de objetos individuales. Cuando se crea un script de toda la base de datos, se obtienen las siguientes ventajas:

  • Se evitan problemas con los objetos dependientes que faltan y que son necesarios para reproducir el problema.
  • Necesita menos pasos para seleccionar los objetos necesarios.

Tenga en cuenta que si 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 recursos de CPU significativos. Se recomienda generar el script durante las horas de poca actividad, o puede usar la segunda opción Script Individual Objects para generar el script para objetos individuales.

Para crear scripts de cada base de datos a la que hace referencia la consulta, siga estos pasos:

  1. Abra el SQL Server Management Studio.

  2. En el Explorador de objetos, expanda Bases de datosy, a continuación, busque la base de datos que desea crear scripts.

  3. Haga clic con el botón derecho en la base de datos, seleccione Tareas y, a continuación, seleccione Generar scripts.

  4. En el Asistente para scripts, compruebe que la base de datos correcta está seleccionada. Haga clic para seleccionar la base de datos completa de script y todos los objetos de base de datos y, a continuación, seleccione Siguiente.

  5. En el cuadro de diálogo Elegir opciones de script , seleccione el botón Opciones avanzadas para cambiar la siguiente configuración del valor predeterminado al valor que aparece en la tabla siguiente.

    Opción de scripting Valor que se va a seleccionar
    Relleno ansi Verdadero
    Continuar scripting en caso de error Verdadero
    Generar script para objetos dependientes Verdadero
    Incluir nombres de restricción del sistema Verdadero
    Intercalación de scripts Verdadero
    Inicios de sesión de script Verdadero
    Permisos de nivel de objeto de script Verdadero
    Estadísticas de script Estadísticas e histogramas de script
    Índices de script Verdadero
    Desencadenadores de script Verdadero

    Nota:

    Tenga en cuenta que es posible que la opción Inicios de sesión de script y la opción Permisos de nivel de objeto de script no sean necesarios a menos que el esquema contenga objetos que pertenecen a inicios de sesión distintos de dbo.

  6. Seleccione Aceptar para guardar los cambios y cierre la página Opciones avanzadas de scripting .

  7. Seleccione Guardar en archivo y seleccione la opción Archivo único .

  8. Revise las selecciones y seleccione Siguiente.

  9. Seleccione Finalizar.

Script de objetos individuales

Solo puede crear scripts de los objetos individuales a los que hace referencia una consulta determinada en lugar de crear scripts para la base de datos completa. Sin embargo, a menos que se crearan todos los objetos de base de datos mediante la WITH SCHEMABINDING cláusula , es posible que la información de dependencia de la tabla del sys.depends sistema no siempre sea precisa. Esta imprecisión puede causar uno de los siguientes problemas:

  • El proceso de scripting no crea scripts de un objeto dependiente.

  • El proceso de scripting puede generar scripts de objetos en el orden incorrecto. Para ejecutar el script correctamente, debe editar manualmente el script generado.

Por lo tanto, no se recomienda crear scripts de objetos individuales a menos que la base de datos tenga muchos objetos y el scripting tarde demasiado tiempo. Si debe usar scripts de objetos individuales, siga estos pasos:

  1. En el SQL Server Management Studio, expanda Bases de datosy, a continuación, busque la base de datos que desea crear scripts.

  2. Haga clic con el botón derecho en la base de datos, seleccione Script Database As(Crear en) y, a continuación, seleccione File (Archivo).

  3. Escriba un nombre de archivo y, a continuación, seleccione Guardar.

    Se creará un script en el contenedor de base de datos principal. Este contenedor incluye archivos, grupos de archivos, la base de datos y propiedades.

  4. Haga clic con el botón derecho en la base de datos, seleccione Tareas y, a continuación, seleccione Generar scripts.

  5. Asegúrese de que la base de datos correcta está seleccionada y, a continuación, seleccione Siguiente.

  6. En el cuadro de diálogo Elegir tipos de objeto , elija Seleccionar objetos de base de datos específicos y seleccione todos los tipos de objetos de base de datos a los que hace referencia la consulta problemática.

    Por ejemplo, si la consulta solo hace referencia a tablas, seleccione Tablas. Si la consulta hace referencia a una vista, seleccione Vistas y tablas. Si la consulta problemática usa una función definida por el usuario, seleccione Funciones.

  7. Cuando haya seleccionado todos los tipos de objeto a los que hace referencia la consulta, seleccione Siguiente.

  8. En el cuadro de diálogo Establecer opciones de scripting , seleccione el botón Opciones avanzadas y cambie la siguiente configuración del valor predeterminado al valor que aparece en la tabla siguiente de la página Opciones avanzadas de scripting .

    Opción de scripting Valor que se va a seleccionar
    Relleno ansi Verdadero
    Continuar scripting en caso de error Verdadero
    Incluir nombres de restricción del sistema Verdadero
    Generar script para objetos dependientes Verdadero
    Intercalación de scripts Verdadero
    Inicios de sesión de script Verdadero
    Permisos de nivel de objeto de script Verdadero
    Estadísticas de script Estadísticas e histogramas de script
    Script USE DATABASE Verdadero
    Índices de script Verdadero
    Desencadenadores de script Verdadero

    Nota:

    Tenga en cuenta que es posible que las opciones Inicios de sesión de script y Permisos de nivel de objeto de script no sean necesarias a menos que el esquema contenga objetos que pertenecen a inicios de sesión distintos de dbo.

  9. Seleccione Aceptar para guardar y cerrar la página Opciones avanzadas de scripting .

    Aparece un cuadro de diálogo para cada tipo de objeto de base de datos seleccionado en el paso 7.

  10. En cada cuadro de diálogo, seleccione las tablas, vistas, funciones u otros objetos de base de datos específicos y, a continuación, seleccione Siguiente.

  11. Seleccione la opción Script to File (Script a archivo ) y especifique el mismo nombre de archivo que especificó en el paso 3.

  12. Seleccione Finalizar para iniciar el scripting.

    Cuando finalice el scripting, envíe el archivo de script al ingeniero de Soporte técnico de Microsoft. El ingeniero de Soporte técnico de Microsoft también podría solicitar la siguiente información:

    • Configuración de hardware, incluido el número de procesadores y la cantidad de memoria física que existe.

    • Opciones SET que estaban activas cuando se ejecutó la consulta.

    Tenga en cuenta que es posible que ya haya proporcionado esta información enviando un informe SQLDiag o un seguimiento de SQL Profiler. Es posible que también haya usado otro método para proporcionar esta información.

Cómo se usa la información

En las tablas siguientes se explica cómo el optimizador de consultas usa esta información para seleccionar un plan de consulta.

Metadatos

Opción Explicación
Restricciones El optimizador de consultas usa con frecuencia restricciones para detectar contradicciones entre la consulta y el esquema subyacente. Por ejemplo, si la consulta contiene la WHERE col = 5 cláusula y existe una CHECK (col < 5) restricción en la tabla subyacente, el optimizador de consultas sabe que no coincidirá ninguna fila. El optimizador de consultas realiza tipos similares de deducciones sobre la nulabilidad. Por ejemplo, se sabe que la WHERE col IS NULL cláusula es true o false en función de la nulabilidad de la columna y de si la columna procede de la tabla externa de una combinación externa. La presencia de restricciones FOREIGN KEY es útil para determinar la cardinalidad y el orden de combinación adecuado. El optimizador de consultas puede usar información de restricciones para eliminar combinaciones o simplificar predicados. Estos cambios podrían quitar el requisito de tener acceso a las tablas base.
Estadísticas La información de estadísticas contiene densidad y un histograma que muestra la distribución de la columna inicial de la clave de índice y estadísticas. En función de la naturaleza del predicado, el optimizador de consultas podría usar la densidad, el histograma o ambos para calcular la cardinalidad de un predicado. Las estadísticas actualizadas son necesarias para las estimaciones precisas de cardinalidad. Las estimaciones de cardinalidad se usan como entrada para calcular el costo de un operador. Por lo tanto, debe tener buenas estimaciones de cardinalidad para obtener planes de consulta óptimos.
Tamaño de tabla (número de filas y páginas) El optimizador de consultas usa los histogramas y la densidad para calcular la probabilidad de que un predicado determinado sea true o false. La estimación de cardinalidad final se calcula multiplicando la probabilidad por el número de filas que devuelve el operador secundario. El número de páginas de la tabla o el índice es un factor en la estimación del costo de E/S. El tamaño de la tabla se usa para calcular el costo de un examen y resulta útil cuando se calcula el número de páginas a las que se accederá durante una búsqueda de índice.
Opciones de base de datos Varias opciones de base de datos pueden afectar a la optimización. Las AUTO_CREATE_STATISTICS opciones y AUTO_UPDATE_STATISTICS afectan a si el optimizador de consultas creará nuevas estadísticas o actualizará las estadísticas que están obsoletas. El nivel de parametrización afecta a cómo se parametriza la consulta de entrada antes de que la consulta de entrada se entregue al optimizador de consultas. La parametrización puede afectar a la estimación de cardinalidad y también puede impedir la coincidencia con vistas indizadas y otros tipos de optimizaciones. La DATE_CORRELATION_OPTIMIZATION configuración hace que el optimizador busque correlaciones entre columnas. Esta configuración afecta a la cardinalidad y a la estimación de costos.

Entorno

Opción Explicación
Opciones set de sesión La ANSI_NULLS configuración afecta a si la NULL = NULL expresión se evalúa como true. La estimación de cardinalidad de las combinaciones externas puede cambiar en función de la configuración actual. Además, las expresiones ambiguas también pueden cambiar. Por ejemplo, la col = NULL expresión se evalúa de forma diferente en función de la configuración. Sin embargo, la col IS NULL expresión siempre se evalúa de la misma manera.
Recursos de hardware El costo de 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 de consultas 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é, es probable que la operación se realice en memoria. SQL Server también considera diferentes optimizaciones si el servidor tiene más de un procesador y si no se ha deshabilitado el paralelismo mediante una MAXDOP sugerencia o la opción de configuración grado máximo de paralelismo.

Vea también