Rendimiento

Durante los anteriores post hemos comentado las recomendaciones de instalación de SQL para SharePoint y sus configuraciones recomendadas, como ha tenido muy buena acogida vamos a continuar con esta línea esta vez hablando de las buenas prácticas de SQL Server para BizTalk. 

Existen varios factores que tenemos que tener en cuenta en nuestro SQL Server para BizTalk. En este artículo vamos a intentar ver los más importantes no solo para configurar nuestro SQL sino para mantenerlo y solucionar los problemas que nos puedan surgir.

¿Qué es BizTalk?

Empecemos por el principio, BizTalk es un producto de Microsoft que facilita la comunicación entre sistemas heterogéneos, tanto dentro como fuera de la organización. BizTalk permite crear flujos de trabajo (orquestaciones) que definen la lógica y las reglas de negocio para procesar los mensajes que se intercambian entre los sistemas. Además, también ofrece herramientas para transformar, enrutar, monitorizar y auditar los mensajes, así como para gestionar las excepciones y errores que puedan ocurrir.

BizTalk se basa en una arquitectura distribuida que consta de varios componentes, entre los que se encuentra SQL Server. SQL Server es el encargado de almacenar la información relativa a las configuraciones, las instancias, las suscripciones, el seguimiento y el archivado de los mensajes. Por tanto, el rendimiento y la disponibilidad de SQL Server son fundamentales para el correcto funcionamiento de BizTalk.

Bases de datos de BizTalk

BizTalk usa varias bases de datos y es importante que las conozcamos pues no todas van a requerir las mismas medidas por nuestra parte. Las principales bases de datos que debemos conocer son: BizTalkMgmtDb (base de datos de administración), BizTalkMsgBoxDb (base de datos del buzón), BizTalkDTADb (base de datos del seguimiento), SSO (base de datos del servicio de configuración) y BAM (base de datos del análisis de negocio).

Usuarios de BizTalk

Además de las bases de datos BizTalk creará una serie de inicios de sesión y roles en nuestro SQL Server. Podemos verlo en la siguiente tabla que proporciona Microsoft (haz click sobre la imagen para ampliar).

Configuraciones de SQL Server ideales para BizTalk

Con estos conocimientos previos en mente ya podemos entrar en materia sobre la configuración de SQL Server específica para BizTalk. Vais a ver que algunas son similares a las que vimos para SharePoint. 

Configuraciones de SQL Server

Es importante que establezcamos el nivel de paralelismo a 1, ya que BizTalk genera muchas (muchísimas) consultas aunque muy ligeras. Con esto nos aseguraremos de poder dar salida a las máximas consultas posibles de manera simultánea.

En cuanto al Fill Factor, la documentación oficial no hace mención a un valor concreto sin embargo sí que nos habla de que la mayoría de los índices son clustered y con identificadores únicos basados en GUID por lo que no es recomendable poner un Fill Factor del 100%. Mi consejo es dejar el parámetro por defecto ya que no se especifica otra cosa. Más adelante volveremos al tema de los índices porque va a ser un dolor de cabeza.

Configuración de TempDB

Aunque este es un consejo general para todos los SQL Server, en el caso de BizTalk tiene mucha importancia porque ejecuta cantidades impensables de transacciones. Dividiremos nuestra base de datos en tantos ficheros como núcleos tenga nuestra CPU (hasta un máximo de 8) a fin de que varios hilos puedan acceder al recurso sin bloqueos de páginas. Todos los ficheros tendrán el mismo tamaño inicial y si usamos un SQL inferior a 2016 habilitaremos la traza  -T1117 en el arranque para que todos los ficheros crezcan simultáneamente. Sin embargo, debemos anticipar el tamaño de la TempDB y dimensionarla previamente para evitar el extra de tiempo del crecimiento de los archivos.

Tamaños de las bases de datos de BizTalk

Al igual que con la TempDB es importante que establezcamos un tamaño inicial y un crecimiento a los ficheros de las bases de datos de la aplicación para optimizar el rendimiento. En entornos pequeños y medianos podemos usar el siguiente script:

