Compartir a través de

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

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 del producto original: SQL Server
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 los 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 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.

Los servicios de soporte al cliente de Microsoft pueden 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 usa el optimizador de consultas 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 sobre ella, el valor de clave alta de cada paso estará en el script de estadísticas generadas.

Script de toda la base de datos

Al generar una base de datos clonada de solo 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. Al crear un script para toda la base de datos, recibirá las siguientes ventajas:

  • Se evitan problemas con los objetos dependientes que faltan 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 punta, o bien puede usar la segunda opción Script Individual Objects para generar el script para objetos individuales.

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

  1. Abra SQL Server Management Studio.

  2. En el Explorador de objetos, expanda Bases de datos y, 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 está seleccionada la base de datos correcta. Haga clic para seleccionar la base de datos completa del 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 al 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 Script statistics and histograms (Estadísticas de script y histogramas)
    Índices de script Verdadero
    Desencadenadores de script Verdadero

    Nota:

    Tenga en cuenta que la opción Inicios de sesión de script y la opción Permisos de nivel de objeto de script podrían no ser necesarias a menos que el esquema contenga objetos que sean propiedad de los 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 en la base de datos completa. Sin embargo, a menos que todos los objetos de base de datos se crearan mediante la WITH SCHEMABINDING cláusula , es posible que la información de dependencia de la tabla del sys.depends sistema no sea siempre precisa. Esta inexactitud puede provocar uno de los siguientes problemas:

  • El proceso de scripting no crea un script para un objeto dependiente.

  • El proceso de scripting podría 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 scripts tardaría demasiado tiempo. Si debe usar objetos individuales de script, siga estos pasos:

  1. En SQL Server Management Studio, expanda Bases de datos y, 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 (Script Database As) y, a continuación, seleccione CREATE To (Crear en) y, a continuación, seleccione Archivo.

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

    El contenedor de base de datos principal se scriptará. Este contenedor incluye archivos, grupos de archivos, la base de datos y las 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 está seleccionada la base de datos correcta 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 objeto 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 en la página Opciones avanzadas de scripting.

    Opción de scripting Valor que se va a seleccionar
    Relleno ansi Verdadero
    Continuar scripting al 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 Script statistics and histograms (Estadísticas de script y histogramas)
    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 sean propiedad de 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 to File ) y, a continuación, 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 puede 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 al ejecutar 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

Las tablas siguientes ayudan a explicar 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 las contradicción 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 ninguna fila coincidirá. El optimizador de consultas hace 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 acceder 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. Según la naturaleza del predicado, el optimizador de consultas puede usar densidad, el histograma o ambos para calcular la cardinalidad de un predicado. Se requieren estadísticas actualizadas para las estimaciones de cardinalidad precisas. 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 al calcular el número de páginas a las que se tendrá acceso 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 no están actualizadas. 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 evitar la coincidencia con vistas indexadas 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 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 tiene distintas optimizaciones si el servidor tiene más de un procesador y si el paralelismo no se ha deshabilitado mediante una MAXDOP sugerencia o la opción de configuración grado máximo de paralelismo.

Consulte también