Cómo buscar queries sin parametrizar en Query Store
Bienvenidos/as a un nuevo artículo en el blog de Aleson ITC. En el artículo de hoy te enseñaremos a buscar queries sin parametrizar en Query Store.
Hace poco tiempo tuvimos un problema en un servidor productivo de uno de nuestros clientes. Este problema estaba relacionado con Query Store y procesos Ad-Hoc. Es muy importante conocer que POR DEFECTO QUERY STORE NO FILTRA LOS PROCESOS ADHOC aunque tengamos la opción Optimize for Ad-Hoc workloads, a no ser que tengamos el modo de captura en Auto o en Custom.
¿Pero… cómo podemos saber si tenemos este tipo de cargas en nuestro entorno?
La verdad es que es bastante sencillo. Pero previamente debemos tener la configuración de captura en “all” en nuestro Query Store. Y de este modo estaremos capturando todas las consultas independientemente de la cantidad de ejecuciones, tiempo de uso de CPU, etc.
QUERY_CAPTURE_MODE = ALL
Para ver si las cargas de nuestro entorno son Ad-hoc utilizaremos los paneles de Query Store. En concreto el de TOP consultas con más ejecuciones. Para ello vamos a la base de datos que queremos analizar, desplegamos la opción de Query Store y abrimos las TOP Resource Consuming Queries.
En la gráfica seleccionamos Execution Count y de este modo se ordenará por el número de ejecuciones.
En nuestro caso se ve una diferencia entre ejecuciones bastante notoria:
Después de configurar el modo de captura en ALL, Query Store empezará a almacenar todo tipo de consultas. En la imagen se ven consultas con muchas ejecuciones y otras con muy pocas o incluso 1 ejecución.
En caso de tener muchas consultas con 1 ejecución se debería valorar la opción PARAMETERIZATION FORCED para que SQL intente parametrizar las consultas y de este modo reutilizar los planes de ejecución.
¿Es siempre una buena práctica aplicar esta opción de Query Store?
La respuesta es NO. Esta opción debería ser revisada y aplicable en los casos en los que la mayoría de las consultas sean Ad-hoc pero los planes de ejecución sean similares. El motivo es que cuando habilitas esta opción SQL empezará a generar planes de ejecución “no tan buenos” y un mayor uso de la caché de planes. Además, se ha de tener en cuenta que cuando aplicamos esta opción se borrarán todos los planes de ejecución guardados en cache.
En que nos deberíamos basar para aplicar esta opción de Query Store
Lo primero en lo que deberíamos basarnos es si nuestra base de datos tiene mucho código Ad-hoc.
Con la siguiente consulta podremos realizar el análisis:
--If the number of different queries is much lower than the number of queries, it means that our queries are Ad-hoc. SELECT COUNT(*) AS CountQueryRows FROM sys.query_store_query; SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRows FROM sys.query_store_query; --If the number of different plans is much lower than the number of plans, it means that our queries are Ad-hoc. SELECT COUNT(*) AS CountPlanRows FROM sys.query_store_plan; SELECT COUNT(DISTINCT query_plan_hash) AS CountDifferentPlanRows FROM sys.query_store_plan;
En caso de ser bastante diferente tendremos dos opciones.
Aplicar PARAMETERIZATION FORCED para toda la base de datos, esto hará que SQL intente parametrizar de forma automática todas las consultas lanzadas sobre esta base de datos.
--Apply forced parameterization for entire database ALTER DATABASE <database name> SET PARAMETERIZATION FORCED;
La otra opción es aplicar la opción sobre las consultas que hayamos detectado que son Ad-hoc y crear un plan guía para estas consultas. Pero este punto requiere mucha más explicación y daría para otra entrada de blog.
Una vez terminado podemos volver a cambiar la opción de captura de Query Store a como lo teníamos antes.
Conclusión
Podemos utilizar Query Store para detectar cargas de trabajo Ad-hoc y de este modo valorar si debemos aplicar la opción PARAMETERIZATION FORCED para toda la base de datos para reutilizar los planes de ejecución.
Gracias a esta herramienta podemos optimizar un servidor SQL Server en el que la carga de trabajo sea mayormente de consultas Ad-hoc.
Muchas gracias por la lectura, espero que te hayas podido resolver alguna duda y nos vemos en el siguiente blog!
¡No dejes que la gestión de datos de tu SQL Server ralentice tu progreso!
Database Administrator. Centrado en la monitorización y el rendimiento de las bases de datos.
