Roberto Carrancio

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.