SQL Server

Hoy te voy a hablar de una opción muy interesante que puede mejorar el rendimiento de tus consultas: la parametrización forzada. ¿Qué es esto y cómo funciona? Te lo explico en este artículo.

Como ya sabes, si has leído mis artículos anteriores sobre planes de ejecución, caché de planes y optimización para consultas ad hoc, SQL Server genera un plan de ejecución óptimo para cada consulta que recibe, basándose en las estadísticas, los índices y otros factores. Este plan se almacena en la caché de planes para reutilizarlo si se vuelve a ejecutar la misma consulta. Sin embargo, hay casos en los que la misma consulta puede tener diferentes valores de parámetros, lo que hace que el plan óptimo varíe. Por ejemplo, si tenemos una consulta que busca los clientes de una ciudad determinada, el plan óptimo puede cambiar según la ciudad que se especifique.

¿Qué es la parametrización?

En estos casos, SQL Server tiene dos opciones: generar un nuevo plan para cada valor de parámetro diferente (lo que se llama parametrización simple) o generar un plan genérico que sirva para todos los valores posibles (lo que se llama parametrización forzada). La primera opción tiene la ventaja de que el plan se adapta al valor concreto, pero tiene el inconveniente de que consume más recursos y puede llenar la caché de planes con planes similares. La segunda opción tiene la ventaja de que ahorra recursos y evita la fragmentación de la caché de planes, pero tiene el inconveniente de que el plan puede no ser el más adecuado para algunos valores.

¿Qué es la parametrización forzada?

La parametrización forzada hace que SQL Server trate todas las consultas como si fueran procedimientos almacenados con parámetros, y genera un plan genérico para cada consulta, independientemente del valor de los parámetros. Esto puede mejorar el rendimiento en algunos casos, pero también puede empeorarlo en otros. Por eso hay que tener cuidado al usar esta opción y hacer pruebas antes de aplicarla en un entorno productivo.

¿Es mejor la parametrización forzada?

Entonces, ¿cuál es la mejor opción? Depende. No hay una respuesta única, sino que depende del tipo de consultas, de la distribución de los datos, del tamaño de la base de datos y de otros factores. Lo ideal sería poder elegir la opción más conveniente para cada consulta, pero eso no es posible con la parametrización simple. Por eso existe la parametrización forzada, que nos permite activar esta opción a nivel de base de datos o a nivel de consulta individual, mediante una directiva o una sugerencia (HINT).

¿Cuándo activar la parametrización forzada?

Hemos visto que la parametrización forzada puede ayudarnos a reducir recursos al reutilizar los planes de ejecución de las consultas aunque cambien los parámetros. Imagina que tenemos una base de datos que da servicio a una aplicación web con miles o millones de usuarios lanzando la misma consulta, cada uno para sus datos. Esto nos va a generar miles de millones de planes de ejecución, en su mayoría iguales que van a consumir recursos para generarse teniendo ya otro igual guardado y que van a llenar la caché.

Sin embargo esto puede perjudicar mucho el rendimiento si la distribución de nuestros datos no es uniforme. Pero no todo está perdido, a partir de SQL 2019, con los joins dinámicos este problema se reduce en gran medida. 

¿Cómo activo la parametrización forzada?

Si llegado a este punto crees que esta solución es para ti, has detectado muchas recompilaciones que están llenando tu caché, has visto que tus consultas invierten mucho tiempo en compilar planes o simplemente quieres hacer pruebas vamos a ver como activarlo. Como hemos dicho además de con un HINT en cada consulta podemos activar la parametrización forzada por base de datos. Podremos hacerlo desde SSMS, en la pestaña opciones de las propiedades de nuestra base de datos o con el siguiente script:

USE [master]
GO
ALTER DATABASE [MiBaseDeDatos] SET PARAMETERIZATION FORCED
GO

Una vez habilitado, debería bajar el consumo de CPU de nuestro SQL, si, por el contrario, no es así deberás volver atrás y pensar en otras soluciones como la optimización para consultas Ad Hoc o analizar más a fondo con Query Store tus planes de ejecución.