EXEC dbo.sp_helpdb BizTalkMsgBoxDb ALTER DATABASE BizTalkMsgBoxDb MODIFY FILE (NAME = BizTalkMsgBoxDb , FILENAME = '\BizTalkMsgBoxDb.mdf' , SIZE = 2GB , FILEGROWTH = 100MB) ALTER DATABASE BizTalkMsgBoxDb ADD FILE (NAME = BizTalkMsgBoxDb_2 , FILENAME = '\BizTalkMsgBoxDb_2.ndf' , SIZE = 2GB , FILEGROWTH = 100MB) ALTER DATABASE BizTalkMsgBoxDb ADD FILE (NAME = BizTalkMsgBoxDb_3 , FILENAME = '\BizTalkMsgBoxDb_3.ndf' , SIZE = 2GB , FILEGROWTH = 100MB) ALTER DATABASE BizTalkMsgBoxDb ADD FILE (NAME = BizTalkMsgBoxDb_4 , FILENAME = '\BizTalkMsgBoxDb_4.ndf' , SIZE = 2GB , FILEGROWTH = 100MB) ALTER DATABASE BizTalkMsgBoxDb ADD FILE (NAME = BizTalkMsgBoxDb_5 , FILENAME = '\BizTalkMsgBoxDb_5.ndf' , SIZE = 2GB , FILEGROWTH = 100MB) ALTER DATABASE BizTalkMsgBoxDb ADD FILE (NAME = BizTalkMsgBoxDb_6 , FILENAME = '\BizTalkMsgBoxDb_6.ndf' , SIZE = 2GB , FILEGROWTH = 100MB) ALTER DATABASE BizTalkMsgBoxDb ADD FILE (NAME = BizTalkMsgBoxDb_7 , FILENAME = '\BizTalkMsgBoxDb_7.ndf' , SIZE = 2GB , FILEGROWTH = 100MB) ALTER DATABASE BizTalkMsgBoxDb ADD FILE (NAME = BizTalkMsgBoxDb_8 , FILENAME = '\BizTalkMsgBoxDb_8.ndf' , SIZE = 2GB , FILEGROWTH = 100MB) GO ALTER DATABASE BizTalkMsgBoxDb MODIFY FILE (NAME = BizTalkMsgBoxDb_log , FILENAME = '\BizTalkMsgBoxDb_log.LDF', SIZE = 20GB , FILEGROWTH = 100MB) GO 

Utilizaremos estos parámetros para entornos standalone y pequeños. En un entorno BizTalk de alto rendimiento, deberemos considerar dividir la BizTalkMsgBoxDb en 8 archivos de datos, cada uno con un tamaño de archivo de 2 GB con 100 MB de crecimiento y un archivo de registro de 20 GB con 100 MB de crecimiento. Debido a que las bases de datos BizTalk MessageBox son las más activas, colocaremos los archivos de datos y los archivos de log de transacciones en unidades dedicadas para reducir la probabilidad de problemas de E/S de disco, como vamos a ver ahora.

Configuración de discos duros de SQL Server para BizTalk

Entramos, sin duda, en el apartado más importante para el futuro rendimiento de nuestras aplicaciones BizTalk, de esto va a depender que nuestra aplicación siga funcionando como al principio cuando empiece a tener un tamaño considerable. Por eso es el apartado en el que más nos vamos a extender hoy.

Por defecto, BizTalk coloca la base de datos MessageBox en un único archivo en el grupo de archivos predeterminado. Y por defecto, los datos y logs de transacciones para todas las bases de datos también se colocan en la misma unidad y ruta. Esto se hace para poder funcionar en sistemas con un único disco. Sin embargo, esta configuración de un único archivo, grupo de archivos y disco no es óptima en un entorno de producción. Para un rendimiento óptimo, los archivos de datos y los archivos de log deben colocarse en discos separados.

Otra cosa que debemos tener en cuenta, especialmente para entornos de alto volumen, y de nuevo, porque las bases de datos MessageBox y Tracking son las más activas, es colocar los archivos de datos y los logs de transacciones de estas dos bases de datos en unidades dedicadas para reducir la probabilidad de problemas de E/S de disco.

Por tanto para un rendimiento óptimo nuestro SQL Server deberá tener por lo menos 7 discos duros de datos:

1. Archivo(s) de datos MessageBox
2. Archivo(s) de log de transacciones de MessageBox
3. Archivo(s) de datos de BizTalk Tracking (DTA)
4. Archivo(s) de log de transacciones de BizTalk Tracking (DTA)
5. Otros archivos de datos de bases de datos BizTalk
6. Otros archivos de registro de transacciones de bases de datos BizTalk
7. TEMPDB

Buscando la perfección en el reparto de discos

