El SQL Server Query Store es una nueva característica recién introducida en SQL Server 2016. En pocas palabras, es un “registrador de vuelo” o “caja negra” del SQL Server, que captura un historial de consultas ejecutadas además consulta las estadísticas de ejecución en tiempo de ejecución, planes de ejecución y base de datos específica. Esta información nos ayuda a identificar los problemas de rendimiento causados por los cambios en el plan de consultas y la solución de problemas al encontrar rápidamente las diferencias de rendimiento, incluso después del reinicio o la actualización de SQL Server. Todos los datos que la captura de Almacén de consultas de SQL Server se almacena en el disco.
En este artículo se cubrirá una explicación de las opciones de Almacén de consultas de SQL Server y una breve descripción de los informes integrados de Almacén de consultas de SQL Server.
Estos son los escenarios comunes en los que la característica de Almacén de consultas de SQL Server puede ser útil:
- Encontrar las consultas más caras para CPU, E/S, memoria, etc.
- Obtener el historial completo de ejecuciones de consulta.
- Obtener información sobre regresiones de consultas (un nuevo plan de ejecución generado por el motor de consultas para ver si el plan es peor que el anterior).
- Encontrar rápidamente la regresión de rendimiento y corregir forzando el plan de consulta anterior, cuyo rendimiento es mucho mejor que el de un nuevo plan generado.
- Determinar cuántas veces se ejecutó una consulta en el intervalo de tiempo dado.
Hechos rápidos
- El almacén de consultas de SQL Server es una función de nivel de base de datos, lo que significa que se puede habilitar en cada base de datos SQL por separado mediante el uso de SQL Server Management Studio o T-SQL. No es una configuración de nivel de instancia.
- El Almacén de consultas de SQL Server nos permite analizar el rendimiento de las consultas mediante informes integrados y DMW de una forma rápida y sencilla.
- La característica del Almacén de consultas de SQL Server está disponible en todas las ediciones de SQL Server.
- Tiene que tomarr en cuenta que, en las bases de datos de Azure, la función del Almacén de consultas de SQL Server está habilitada de forma predeterminada.
- Los permisos mínimos para usar el almacén de consultas de SQL Server son los permisos de VER ESTADO DE BASE DE DATOS:
|
1 2 3 |
USE AdventureWorks2014 GO GRANT VIEW DATABASE STATE TO [USER]; |
Como poder preparar
Para poder habilitar el Almacén de consultas de SQL Server para una base de datos en el servidor SQL prometido, tiene que hacer clic con el botón derecho en una base de datos en el Explorador de objetos y en el menú contextual, elija la opción Propiedades:
Desde el cuadro de diálogo Propiedades de la base de datos en la sección Seleccionar una página, seleccione la página Almacén de consultas:
En el cuadro desplegable Modo de operación (solicitado), tiene que elegir el elemento de lectura y escritura:
Tan pronto como sea posible, elija el elemento de lectura y escritura, los otros campos del cuadro desplegable Modo de operación (solicitado) se rellenarán previamente con los valores predeterminados:
Al momento de presionar el botón Aceptar en el cuadro de diálogo Propiedades de la base de datos, el almacén de consultas de SQL Server se habilita para capturar los planes de ejecución de consultas y la información de tiempo de ejecución.
Para poder confirmar que el Almacén de consultas de SQL Server está habilitado en la base de datos elegida, tiene que ir al Explorador de objetos, actualice y expanda la base de datos. La carpeta del almacén de consultas de SQL Server aparecerá con la lista de informes integrados disponibles:
Para poder habilitar el almacén de consultas de SQL Server mediante T-SQL, ejecute la siguiente declaración en una ventana de consulta:
ALTER DATABASE [AdventureWorks2014] SET QUERY_STORE = ON;
Tiene que tener en cuenta que el almacén de consultas de SQL Server no se puede habilitar para la base de datos master o tempdb.
El almacén de consultas de SQL Server tendrá un impacto en el rendimiento de SQL Server 3-5 % en promedio por indicación de Microsoft.
Las opciones
La primera opción en la página del Almacén de consultas de SQL Server del cuadro de diálogo Propiedades de la base de datos es la opción Modo de operación (activa):
Esta opción se encuentra deshabilitada y no se puede cambiar. Esta opción indica el estado del almacén de consultas de SQL Server. Existen tres modos del almacén de consultas de SQL Server y son los siguientes: Desactivado, Sólo lectura y Lectura y escritura.
Desactivado – el almacén de consultas de SQL Server está desactivado
Solo lectura – este modo indica que las nuevas estadísticas de tiempo de ejecución de consulta o los planes ejecutados no serán rastreadas (recopiladas)
Lectura de escritura – permite capturar planes ejecutados de consulta y consultar estadísticas de tiempo de ejecución
En el Modo de operación (solicitado), en el cuadro desplegable, se pueden establecer las mismas opciones que las mencionadas para la opción anterior. El momento que establezcamos, los valores tendrán un impacto directo en el estado del Almacén de consultas.
Como por ejemplo, si en el modo de operación (solicitado), en el cuadro desplegable se elige el valor de solo lectura:
El código T-SQL equivalente para la misma opción es:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( OPERATION_MODE = READ_ONLY ); |
En la opción Intervalo de descarga de datos (minutos), se puede configurar un intervalo en minutos que muestre la frecuencia con la que las estadísticas de tiempo de ejecución de la consulta y los planes de ejecución de la consulta se vaciarán de la memoria de la instancia de SQL Server al disco. De forma predeterminada, esta opción se establece en 15 minutos:
Si esta opción se establece en un valor más bajo que la frecuencia de las descargas, los datos de la memoria al disco se producirán con frecuencia, lo que hará que tenga un impacto negativo en el rendimiento de la instancia de SQL Server. Pero si el valor aumenta, más información de Almacén de consultas de SQL Server se colocará en la memoria de la instancia de SQL Server antes de que se descargue en el disco, lo que hará que aumente el riesgo de perder esos datos en caso de que el servidor se reinicie o se bloquee.
A continuación se muestra el código T-SQL para configurar la opción Intervalo de descarga de datos (minutos):
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( DATA_FLUSH_INTERVAL_SECONDS = 900 ); |
La opción Intervalo de recopilación de estadísticas definió el intervalo de agregación de las estadísticas de tiempo de ejecución de la consulta que deben usarse dentro del almacén de consultas de SQL Server. Por defecto, el tiempo que se establece es de 60 minutos. Un valor más bajo significa que la granularidad de las estadísticas en tiempo de ejecución de consultas es más precisa, debido a eso, se producen más intervalos que necesitan más espacio en el disco para almacenar las estadísticas en tiempo de ejecución de las consultas.
El código T-SQL para configurar la opción Intervalo de recopilación de estadísticas:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( INTERVAL_LENGTH_MINUTES = 1440 ); |
Tenga en cuenta que, en el código T-SQL para la opción Intervalo de recopilación de estadísticas, se pueden configurar los siguientes valores en los minutos 1, 5, 10, 15, 30, 60, 1440.
En caso de que esté en INTERVAL_LENGTH_MINUTES, establezca algún otro número:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( INTERVAL_LENGTH_MINUTES = 900 ); |
El siguiente mensaje aparecerá el momento en el que se ejecute la instrucción:
Msg 12432, Nivel 16, Estado 1, Línea 1
La longitud del intervalo del almacén de consultas de SQL Server no se puede cambiar porque se proporcionó un valor no válido. Vuelva a intentarlo con un valor válido (1, 5, 10, 15, 30 y 60).
Mensaje 5069, Nivel 16, Estado 1, Línea 1
La instrucción ALTER DATABASE falló.
La opción Tamaño máximo (MB) es para poder configurar el tamaño máximo del almacén de consultas de SQL Server. De una forma predeterminada, el tamaño máximo del almacén de consultas de SQL Server se establece en 100 MB. Los datos en el Almacén de consultas de SQL Server se almacenan en la base de datos donde ya está habilitado el Almacén de consultas de SQL Server. El Almacén de consultas de SQL Server no crece automáticamente y una vez que el Almacén de consultas de SQL Server alcance el tamaño máximo, el Modo de operación cambiará automáticamente al modo de solo lectura y no se recopilarán las nuevas estadísticas del plan de ejecución de consulta y el tiempo de ejecución de la consulta:
El código T-SQL para establecer el tamaño máximo del almacén de consultas de SQL Server es:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( MAX_STORAGE_SIZE_MB = 1024 ); |
La opción Modo de captura del almacén de consultas determina qué tipo de consulta se capturará en el Almacén de consultas. De una manera predeterminada, la opción Modo de captura del almacén de consultas está establecida en Todas, lo que quiere decir es que cada consulta ejecutada se almacenará en el Almacén de consultas de SQL Server que se ejecuta en la base de datos. Cuando la opción Modo de captura del almacén de consultas está configurada en Automática, el Almacén de consultas de SQL Server intentará determinar qué captura de consultas, por prioridad, e intentará ignorar las consultas ejecutadas con poca frecuencia y otras consultas ad hoc. Además, que existe el tercer valor en el cuadro desplegable de Modo de captura del almacén de consultas que es Ninguno. Cuando se elige el valor Ninguno, entonces el Almacén de consultas de SQL Server no recopilará información para las nuevas consultas y solo continuará recopilando información sobre las consultas que se han registrado anteriormente:
El código T-SQL para configurar esta opción es:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( QUERY_CAPTURE_MODE = ALL ); |
La opción Modo de limpieza basada en tamaño es para limpiar los datos del Almacén de consultas de SQL Server cuando el tamaño máximo en la opción Tamaño máximo (MB) se alcanza al 90 % de la capacidad. El proceso de limpieza empieza a eliminar los datos de consulta más antiguos y menos costosos. El proceso de limpieza se detiene cuando se alcanza el 80 % del tamaño máximo en la opción Tamaño máximo (MB). De una forma predeterminada, esta opción está configurada en Auto. Si en el cuadro desplegable Modo de limpieza basada en tamaño se establece el valor Desactivado, el proceso de limpieza no se realizará cuando el tamaño del almacén de consultas de SQL Server alcance el 90 % del tamaño máximo y el almacén de consultas de SQL Server vaya al modo de Solo Leer Solo cuando se alcanza el tamaño máximo:
El código T-SQL para configurar esta opción es:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( SIZE_BASED_CLEANUP_MODE = AUTO ); |
La opción de umbral de consulta obsoleta (días) es para poder definir cuánto tiempo permanecerán los datos en el almacén de consultas de SQL Server. Por defecto, esto está configurado para 30 días.
El código T-SQL para configurar esta opción es:
|
1 2 3 4 5 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 30) ); |
En más opciones que se pueden configurar a través de T-SQL y que no están presentes en la pestaña del Almacén de consultas de SQL Server está MAX_PLANS_PER_QUERY:
|
1 2 3 4 5 6 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE = ON ( MAX_PLANS_PER_QUERY=200 ); |
Con esta opción, se pueden llegar a establecer los Planes de Ejecución máximos que se almacenarán en el Almacén de consultas de SQL Server por consulta. De una forma predeterminada, esto se establece en 200 planes de ejecución por consulta.
La última opción en la pestaña Almacén de consultas de SQL Server es una opción que borra / purga todos los datos en el Almacén de consultas de SQL Server presionando el botón Purgar datos de consulta:
Se puede hacer lo mismo ejecutando el siguiente código T-SQL en la ventana de consulta:
|
1 |
ALTER DATABASE AdventureWorks2014 SET QUERY_STORE CLEAR; |
La Arquitectura y Colección
El Almacén de consultas de SQL Server recopila el texto de consulta, el plan de consulta y las estadísticas de tiempo de ejecución de la consulta y las coloca en la memoria.
Tiene que tener en cuenta que los planes de consulta que muestran el Almacén de consultas de SQL Server son solo planes de ejecución estimados, no planes de ejecución reales.
Nota: Puede encontrar más información sobre los planes estimados y los planes reales en el siguiente artículo: Estimado Vs Plan de ejecución real de SQL Server.
El Almacén de consultas de SQL Server extrae cada declaración de consulta de la consulta. Como por ejemplo, si en el procedimiento almacenado existen varias declaraciones de consulta, el Almacén de consultas de SQL Server almacenará cada declaración de consulta por separado y obtendrá las estadísticas de tiempo de ejecución de consulta de cada declaración de consulta.
El único plan de consulta y el texto de la consulta, si es nuevo, se pasarán directamente a ASYNC Writer, que lo escribirá en el disco.
Las estadísticas de tiempo de ejecución de la consulta no se escribirán de forma directa en el ASYNC Writer. Las estadísticas de tiempo de ejecución de la consulta se escribirán en un intervalo de tiempo específico el cual se establece en la opción de Intervalo de descarga de datos (minutos).
El Almacén de consultas de SQL Server reúne toda la información, sin importar si la información está almacenada en el disco o en la memoria y los muestra en los informes integrados:
Informes
Como se mencionó anteriormente en el artículo, una vez que este habilitado, el Almacén de consultas de SQL Server, este comenzará a recopilar estadísticas de tiempo de ejecución de consulta y consultar los planes de ejecución. Los informes incorporados utilizan datos recopilados que los analizan y los muestran en formato de cuadrícula o gráfico en función de lo que se establece en los informes.
Actualmente, ya hay seis informes incorporados.
Consultas devueltas se trata de un informe incorporado que muestra todas las consultas de que las matrices de ejecución que se degradan en un rango de tiempo específico (última hora, día, semana)
El informe integrado de Consultas Devuletas se divide en varios paneles. De una forma predeterminada, se exhiben las 25 consultas regresivas principales en la última hora.
Aquí, ya se pueden configurar diferentes opciones para poder ver la información deseada. Como por ejemplo, si desea ver la fecha sin procesar en lugar del gráfico, hay que presionar el botón para mostrar las consultas regresivas en un formato de cuadrícula:
El informe integrado de Consumo de recursos global muestra el consumo de recursos de resumen durante el conjunto específico de tiempo. De una forma predeterminada, los resultados se muestran durante el último mes y los resultados se muestran en cuatro cuadros: Duración, Tiempo de CPU, Lecturas lógicas y Recuento de ejecución:
Para poder configurar el informe de gráficos, el tiempo y el intervalo de agregación adicional, tiene que presionar el botón Configurar y aparecerá el cuadro de diálogo Configurar consumo global de recursos donde se pueden configurar las diferentes opciones para el informe de Consumo general de recursos:
El informe integrado de las Mejores Consultas de Consumo de Recursos se muestra de manera predeterminada, las 25 consultas principales contra una base de datos específica que consume la mayoría de los recursos como el Tiempo de CPU, el Consumo de Memoria, las Lecturas Físicas, etc. durante un conjunto específico de tiempo:
Con el informe integrado Consultas rastreadas, las estadísticas de tiempo de ejecución de consultas y los planes de ejecución de consultas se pueden rastrear para la consulta específica a lo largo del tiempo. En el cuadro de texto Consulta de seguimiento, tiene que ingresar la identificación de la consulta (por ejemplo, 205) y presione el botón verde de reproducción junto al cuadro de consulta de Seguimiento:
El informe integrado Consultas con planes forzados nos muestra todos los Planes de ejecución forzados para consultas específicas:
Para forzar a SQL Server a usar un plan de ejecución específico para la consulta en particular, en los informes integrados Consultas en regresión, Consumo de recursos principales, Consultas con alta variación o Consultas rastreadas, primero tiene que seleccionar el ID del plan de ejecución y hacer clic en el botón Forzar plan:
Presione el botón de Sí en el cuadro de mensaje de confirmación:
Al hacer esto, obliga a que el SQL Server use este plan de ejecución para consultas específicas a partir de ahora cuando se ejecute esa consulta. Lo que significa que SQL Server no generará un nuevo Plan de Ejecución para esa consulta hasta que se desactive ese plan.
Para poder desestimar a SQL Server para poder usar un plan de ejecución específico para la consulta de partículas en el informe Consultas con planes forzados, Consultas regresivas, Consultas con mayor consumo de recursos, Consultas con alta variación o Consultas rastreadas, seleccione el Plan de ejecución y tiene que presionar el botón Unforce Plan:
En el cuadro de mensaje de confirmación, tiene que presionar el botón Sí para confirmar la eliminación del Plan de ejecución forzado:
Esto hará que se elimine el Plan de ejecución del informe Consultas con planes forzados.
El informe integrado Consultas con alta variación analiza las consultas y nos muestra las consultas con los problemas de parametrización más frecuentes:
Conclusión
El Almacén de consultas de SQL Server es una característica muy poderosa, para los usuarios de SQL Server 2016 y superior, el cual realiza un seguimiento de la ejecución en tiempo de ejecución de las consultas y los planes de ejecución de consultas, además que monitorea y analiza el rendimiento de las consultas y nos muestra los resultados en los informes integrados. Esto nos proporciona nuevas capacidades poderosas en comparación con los métodos anteriores a los DBA que fueron relegados a usar en versiones anteriores de SQL Server.
Actualmente trabajando para ApexSQL LLC como un Ingeniero de Ventas de Software, él está ayudando a los clientes con problemas técnicos y hace aseguramiento de calidad para los complementos ApexSQL Complete, ApexSQL Refactor y ApexSQL Search.
Ver todas las entradas de Marko Zivkovic