Conclusión

En este artículo te he explicado qué es la parametrización forzada y cómo funciona. También te he mostrado las ventajas y los inconvenientes de esta opción y cómo puedes activarla o desactivarla según tus necesidades. Espero que te haya resultado útil y que lo pongas en práctica. Recuerda que puedes dejarme tus comentarios o preguntas al final del artículo, en Twitter o por mail. Hasta la próxima.

En este artículo vamos a hablar de una opción de SQL Server que puede mejorar el rendimiento de las consultas ad hoc, es decir, aquellas que se generan dinámicamente y no se repiten con frecuencia. Se trata de la opción OPTIMIZE FOR AD HOC WORKLOADS, que modifica el comportamiento del caché de planes de ejecución. El objetivo de esto es reducir el consumo de recursos de nuestro servidor como vamos a ver a continuación.

Antes de optimizar para Ad Hoc

Para poder saber si esta es una solución ideal para nosotros tenemos que tener claro una serie de conceptos previos. Estos son los planes de ejecución y la caché de planes. Nosotros los hemos tratado ya en los anteriores artículos en el blog pero aún así vamos a revisarlos.

Como sabemos, los planes de ejecución son la forma en la que el motor de base de datos ejecuta las consultas. Son una guía paso a paso dividida en componentes atómicos que dicen todo lo que va a necesitar hacer SQL Server para devolvernos los datos solicitados.

Como también sabemos, la caché de planes de ejecución es una estructura de memoria que almacena los planes generados por el optimizador para las consultas que se ejecutan en el servidor. Esto permite reutilizar los planes y ahorrar el coste de generarlos cada vez que se ejecuta una consulta. Sin embargo, la caché de planes tiene un tamaño limitado y puede llenarse rápidamente si se ejecutan muchas consultas diferentes. En ese caso, el servidor tiene que eliminar los planes más antiguos o menos usados para hacer espacio a los nuevos, lo que se conoce como recompilaciones. Las recompilaciones pueden afectar al rendimiento del servidor y consumir recursos de CPU y memoria.

Activar la optimización para consultas Ad Hoc

La opción OPTIMIZE FOR AD HOC WORKLOADS ayuda a reducir el impacto de las consultas ad hoc en la caché de planes. Lo que hace es almacenar solamente una parte del plan (un stub) la primera vez que se ejecuta una consulta ad hoc, en lugar del plan completo. Esto ocupa menos espacio en la caché y permite almacenar más planes. Si la consulta se vuelve a ejecutar, entonces se almacena el plan completo y se puede reutilizar. De esta forma, se evita llenar la caché con planes que solo se usan una vez y se favorece la reutilización de los planes que se repiten.

Para activar esta opción, se puede usar el siguiente comando a nivel de base de datos:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;

Esta opción solo afecta a la base de datos actual y se puede activar o desactivar en cualquier momento. Sin embargo, también existe la posibilidad de configurar esta opción a nivel de instancia, lo que afecta a todas las bases de datos del servidor. Para ello, se puede usar el siguiente comando:

sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;

Al activar esta opción a nivel de instancia, podemos aplicar los beneficios de la optimización para consultas ad hoc a todo el servidor, sin tener que hacerlo individualmente para cada base de datos. Esto puede ser útil cuando tenemos un entorno con muchas bases de datos y muchas consultas dinámicas.

¿Merece la pena activar la optimización para Ad Hoc?

Ahora bien, ¿cuándo debemos activar esta opción y cuándo no? La respuesta depende del tipo de carga de trabajo que tengamos en nuestro servidor. Si tenemos un entorno con pocas consultas ad hoc y muchas consultas repetitivas, entonces no es necesario activar esta opción, ya que no tendremos problemas con el caché de planes. Por el contrario, si tenemos un entorno con muchas consultas ad hoc y pocas consultas repetitivas, entonces sí es conveniente activar esta opción, ya que podremos ahorrar espacio en el caché y evitar recompilaciones innecesarias.

