Query Store es una característica de SQL Server 2016+ que nos permite almacenar un histórico de los planes de consultas, así como las estadísticas de ejecución de dichos planes. Esto nos permite analizar diferencias de rendimiento derivadas de cambios en los planes de ejecución a lo largo del tiempo. En general es una característica que la mayor parte que, como administradores de bases de datos, desearíamos tener disponible en nuestras bases de datos.

Como cualquier tipo de monitorización, añade un impacto a la carga existente, eso es inevitable, y realmente en Query Store se ha tratado de minimizar dicho impacto con varias opciones configurables:

  • DATA_FLUSH_INTERVAL_SECONDS: Controla la frecuencia con que los datos son escritos a disco, persistidos, ya que por defecto los datos se capturan asíncronamente en memoria.
  • INTERVAL_LENGTH_MINUTES: Controla la granularidad en la que los datos son agregados en Query Store. Cuanta menor granularidad más cantidad de espacio utilizaremos para almacenar estas estadísticas de ejecuciones.
  • QUERY_CAPTURE_MODE { ALL | AUTO | CUSTOM }: Permite controlar qué operaciones capturamos. El valor ALL, tiene el mayor impacto en el rendimiento, y puede ser demasiado agresivo en un entorno productivo con alta carga. El valor AUTO automáticamente descarta aquellas operaciones muy ligeras que probablemente no representen un problema para el servidor, así como aquellas que sean muy puntuales.

El modo CUSTOM es una novedad de SQL Server 2019 y nos permite aún personalizar más esta política de captura. Podemos definir la ventana de tiempo en la cual estas operaciones capturadas estarán «a la espera de decidir si pasan el filtro» con STALE_CAPTURE_POLICY_THRESHOLD. Dentro de esa ventana, podemos configurar con EXECUTION_COUNT la frecuencia mínima de repetición para ser persistida en el Query Store. También podemos controlar que solo aquellas que acumulan cierta cantidad de tiempo de compilación TOTAL_COMPILE_CPU_TIME_MS o cierto tiempo de ejecución TOTAL_EXECUTION_CPU_TIME_MS sean las que persistan finalmente.

 

Query Store en un escenario adverso

 

Sin embargo, pese a que parametricemos cuidadosamente Query Store, existen algunas situaciones especiales que pueden hacer que el impacto de Query Store sea «letal» para nuestro sistema. Para mostraros una de ellas vamos a crear una base de datos sobre la que lanzaremos operaciones adhoc. Las operaciones adhoc son aquellas donde básicamente «componemos» con una cadena la consulta, con sus parámetros, etc. y la «lanzamos» contra el servidor. Estas consultas tienen varios problemas, como el aumento del consumo de CPU por las compilaciones, el llenado de la caché con planes con 1 sola ejecución, etc. Para intentar minimizar este punto activaremos la opción «Optimize for adhoc workloads» ya que mitiga, en parte, este impacto.

EXEC sys.sp_configure N'optimize for ad hoc workloads', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

A continuación, crearemos una base de datos donde lanzaremos esta carga adhoc, con una sencilla tabla vacía:

use QueryStoreKiller
go
create table test (i int primary key, data varchar(100))
go
dbcc freeproccache

La carga a lanzar consistirá en simplemente crear una consulta adhoc nueva y ejecutarla dentro de un bucle infinito. Cada consulta será probablemente distinta a todas las anteriores debido a que estamos añadiendo un valor aleatorio:

