Query Store y Automatic Tuning en SQL Server 2017

En el post de hoy vamos a hablar sobre Query Store, el repositorio que encontramos dentro de nuestras bases de datos SQL Server. En primer lugar, vamos a conocer mejor cuál es el objetivo de esta herramienta.

Qué es Query Store

Query Store nace en SQL Server 2016, pero no es hasta SQL Server 2017 donde muestra su gran potencial convirtiéndose así en una herramienta de monitoreo y troubleshooting esencial para los database administrators.

A grandes rasgos, simplemente almacena información relacionada con la ejecución de consultas a lo largo del tiempo. Es decir, Query Store recoge toda aquella información que considera relevante para una posible mejora de rendimiento.

La principal y nueva ventaja respecto a las prácticas anteriores al Query Store es que anteriormente, las métricas de rendimiento se agregaban como un solo total desde el último reinicio de la instancia de SQL, y se borraban en el próximo reinicio. Ahora se conservan y se dividen en el tiempo para que pueda medir los cambios a lo largo del tiempo.

La simple actividad de almacenar planes de ejecución antiguos también es importante para la resolución de problemas de rendimiento. Cualquiera que haya trabajado con multitud de datos en producción habrá experimentado el problema cuando una consulta que estaba funcionando bien, de repente comienza a desarrollar problemas de rendimiento.

Una causa común de esto es lo que se conoce como «regresión del plan«.

Este suceso ocurre cuando se actualiza un plan de ejecución y el nuevo no es tan Bueno como el anterior, en este caso, podría analizarse en el Query Store y realizar la comparativa de ambos planes. Incluso podemos forzar la consulta para que vuelva a usar el plan anterior (que es mayor que el actual). Si SQL Server detecta que el nuevo plan es mayor, pese a la pérdida de rendimiento, habría que profundizar un poco en el código o en los índices de las tablas afectadas en dicha consulta ya que habría una posible mejora a implementar.

El Query Store es una configuración a nivel de base de datos y es importante resaltar que la información obtenida por éste se almacena dentro de los ficheros de datos, por lo tanto, eso significa que si realiza una copia de seguridad y restaura la base de datos, la información se conserva.

La información se almacena de forma asincrónica, por lo que no debería haber ningún impacto en el rendimiento de las consultas ejecutadas. A diferencia de las estadísticas de consultas en vivo, como puede ser un profiler, Query Store no agregará un impacto de rendimiento. Por supuesto, habrá una sobrecarga general del servidor en el momento en que se guarden los datos, pero eso no debería ser demasiado significativo.

Modo de Activación de Query Store

En Azure SQL viene activo por defecto, pero en nuestro servidor On-Premise vendrá con el valor “Off”, es en cada una de las bases de datos donde tendremos que acudir y activarlo de la forma que creamos conveniente.

El primer ajuste es «Operation Mode«. De forma predeterminada, está establecido en «Off». Para habilitar el Almacén de consultas y hacerlo funcionar para una base de datos en específico, lo cambiaremos a «Read/Write», de esta forma, Query Store comenzará a almacenar datos sobre los planes de ejecución y estadísticas de esta base de datos.

El parámetro “Data Flush Interval” es la frecuencia con la que los datos del almacén de consultas se escriben en el disco, como dijimos, esta acción se hará de forma asíncrona (15 minutos por defecto).

El parámetro “Statistics Collection Interval” determina cada cuanto tiempo se agregan las métricas de rendimiento de la consulta (1h por defecto).

Los parámetros siguientes constan sobre la retención de datos. Lo más importante a señalar es que si el Query Store se llena y no se realiza ningún borrado, cambiará al modo de solo lectura. Con esto, Query Store no almacenará más datos, pero sí realizará lectura de éstos con tal de seguir mejorando el rendimiento de las consultas con los datos guardados.

La cantidad de espacio predeterminada establecida para él es de 100 MB; no es mucho espacio, y recordemos que se almacena en el datafile de cada base de datos, por lo que realmente no supone un problema para habilitarlo.

Si dejamos el parámetro «Size Based Cleanup Mode» configurado en Automático, debemos asegurarnos de que los datos antiguos se eliminen si el almacén de consultas comienza a llenarse.

Un parámetro muy a tener en cuenta es el «Query Store Capture Mode«: si lo deja en “AUTO”, ignorará las consultas poco frecuentes o aquellas con una sobrecarga insignificante. Por el contrario, si lo dejamos en “ALL”, Query Store capturará todas las consultas, incluidas las Ad-hoc o no parametrizadas. Esta opción no es viable en un entorno como el que disponemos, ya que se entorpecería al haber demasiadas queries no parametrizadas o Ad-hoc.

El ultimo parámetro es «Stale Query Threshold» es cuánto tiempo conserva los datos en días (30 días por defecto).

Podemos considerer útil aumentar este valor si queremos usar Query Store para poder monitizar el rendimiento durante un periodo más prolongado de tiempo, pero puede depender de la cantidad de espacio que Query Store quiera consumir en la base de datos, recordemos que el valor predeterminado es 100 MB pero podemos aumentarlo lo que sea necesario.

En la parte inferior de la página de propiedades, también se pueden ver algunos gráficos circulares que muestran qué proporción de la base de datos Query Store ha asignado y cuánto de ese espacio está usando.