Pero ¿cómo saber el número de consultas ad hoc que tenemos en nuestro servidor? Una forma sencilla es usar la vista dinámica de gestión sys.dm_exec_cached_plans, que nos muestra los planes almacenados en el caché. Podemos filtrar por el tipo de objeto ‘Adhoc’ y contar cuánta cache ocupan estos planes. Por ejemplo:

WITH CTE_PLANS AS (
SELECT SUM(CASE
WHEN objtype = 'adhoc'
THEN convert(float,size_in_bytes)
ELSE 0 END) / 1048576.0 AdHoc_Plan_MB,
SUM(convert(float,size_in_bytes)) / 1048576.0 Total_Cache_MB
FROM sys.dm_exec_cached_plans)

SELECT AdHoc_Plan_MB, Total_Cache_MB, AdHoc_Plan_MB  *100.0 / Total_Cache_MB AS [AdHoc %]
FROM CTE_PLANS

Este comando nos devuelve el espacio consumido por planes ad hoc en la caché. Podemos compararlo con el espacio total de los planes en caché para ver qué porcentaje representan. Si es mayor al 25%, entonces podemos considerar activar la opción OPTIMIZE FOR AD HOC WORKLOADS. Tened en cuenta que los datos de la vista sys.dm_exec_cached_plans se reinician con cada reinicio del servicio de SQL Server por lo que antes de ejecutarlo debemos asegurarnos que el servidor lleva encendido bastante tiempo para tener datos fiables.

Conclusión

En este artículo hemos visto cómo la opción OPTIMIZE FOR AD HOC WORKLOADS puede mejorar el rendimiento de las consultas ad hoc en SQL Server. Como hemos comentado en la introducción también vamos a lograr reducir el consumo de recursos. Esta opción es especialmente útil cuando tenemos un entorno con muchas consultas dinámicas que no se repiten con frecuencia y que pueden llenar el caché de planes rápidamente. Al activar esta opción, podemos reducir el espacio ocupado por los planes en el caché (consumo de memoria RAM), evitar las recompilaciones innecesarias (Consumo de CPU) y favorecer la reutilización de los planes que se ejecutan más de una vez (Consumo de CPU).

Además, hemos visto cómo podemos configurar esta opción a nivel de base de datos o a nivel de instancia, según nuestras necesidades. También hemos visto cómo podemos saber el número de consultas ad hoc que tenemos en nuestro servidor y cuándo debemos activar o no esta opción. Esperamos que este artículo te haya sido útil y te invitamos a leer nuestros artículos anteriores sobre planes de ejecución y caché de planes.

En el artículo anterior, vimos cómo funcionan los planes de ejecución en SQL Server y cómo podemos analizarlos para optimizar nuestras consultas. Hoy vamos a profundizar en un aspecto muy importante de los planes de ejecución: la caché de planes.

La caché de planes es el espacio de memoria donde SQL Server almacena los planes de ejecución que ha generado previamente, para poder reutilizarlos si se vuelve a ejecutar la misma consulta. Esto tiene varias ventajas, como evitar el coste de generar un nuevo plan cada vez, y facilitar el uso de parámetros en las consultas.

¿Cómo ver los planes en caché?

Para ver los planes de ejecución almacenados en caché usaremos la vista de sistema sys.dm_exec_cached_plans y nos ayudaremos de las funciones sys.dm_exec_sql_text y sys.dm_exec_query_plan para ver el texto de la consulta y el plan de ejecución en formato XML. Está será la consulta que usaremos:

SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan
FROM sys.dm_exec_cached_plans AS cplan
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
ORDER BY cplan.usecounts DESC

¿Cómo se almacenan los planes en caché?