while (1=1) begin
  set nocount on
  declare @sql varchar(max)= 'SELECT ''' + convert(varchar(max),rand()) + ''' from test'
  exec (@sql)
end

Para poder aumentar la carga y facilitar su ejecución, podemos lanzar esta operación desde sqlcmd ignorando los valores de retorno:

sqlcmd -S .\sql2017 -E -d QueryStoreKiller -Q "while (1=1) begin set nocount on; declare @sql varchar(max)= 'SELECT ''' + convert(varchar(max),rand()) + ''' from test';exec (@sql); end" > nul:

En cuanto lancemos este comando veremos que poco a poco se van insertando más y más planes hasta llegar a un valor aproximadamente máximo de 160 mil:

Query Store Killer

Una vez que llegamos a este punto máximo, cada nuevo plan que entre en la caché estará forzando a la salida de un plan previamente.  ¿Qué ocurrirá si estando en esta situación mantenemos la carga y habilitamos Query Store con la opción AUTO de captura de consultas?

Si analizamos las escrituras de Query Store así como el consumo de CPU veremos cómo partiendo de un consumo de CPU del 20% aproximadamente a las 18:37 vamos, siguiendo la pendiente marcada por las líneas azules, subiendo ese consumo y a los pocos minutos, sobre las 18:41 ya tendremos un consumo del 100% de la CPU:

Query Store Killer

Si mantenemos la carga un tiempo vamos viendo que llega un momento que el número de planes cacheados cae en picado, intenta remontar, vuelve a caer, etc. y llega un momento que ya no remonta:

Query Store Killer

En esta situación el rendimiento del servidor es muy pobre. También vemos que cuando tenemos esta situación anómala tenemos una gran cantidad de spinlocks de tipo SOS_CACHESTORE_CLOCK:

Query Store Killer

Este problema puede ser aliviado mediante un trace flag, el 174, que multiplica por 4 el número de buckets y por tanto aumenta por 4 la cantidad de planes que podemos cachear de 160,036 a 640,004:

https://support.microsoft.com/en-us/help/3026083/fix-sos-cachestore-spinlock-contention-on-ad-hoc-sql-server-plan-cache

Para ello necesitaremos reiniciar la instancia, cosa que por alguna razón tras llegar a esta situación donde subió tanto la CPU, parece retrasarse muchísimo, sin llegar finalizar. En ese momento vemos que procedimientos como sp_server_diagnostics, diseñados para funcionar en circunstancias difíciles, no devuelve ni resultados:

Tampoco obtenemos valores con el conocido procedimiento sp_whoisactive:

La caché se encuentra vacía:

Si analizamos los bloqueos existentes vemos un proceso 12 realizando un flush de Query Store bloqueado por un spid 5:

El cual a su vez está bloqueado en un comando SIGNAL HANDLER por el spid 42:

El cual parece que es una tarea de flush en background que hasta que no termine no permite el reinicio del motor. Aunque intentemos realizar un KILL del SPID, es ignorado:

La última espera de dicho proceso es WRITELOG y vemos que se mantiene cierta actividad de escritura sobre el fichero de datos, pero a una velocidad muy baja:

Si observamos desde Process Explorer de Sysinternals vemos que efectivamente el kpid/tid 24516 que es el que corresponde con esta sesión está consumiendo CPU y “haciendo algo” aparentemente:

La stack tampoco nos da una pista sobre lo que está realizando más allá de que está relacionado con obtener información de una TVF de QS:

Query Store Killer Database

En el log vemos que distintos SPID obtenían errores de lock request timeout durante este apagado fallido de la instancia:

Query Store Killer log request time exceeded

Si intentamos obtener por ejemplo un conteo de los planes almacenados por el Query Store, nos quedamos nosotros bloqueados con el mismo SPID 5 visto anteriormente:

select COUNT(*) from sys.query_store_query

Query Store Killer execute

Llegados a este punto y tras haber esperado ya un rato más que razonable, procedemos a matar el proceso de SQL Server de forma forzosa.

Una vez reiniciado el servicio, el límite de buckets se habrá visto incrementado al haber configurado el trace flag, por lo que vamos a probar a alcanzar unos 200 mil planes cacheados (simulando una situación en la cual con 200000 ya no tengamos más planes distintos) para ver qué comportamiento tiene el Query Store. Comenzaremos lanzando el sqlcmd y observando cómo va subiendo el número de planes en caché hasta llegar a ese valor:

Query Store Killer cache

Estando alrededor de 200 mil planes ahora el comportamiento parece ser normal, sin ralentizaciones extrañas. El número de backoffs es moderado en los spinlocks que antes destacaban:

Vamos a proceder a seguir creando más planes para intentar llegar al nuevo límite de 640 mil. A medida que van creciendo el número de planes vemos que vuelve a aumentar el consumo de CPU, con picos de uso de CPU por parte del Query Store:

Query Store Killer CPU Usage

Una vez llegamos al límite de nuevo, cerca de los 640mil, vemos que vuelven a aumentar los spins, los backoffs de los spinlocks y el consumo de CPU vuelve a ser muy elevado:

Llegados a este punto vemos que el consumo de CPU está descontrolado y si intentamos consultar el Query Store nos encontramos con los mismos bloqueos que en el caso anterior por parte de tareas de flush de Query Store:

Query Store Killer processes

Si intentamos encontrar algún parche relacionado con estos problemas de Query Store encontramos que el problema del apagado y el flush de Query Store puede desactivarse mediante el trace flag 7745 (https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-2017#Recovery):

Query Store Killer trace flags

También tenemos el trace flag 8032 que permite utilizar más cantidad de memoria para los planes de ejecución:

Query Store Killer trace flag

También encontramos referencias a problemas con spinlocks, similares al sufrido, solucionados en SQL Server 2016 SP2  CU2: https://support.microsoft.com/en-us/help/4340759/slow-performance-when-query-store-is-enabled-in-sql-server-2016 o a temas bastantes graves como llenados del log de transacciones que pueden ocurrir (https://support.microsoft.com/en-us/help/4511715/fix-filled-transaction-log-causes-outages-when-you-run-query-store-in) ocurriendo incluso hasta SQL 2017 CU16. Parece que realmente aún existen bastantes casos límite en los cuales esta característica de Query Store puede no funcionar de la forma esperada y aún necesita algo más de estabilización para poder habilitarla en el 100% de casos.

 

Conclusión y recomendaciones antes de habilitar Query Store

En conclusión, parece que en escenarios con peticiones adhoc masivas es realmente una mala idea “vivir al límite” en lo que respecta a la utilización de la caché de planes, saturando el número de buckets por defecto (~40mil = ~160mil planes) o el ampliado por el trace flag 174 (~160 mil = ~640 mil). Esto es especialmente “sangrante” si utilizamos Query Store, el cual sufrirá mucho para realizar el flush y se generará una cantidad de CPU extra (vía spinlocks) debido a la cantidad de planes existentes y el estar alcanzando el límite máximo de forma continua. Por tanto, recomendamos que antes de habilitar Query Store en una instancia se verifique cuántos planes tenemos en la caché y cómo de cerca estamos del límite. En el caso de estar cerca al límite del valor por defecto, 160 mil, recomendamos analizar los planes y tratar de recudir la cantidad de planes. Si ello no fuese posible, podemos probar a habilitar el trace flag 174 y ver si con ello si conseguimos estabilizar ese número de planes bien por debajo del nuevo límite de 640 mil. Incluso en ese caso a día de hoy consideraría algo arriesgado plantear el uso de Query Store ya que con volúmenes de planes tan elevados el riesgo que algo no esté «100% fino» puede traer consecuencias inesperadas de cara a la estabilidad/rendimiento de la instancia.

¿Necesitas optimizar el rendimiento de tus consultas? Si tienes algo de experiencia con SQL Server, conoces el modelo relacional y el lenguaje T-SQL, podrían interesarte nuestros próximos cursos presenciales de Planes de Ejecución, que se impartirán el próximo mes de diciembre. ¡Elige tu ciudad y reserva tu plaza para el curso Planes de Ejecución!

¡Y si quieres estar al tanto de nuevos posts y más info relacionada suscríbete a nuestra newsletter !   🙂

Rubén Garrigós

Mentor at SolidQ
I am an expert in high-availability enterprise solutions based on SQL Server design, tuning, and troubleshooting. Over the past fifteen years, I have worked with Microsoft data access technologies in leading companies around the world.

Nowadays, I am a Microsoft SQL Server and .NET applications architect with SolidQ. I am certified by Microsoft as a Solution Expert on the Microsoft Data Platform (MSCE: Data Platform) and as a Solution Expert on the Microsoft Private Cloud (MSCE: Private Cloud). As a Microsoft Certified Trainer (MCT), I have taught multiple official Microsoft courses as well as other courses specializing in SQL Server. I have also presented sessions at official events for various Microsoft technologies user groups.
Rubén Garrigós

Latest posts by Rubén Garrigós (see all)