Esta técnica descrita anteriormente será familiar para cualquiera que haya creado una instalación grande y multiservidor de BizTalk Server. Además, es preferible que la base de datos de seguimiento (DTA) se sitúe en un servidor separado (o clúster en un entorno de alta disponibilidad) del buzón de mensajes.
Otra de las mejores optimizaciones de rendimiento que podemos hacer es repartir las tablas de las bases de datos de BizTalk entre varios grupos de archivos. Cada grupo de archivos añade un hilo de E/S que, junto con el archivo de datos en un LUN separado, mejorará enormemente el rendimiento (con ganancias del 100-1000%, dependiendo de las características de carga del sistema). No sólo moveremos los datos de la tabla a grupos de archivos separados, sino también los datos LOB y los índices no agrupados. Esto maximiza el paralelismo entre consultas y otras operaciones de base de datos. Haremos esto si nuestra base de datos MessageBox es cercana o mayor a 15 GB. 

En entornos pequeños o medianos sin una gran cantidad de mensajes, no es necesario tener tanta separación en discos. Sin embargo, es muy recomendable que al menos tengamos unidades dedicadas para los archivos de datos y de logs (para permitir que la actividad de E/S se produzca al mismo tiempo para los archivos de datos y de log) y una unidad dedicada para TEMPDB.

Índices y estadísticas en las bases de datos de BizTalk

BizTalk hace su propia gestión de los índices y estadísticas de las bases de datos por lo que tendremos que asegurarnos de que todas las bases de datos de la aplicación tengan tanto la creación como la actualización automática de estadísticas deshabilitadas.

El tema de los índices es más delicado, ya que un mal mantenimiento nos llevará a un escenario insostenible de continuos bloqueos que harán perder la paciencia a los usuarios. Para complicarnos aún más las cosas BizTalk no admite un mantenimiento estándar de índices, es decir no podremos programar nuestros habituales planes de mantenimiento nativos o de Ola Hallengren, o bloquearemos por completo la aplicación.

El único método admitido para mantenimiento de índices en la base de datos BizTalkMsgBoxDb es ejecutar el procedimiento almacenado bts_RebuildIndexes. En BizTalk Server 2006 y versiones posteriores, tenemos también el procedimiento almacenado dtasp_RebuildIndexes para en la base de datos BizTalkDTADb. Por si te parece poco, estos procedimientos solo se pueden ejecutar cuando la aplicación no tiene carga, es decir cuando no está procesando mensajes por lo que es vital acordar con el administrador de la aplicación una ventana de mantenimiento para ejecutarlo. Para poder ejecutar el mantenimiento de índices todas las instancias de BizTalk y el Agente de SQL deben estar detenidos.

Niveles de aislamiento

El valor predeterminado de Transaction Isolation Level es Serializable para el adaptador de BizTalk WCF-SQL tanto para las operaciones entrantes y salientes. Esto provoca gran cantidad de bloqueos, que aunque no deberían ser un problema en entornos pequeños y con poca carga de trabajo puede que nos de problemas en cuanto el volumen de peticiones se incremente. Por suerte el administrador de BizTalk podrá cambiar esta configuración. Revisa nuestro articulo de niveles de aislamiento para entender mejor este tema.

Agente de SQL Server

Por último, pero no menos importante, como se suele decir, tenemos que prestar atención a los jobs de SQL Server que genera BizTalk. Dependiendo de la versión de BizTalk se generarán 12 o 13 jobs, la mayoría realizan procesos internos de la aplicación que, como DBAs, no hace falta conocer. Sin embargo es muy importante que si atendamos a dos de ellos que se llaman «Backup BizTalk Server» y  «DTA Purge and Archive». Estos dos jobs son críticos y por defecto nos vienen programados por lo que tendremos que hacerlo nosotros. El primero de ellos, como habréis adivinado por el nombre, se encarga de las copias de seguridad de las bases de datos siguiendo las buenas prácticas de Microsoft para ello. El segundo, es el encargado de purgar y archivar la información de la base de datos de seguimiento y, eso es lo principal para no tener degradaciones del rendimiento.

Conclusión

En este artículo he compartido con vosotros algunas buenas prácticas de configuración de SQL Server para BizTalk, que os pueden ayudar a mejorar el rendimiento, la escalabilidad y la seguridad de vuestra plataforma de integración. Espero que os hayan resultado útiles y que las pongáis en práctica. Si tenéis alguna duda o comentario, podéis dejarlo abajo, en mi Twitter o por mail. 

PD: Si esto os ha parecido poco podéis ampliar toda esta información el la guía de documentación de Microsoft aquí.

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