Los más avispados os habréis dado cuenta que el plan de ejecución se almacena usando el campo plan_handle, este corresponde a un hash de la consulta que estamos ejecutando. Esto quiere decir que cualquier mínima variación en el texto de la consulta generará un nuevo plan que se almacenará en la caché, aunque la consulta sea la misma y solo hayamos añadido espacios o tabuladores. Esto puede ser un problema y tenemos que tenerlo en cuenta ya que puede ser que almacenemos multitud de planes en caché para una misma consulta.

¿Qué pasa si la caché se llena y no hay espacio para nuevos planes?

Si la caché se llena, SQL Server empieza a eliminar los planes más antiguos o menos usados, siguiendo un algoritmo llamado LRU (least recently used). Esto puede provocar que se pierdan planes óptimos y que se tengan que generar nuevos planes cada vez, lo que afecta al rendimiento. Para evitarlo, podemos aumentar el tamaño de la caché, usando la opción ‘max server memory’, o podemos limpiar la caché manualmente, usando el comandos DBCC. Tenemos que evitar vaciar completamente la caché en nuestros entornos de producción pues todos las nuevas consultas tendrán que compilar de nuevo sus planes de ejecución consumiendo más recursos. También tendremos que tener en cuenta que al ser una caché en memoria no es persistente, se borrará completamente con cada reinicio del servicio.

¿Puede ser que los planes de la caché no sean los más adecuados para las consultas actuales?

Si los datos han sufrido una variación tal que los planes en caché no son los más adecuados para las consultas actuales, podemos tener un problema de recompilación o de parámetro erróneo. La recompilación ocurre cuando SQL Server detecta que el plan existente no es válido o no es óptimo para la consulta actual, y genera uno nuevo. Esto puede ser beneficioso en algunos casos, pero también puede consumir recursos y tiempo. El parámetro erróneo ocurre cuando SQL Server usa el mismo plan para consultas con diferentes valores de parámetros, lo que puede provocar que el plan no sea eficiente para algunos valores. Para evitar estos problemas, podemos usar la opción ‘optimize for’ o ‘recompile’ en las consultas, o podemos usar procedimientos almacenados con parámetros.

¿Qué pasa si hay demasiados planes similares que ocupan espacio innecesariamente?

Si hay demasiados planes similares que ocupan espacio innecesariamente, podemos tener un problema de crecimiento de la caché. Esto ocurre cuando SQL Server genera planes diferentes para consultas que solo cambian en los valores literales, lo que hace que la caché se llene de planes redundantes. Para evitarlo, podemos usar parámetros en las consultas, o podemos usar la opción ‘forced parameterization’ en las bases de datos.

Uso de procedimientos almacenados para optimizar la caché

Una forma de reducir el impacto de estos problemas es usar procedimientos almacenados en lugar de consultas ad hoc. Los procedimientos almacenados son bloques de código SQL que se almacenan en la base de datos y se pueden ejecutar con diferentes valores de parámetros. 

Los procedimientos almacenados tienen varias ventajas respecto a las consultas ad hoc:
– Solo generan un plan de ejecución por cada procedimiento almacenado, independientemente del valor de los parámetros. Esto evita la inflación de la caché y el parámetro erróneo.
– Solo recompilan el plan si hay cambios en el código del procedimiento almacenado o en la estructura de las tablas. Esto evita la recompilación innecesaria y mejora el rendimiento.
– Permiten encapsular la lógica de negocio y facilitar el mantenimiento y la seguridad del código.

Sin embargo, los procedimientos almacenados también tienen algunos inconvenientes:
– Pueden ser más difíciles de escribir y depurar que las consultas ad hoc.
– Pueden tener problemas de rendimiento si el plan generado no es óptimo para todos los valores posibles de los parámetros. Desde SQL 2019 esto se soluciona en gran medida gracias al uso del componente adaptive join al persistir los planes en caché. En versiones anteriores de SQL tendríamos que recurrir a usar la opción ‘recompile’ o ‘optimize for’ en nuestro procedimiento.
– Pueden tener problemas de seguridad si no se usan correctamente las opciones ‘execute as’ o ‘with encryption’.

Conclusión

La caché de planes es un elemento clave para el rendimiento de SQL Server, ya que permite reutilizar los planes de ejecución y ahorrar recursos. Pero también hay que tener cuidado con los posibles problemas que puede causar, como la pérdida de memoria, los planes ineficientes o la inconsistencia de datos. Para evitarlos, podemos usar herramientas y opciones de configuración que nos permitan controlar y optimizar la caché de planes. Además, podemos usar procedimientos almacenados en lugar de consultas ad hoc, para reducir el número de planes generados y mejorar la calidad del código.

Si quieres convertirte en un experto en planes de ejecución y mejorar el rendimiento de tus bases de datos, no te pierdas los siguientes artículos. Y si tienes alguna duda o comentario, no dudes en escribirme aquí abajo, en Twitter o por mail. Estaré encantado de ayudarte.

Hoy vamos a hablar de los planes de ejecución en SQL Server, uno de los temas más amplios y a la vez imprescindibles para entender el rendimiento de nuestras consultas. Entender los planes de ejecución es básico para analizar el rendimiento de las consultas y optimizarlas. Empecemos por el principio.

¿Qué son los planes de ejecución? 

Los planes de ejecución son una representación gráfica o textual de cómo SQL Server procesa una consulta. Cada consulta se divide en una serie de operadores lógicos y físicos que realizan diferentes tareas, como leer datos de una tabla, filtrar filas, ordenar resultados, etc. Además, los planes de ejecución nos muestran el orden y el coste de cada operador, así como las estadísticas de las filas procesadas y el uso de memoria y CPU. 

¿Para qué sirven los planes de ejecución? 

Los planes de ejecución nos ayudan a entender cómo SQL Server ejecuta una consulta y a identificar posibles problemas de rendimiento. Por ejemplo, podemos ver si una consulta usa índices adecuados, si hay operadores costosos o ineficientes, si hay estimaciones erróneas de cardinalidad, etc. Con esta información, podemos aplicar técnicas de optimización, como crear o modificar índices, reescribir la consulta, actualizar las estadísticas, etc. 

¿Cómo se obtienen los planes de ejecución? 

Hay varias formas de obtener los planes de ejecución en SQL Server. Una de las más sencillas es usar el Management Studio y activar la opción «Incluir plan de ejecución actual» o «Incluir plan de ejecución estimado» en el menú «Consulta». Esto nos mostrará el plan de ejecución junto con los resultados de la consulta. Otra forma es usar la instrucción SET SHOWPLAN_ON o SET SHOWPLAN_TEXT para obtener el plan de ejecución en formato textual. También podemos usar la función sys.dm_exec_query_plan para obtener el plan de ejecución en formato XML a partir del identificador de una consulta. Después, podemos usar esta web para ver gráficamente nuestros planes en XML.

¿Qué tipos de planes de ejecución hay?

Hay dos tipos principales de planes de ejecución: estimados y actuales. Los planes estimados se generan antes de ejecutar la consulta y se basan en las estadísticas disponibles y en el optimizador de consultas. Los planes actuales se generan después de ejecutar la consulta y reflejan lo que realmente ha ocurrido durante la ejecución. Nuestros planes estimados y actuales pueden ser diferentes si hay cambios en los datos, en las estadísticas o en el entorno. Por eso, es recomendable comparar ambos tipos de planes para detectar discrepancias y ajustar la consulta o las estadísticas si es necesario. 

¿Qué elementos componen un plan de ejecución? 

Un plan de ejecución se compone de varios elementos que representan los operadores lógicos y físicos que intervienen en la consulta. Cada elemento tiene un icono, un nombre y unas propiedades que describen su función y su coste. 

Algunos elementos comunes son: 
– Scan: lee todas las filas o páginas de una tabla o un índice. 
– Seek: busca una fila o un rango de filas en un índice usando una clave. 
– Join: combina filas de dos orígenes usando un criterio de unión. Hay varios tipos de join, como nested loops, merge o hash. 
– Aggregate: agrupa filas por una o más columnas y calcula funciones agregadas, como sum, count o avg. 
– Sort: ordena filas por una o más columnas. 
– Filter: elimina filas que no cumplen una condición. 
– Compute Scalar: calcula una expresión escalar, como una columna calculada o una función. 
– Stream Aggregate: agrupa filas por una o más columnas y calcula funciones agregadas usando un algoritmo eficiente que requiere que las filas estén ordenadas previamente. 
– Hash Match: realiza operaciones como join, aggregate o union usando una estructura hash para almacenar y buscar las filas. 
– Spool: almacena temporalmente las filas en el disco para reutilizarlas más adelante. 
– Top: devuelve un número fijo o porcentual de filas ordenadas por una o más columnas. 

Estos son solo algunos ejemplos, sin embargo, hay muchos más elementos que pueden aparecer en un plan de ejecución. Lo importante es entender qué hace cada elemento y cómo afecta al rendimiento de la consulta. Puedes encontrar la lista completa de operadores en la documentación oficial.

¿Cómo se interpreta un plan de ejecución? 

Para interpretar un plan de ejecución hay que tener en cuenta varios aspectos, como el orden, el coste, las estadísticas y las advertencias.
El orden indica la secuencia en la que se ejecutan los operadores. En un plan de ejecución gráfico, el orden se lee de derecha a izquierda y de arriba a abajo.
Por otro lado, el coste indica el porcentaje de recursos que consume cada operador respecto al total de la consulta y se puede ver en la barra de colores que acompaña a cada elemento. El coste es una estimación basada en las estadísticas y puede no reflejar el coste real.
Las estadísticas muestran información sobre las filas procesadas, el uso de memoria y CPU, el tiempo de ejecución, etc y se pueden ver en las propiedades de cada elemento o en el panel de detalles. Las estadísticas son datos reales que se obtienen al ejecutar la consulta.
Por último, las advertencias son mensajes que indican posibles problemas de rendimiento o de calidad de los datos. 

Advertencias

Las advertencias se pueden ver como símbolos de exclamación o de información en los elementos del plan. Como hemos visto, es importante que les prestemos atención ya que nos indican problemas en nuestras consultas.

Algunas advertencias comunes son: 
– Missing Index: indica que la consulta podría beneficiarse de un índice que no existe. 
– Sort Warnings: indica que la operación de ordenación ha usado más memoria de la disponible y ha tenido que escribir datos en el disco. 
– Hash Warnings: indica que la operación hash ha usado más memoria de la disponible y ha tenido que escribir datos en el disco. 
– Plan Affecting Convert: indica que hay una conversión implícita o explícita de tipos de datos que puede afectar al rendimiento o a la precisión de la consulta.
– SpillToTempDb: indica que una operación ha usado más memoria de la estimada y ha tenido que escribir datos en el disco. 

Estos son solo algunos ejemplos pero, hay muchas más advertencias que pueden aparecer en un plan de ejecución. Lo importante es prestar atención a las advertencias y corregir los problemas si es posible. 

Conclusión 

Los planes de ejecución son una herramienta imprescindible para cualquier DBA que quiera optimizar el rendimiento de las consultas en SQL Server. Los planes de ejecución nos permiten ver cómo SQL Server procesa una consulta y qué recursos consume cada operador. Con esta información, podemos identificar y resolver los problemas más comunes, como el uso inadecuado o la falta de índices, las estimaciones erróneas de cardinalidad, los operadores costosos o ineficientes, etc. Espero que este artículo os haya servido para introduciros en el mundo de los planes de ejecución y os anime a usarlos en vuestro trabajo diario. 

Si queréis profundizar más en este tema, os recomiendo los siguientes recursos:
– Los libros SQL Server Execution Plans y SQL Server Query Performance Tuning de Grant Fritchey
– Los post de Paul White en SQLPerformance.com
– Los cursos de Brent Ozar