Rendimiento

Dic

2024

Los peligros del permiso SHOWPLAN

Cuando administramos bases de datos en SQL Server, no debemos perder el foco en asegurar la confidencialidad, integridad y disponibilidad de los datos. Sin embargo, a veces, centrados en el rendimiento, subestimamos cómo ciertos permisos pueden abrir brechas de seguridad inesperadas. Uno de estos permisos es SHOWPLAN. ¿Alguna vez le has dado permisos de SHOWPLAN en producción a un desarrollador? Puede parecer inofensivo y además es una herramienta poderosa y útil para que los desarrolladores puedan optimizar sus consultas, pero no es tan sencillo. ¿Sabías que puede convertirse en un riesgo significativo si se concede de manera inapropiada? A continuación, te cuento en profundidad qué implica este permiso, sus ventajas legítimas y los riesgos que puede acarrear cuando se utiliza fuera de contexto.

¿Qué es el permiso SHOWPLAN?

El permiso SHOWPLAN en SQL Server permite a los usuarios generar y visualizar los planes de ejecución de las consultas. Esto incluye detalles sobre cómo el motor de base de datos planea ejecutar una consulta SQL, mostrando operaciones como búsquedas en índices, uniones, escaneos de tablas, y predicados de filtro.

Existen dos variantes principales de este permiso, SHOWPLAN_XML y SHOWPLAN_TEXT, que permiten generar una representación del plan de ejecución en formato XML o texto, respectivamente. Además, SHOWPLAN_ALL muestra información completa sobre el plan de ejecución.

Cuando se activa este permiso, SQL Server no ejecuta realmente la consulta, sino que devuelve un «plan estimado», describiendo cómo se procesaría la consulta en términos de operaciones lógicas y físicas.

Ventajas del permiso SHOWPLAN

El permiso SHOWPLAN tiene aplicaciones legítimas y valiosas en el desarrollo y mantenimiento de bases de datos. Entre sus principales beneficios, podríamos destacar los siguientes:

Identificación de problemas de rendimiento

La principal utilidad del permiso SHOWPLAN es ayudar a identificar cuellos de botella en el rendimiento de las consultas. Tanto los administradores de bases de datos (DBAs) y desarrolladores podemos usar esta información para optimizar consultas, ajustar índices o rediseñar tablas. Por ejemplo, un plan de ejecución puede revelar que una consulta está realizando un «escaneo completo de tabla» (Table Scan) en lugar de usar un índice, lo que indica la necesidad de crear un índice o ajustar el predicado.

Análisis predictivo sin ejecutar consultas

Con SHOWPLAN, es posible analizar cómo SQL Server ejecutaría una consulta sin necesidad de ejecutarla realmente. Esto es crucial cuando se trabaja con consultas que afectan grandes volúmenes de datos, ya que permite evaluar su impacto sin riesgo de sobrecargar el sistema.

Comparación de estrategias de consulta

Los desarrolladores pueden usar SHOWPLAN para comparar alternativas de diseño de consultas. Por ejemplo, al evaluar si una subconsulta correlacionada es más eficiente que una JOIN, los planes de ejecución ayudan a elegir la mejor estrategia.

Herramienta educativa y formativa

En entornos de desarrollo, SHOWPLAN también se usa para enseñar a nuevos DBAs y desarrolladores cómo optimizar consultas y comprender el comportamiento interno de SQL Server. Es una herramienta ideal para profundizar en cómo el optimizador toma decisiones.

Riesgos asociados al permiso SHOWPLAN

Aunque las ventajas de SHOWPLAN son innegables en manos de DBAs y desarrolladores, los riesgos emergen cuando este permiso se concede a usuarios fuera de estos roles o sin las medidas de seguridad adecuadas.

Exposición de datos sensibles

Una característica poco conocida de SHOWPLAN es que los planes de ejecución pueden revelar los valores exactos de las variables o parámetros utilizados en las consultas. Aunque un usuario no tenga acceso directo a las tablas implicadas, podría deducir información confidencial a través del análisis del plan.

Por ejemplo si ves esta consulta en ejecución:

SELECT * FROM Clientes WHERE NumeroTarjetaCredito = @Numero;

El plan de ejecución en XML mostrará que @Numero contiene un valor como 1234-5678-9012-3456, exponiendo información sensible.

Mapeo de la estructura de la base de datos

Los planes de ejecución muestran detalles como nombres de índices, columnas y relaciones entre tablas. Un usuario malintencionado podría utilizar esta información para mapear la estructura de la base de datos y diseñar ataques dirigidos, como inyecciones SQL más efectivas o extracción de datos.

Imagina que alguien, gracias a técnicas de SQLi es capaz de vulnerar la seguridad de tu app y llegar a la base de datos. Si el usuario de la aplicación tiene más permisos de los estrictamente necesarios el daño puede ser gravísimo.

Ingeniería inversa de estadísticas

Los planes de ejecución contienen estadísticas sobre cardinalidad y distribución de datos, lo que permite deducir patrones sensibles, como la cantidad de registros que cumplen ciertas condiciones. Esto podría facilitar ataques de análisis estadístico. Sumale a esto los anteriores peligros y tendrás el cóctel perfecto para una fuga inesperada de datos.

Uso en ataques de denegación de servicio (DoS)

Por último, pero no menos importante, un usuario malintencionado con acceso a SHOWPLAN podría diseñar consultas costosas que generen planes de ejecución extremadamente complejos, agotando recursos del servidor.

Mejores prácticas para mitigar los riesgos de SHOWPLAN

Para evitar que el permiso SHOWPLAN se convierta en un vector de ataque, es esencial adoptar un enfoque de seguridad robusto que contemple varias estrategias complementarias. En primer lugar, es imprescindible seguir el principio de menor privilegio, limitando el uso de este permiso exclusivamente a administradores de bases de datos (DBAs) y algunos desarrolladores, y exclusivamente en entornos de desarrollo o pruebas. En producción, el acceso debe ser excepcional y estrictamente controlado.

Cuando se trabaja con bases de datos reales en entornos de prueba, la anonimización o el enmascaramiento de datos son medidas clave para evitar la exposición accidental de información sensible. Esta práctica protege los datos al tiempo que permite un análisis seguro del rendimiento de las consultas.

Además, las auditorías regulares resultan fundamentales para identificar usuarios que dispongan de permisos sensibles como SHOWPLAN. Herramientas nativas como Extended Events en SQL Server pueden facilitar el rastreo y análisis del uso de este permiso, asegurando un monitoreo constante de posibles accesos indebidos.

Por último, la formación continua de los equipos de desarrollo y administración es un pilar esencial. Educar a los responsables sobre los riesgos asociados al uso indebido de permisos sensibles y las mejores prácticas para gestionarlos garantiza que las concesiones innecesarias puedan evitarse de manera efectiva, fortaleciendo así la postura de seguridad general del entorno.

Conclusión

El permiso SHOWPLAN es una gran herramienta para la optimización de consultas y el análisis del rendimiento en SQL Server, pero su mal uso puede comprometer la seguridad de los datos. Concederlo sin restricciones puede exponer datos sensibles, facilitar ataques y comprometer la integridad del sistema.

Para evitar estos riesgos, es esencial adoptar un enfoque de seguridad proactiva, restringiendo su uso a roles específicos, monitoreando su aplicación y utilizando alternativas seguras cuando sea posible. De esta manera, podemos aprovechar las ventajas de SHOWPLAN sin comprometer la seguridad de nuestras bases de datos. Como siempre, en SQL Server, un enfoque basado en “confianza cero» (Zero Trust) es la mejor política.

Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Nov

2024

Puertas de Enlace Power BI y Fabric

En el análisis de datos con Power BI o Fabric, las puertas de enlace juegan un papel fundamental. Son el puente que nos permite conectar de forma segura y eficiente los servicios en la nube de Microsoft con los datos locales de nuestra organización. Estas herramientas facilitan la comunicación entre los sistemas internos y Power BI, garantizando que los datos estén siempre actualizados y disponibles para análisis en tiempo real. Sin embargo, las puertas de enlace no solo son esenciales para Power BI; también pueden integrarse con Microsoft Fabric, potenciando los flujos de trabajo avanzados y las capacidades de procesamiento en paralelo. Este artículo se inspira en los artículos de Toni Jurado en explorandodatos.com, quien ha profundizado en las características esenciales y las mejores prácticas de las puertas de enlace en Power BI, y busca añadir una perspectiva integral sobre su uso y consideraciones técnicas.

¿Qué es una puerta de enlace de Power BI y Microsoft Fabric?

Las puertas de enlace de Power BI y Fabric son un componente de software (programa) que se instala en nuestros servidores locales y que permiten a los servicios en la nube de Microsoft conectarse de manera segura a los datos almacenados en nuestras instalaciones on-premises. Esto es especialmente valioso para las empresas que, por razones de seguridad o privacidad, no desean o no pueden migrar sus datos sensibles a la nube. La puerta de enlace actúa como un intermediario, procesando solicitudes de datos y cifrando la comunicación entre la fuente local y el servicio en la nube, asegurando así la privacidad y protección de la información.

En la práctica, la puerta de enlace permite realizar transformaciones y filtrados de datos en el entorno local, reduciendo el volumen de datos que se envía a la nube. Esto optimiza el uso del ancho de banda de la red y mejora el rendimiento de las consultas en Power BI o Fabric. Sin embargo, este procesamiento requiere recursos en el servidor donde está instalada la puerta de enlace, lo que implica ciertas consideraciones técnicas a la hora de configurarla y mantenerla.

Tipos de puertas de enlace: Personal y Estándar

Existen dos tipos de puertas de enlace y, aunque las dos opciones sirven para enviar nuestros datos de los servidores locales a la nube de Power BI o de Fabric, tienen sus diferencias significativas. Tendremos que elegir una u otra en función de nuestras necesidades.

Puerta de enlace personal

Esta versión está diseñada para un único usuario y es ideal para casos de uso individual o pruebas rápidas. Permite conectar Power BI con fuentes de datos locales, pero está limitada a un solo usuario y no es adecuada para entornos de producción. No es compatible con los servicios de Microsoft Fabric.

Puerta de enlace estándar

Pensada para su uso en entornos de producción, esta puerta de enlace puede ser utilizada por múltiples usuarios y soporta una configuración avanzada para integrarse, además de con Power BI, con otros servicios, como Power Automate, Power Apps y Microsoft Fabric. Es ideal para empresas que manejan grandes volúmenes de datos y requieren análisis en tiempo real.

Es importante tener en cuenta que, aunque las puertas de enlace personales son útiles para usuarios individuales, en entornos de producción o con Microsoft Fabric se recomienda siempre utilizar las puertas de enlace estándar. Esto garantiza el soporte multiusuario, configuraciones avanzadas de balanceo de carga y alta disponibilidad, que son esenciales para entornos de análisis de datos complejos.

Uso de las puertas de enlace: Orígenes de datos compatibles

Una de las grandes ventajas de las puertas de enlace en Power BI y Fabric es su compatibilidad con una amplia variedad de orígenes de datos locales. Esto permite a las organizaciones integrar en sus análisis en la nube prácticamente cualquier sistema interno sin necesidad de migrar los datos a la nube, y manteniendo el control y la seguridad de la información. A continuación, os nombro algunos de los principales orígenes de datos locales que pueden conectarse a través de las puertas de enlace estándar:

Bases de datos relacionales

Entre los sistemas compatibles podemos encontrar SQL Server, Oracle Database, MySQL, PostgreSQL, DB2 y Teradata. Estos sistemas tienen la capacidad de plegar las consultas y nos permiten la conexión en modo DirectQuery, que consulta los datos en tiempo real, o en modo de importación, ideal para escenarios de carga periódica. Ambos modos facilitan el análisis sin necesidad de replicar todos los datos en la nube.

Fuentes de datos de archivos

Las puertas de enlace permiten el acceso a archivos de Excel, CSV, archivos de texto, XML y carpetas compartidas en red. Los datos de estos archivos pueden ser actualizados automáticamente para reflejar la última versión sin intervención manual. Esto es especialmente útil en entornos donde los datos se almacenan en archivos distribuidos en múltiples ubicaciones.

Sistemas de almacenamiento en la nube privada

Las puertas de enlace pueden conectar servicios de almacenamiento en la nube privada alojados dentro del entorno corporativo, incluyendo SAP HANA y SAP BW. Estos sistemas se encuentran en muchas grandes empresas, y la puerta de enlace asegura que los datos permanezcan en la red local mientras están disponibles para análisis en Power BI y Fabric.

Sistemas de información y aplicaciones empresariales

Power BI y Fabric permiten conectarse a sistemas como Microsoft Dynamics 365 on-premises y servicios OData, incluyendo SharePoint on-premises y otros servicios web empresariales. Estas integraciones son clave para empresas que utilizan sistemas empresariales complejos y desean incorporar datos de múltiples fuentes sin comprometer la seguridad.

Sistemas de mensajería y API REST

Las puertas de enlace también son compatibles con servicios que utilizan API REST, lo que permite integrarse con aplicaciones internas que exponen datos mediante servicios web. Esta opción ofrece flexibilidad para conectar sistemas personalizados de la organización.

Configuración de puertas de enlace en entornos de producción

La instalación y configuración de una puerta de enlace requiere ciertos conocimientos técnicos, especialmente en entornos de producción. Para instalar una puerta de enlace, debemos contar con un equipo (idealmente un servidor) que cumpla con los requisitos técnicos de Microsoft y que esté optimizado para manejar el flujo de datos y la carga de procesamiento.

Una vez instalada, la configuración de la puerta de enlace se gestiona desde el portal de administración de Power BI, donde podemos definir permisos de acceso a las fuentes de datos, configurar la autenticación de usuarios y programar las actualizaciones de datos. Las opciones de administración permiten a sus responsables controlar el acceso de los usuarios y gestionar los permisos para asegurar que solo personas autorizadas puedan acceder a los datos a través de la puerta de enlace.

Buenas prácticas para la instalación de puertas de enlace

Existen ciertas buenas prácticas recomendadas para optimizar el rendimiento y la seguridad de las puertas de enlace en entornos corporativos. A continuación, os comparto algunas consideraciones clave a tener en cuenta al instalar una puerta de enlace:

Separación de la puerta de enlace y el servidor de bases de datos

Aunque pueda parecer tentador, no es recomendable instalar la puerta de enlace en el mismo servidor donde reside la base de datos, ya que ambos servicios requieren recursos significativos. Si comparten el mismo servidor, podrían surgir conflictos de recursos en momentos de alta demanda, afectando la estabilidad y el rendimiento de ambos servicios. En su lugar, se recomienda instalar la puerta de enlace en un servidor dedicado que cuente con recursos suficientes para manejar la carga de trabajo de la puerta de enlace.

Ubicación en la misma subred

Para minimizar la latencia y optimizar el rendimiento de las transferencias de datos, es ideal que la puerta de enlace y el servidor de origen de los datos se encuentren en la misma subred. Esto permite una comunicación más rápida y reduce el riesgo de pérdida de paquetes de datos. Cuando la puerta de enlace y la base de datos están en diferentes subredes o ubicaciones, se puede experimentar una mayor latencia, lo cual afecta la eficiencia en el análisis de datos.

Ahora una recomendación personal. El tráfico de datos generado entre la puerta de enlace y un servidor de base de datos puede ser considerable lo que puede afectar al rendimiento de la red y verse afectado por otros usuarios de la red. Para estos casos la solución ideal es una subred aislada entre el servidor de bases de datos y la puerta de enlace. De este modo, nuestro servidor de bases de datos tendrá dos tarjetas de red, cada una en una red, una para el tráfico de los usuarios y otra exclusiva de comunicación con la puerta de enlace. Tampoco es raro encontrarnos en escenarios empresariales con una subred entre servidores aislada de los usuarios. En estos casos, deberéis valorar con los técnicos de redes si la puerta de enlace debe ir en esta red o en una dedicada.

Además, en entornos corporativos complejos, se recomienda aislar el tráfico de la puerta de enlace y la base de datos mediante VLANs independientes. Esta práctica no solo permite gestionar mejor el ancho de banda como acabamos de comentar sino que mejora la seguridad. La segmentación es particularmente importante cuando se trabaja con datos sensibles, ya que ayuda a cumplir con las normativas de privacidad y seguridad.

Balanceo de carga y alta disponibilidad

En organizaciones con grandes volúmenes de datos o con una alta frecuencia de actualizaciones, es recomendable implementar un clúster de puertas de enlace. Esto permite distribuir la carga de trabajo entre varias instancias, garantizando alta disponibilidad. En caso de que una puerta de enlace falle, otra puede asumir su carga, evitando interrupciones. El balanceo de carga es esencial cuando se trabaja con Microsoft Fabric, especialmente cuando usamos procesamiento de datos en tiempo real que requiere una infraestructura robusta y estable. Estos cluster de puertas de enlace solo los podremos hacer con puertas de enlace estándar, las personales no lo permiten.

Monitorización de recursos

Como la puerta de enlace realiza procesamiento de datos local, es importante monitorizar regularmente los recursos del servidor, como CPU, memoria y uso de red. Si detectamos un uso elevado de recursos, podríamos necesitar escalar la capacidad del servidor o migrar la puerta de enlace a un servidor con mayor capacidad para evitar problemas de rendimiento.

Consideraciones especiales para Microsoft Fabric

Con la integración de Microsoft Fabric, las puertas de enlace han ampliado su funcionalidad, permitiendo manejar flujos de datos más complejos y ejecutar análisis en tiempo real. En entornos de Fabric, es fundamental que la puerta de enlace tenga acceso a suficiente capacidad de procesamiento para soportar las demandas adicionales. Esto incluye configuraciones avanzadas para equilibrar la carga de procesamiento y distribuir las solicitudes de datos de forma eficiente. Además, como ya os he mencionado solo las puertas de enlace estándar son compatibles con Fabric, no vamos a poder usar una puerta de enlace personal.

Conclusión

Las puertas de enlace de Power BI y Microsoft Fabric son herramientas indispensables para las organizaciones que necesitan conectar sus datos locales con el servicio en la nube de forma segura y eficiente. Al permitir el procesamiento local de datos, las puertas de enlace optimizan el uso del ancho de banda y aseguran que solo los datos necesarios se transmiten a la nube, reduciendo la carga en la red y mejorando el rendimiento.

Este artículo y las ideas expuestas han sido inspirados en los artículos de Toni Jurado en explorandodatos.com, quien ha compartido valiosos conocimientos sobre el uso de puertas de enlace en Power BI. También os recomiendo el video sobre este tema del Power Quiz presentado por Ricardo Rincón y Diego jurado con Toni Jurado como quiz maker.

Espero que esta guía contribuya a un entendimiento más profundo de estas herramientas y a su implementación eficaz en entornos empresariales. Si tenéis alguna duda o sugerencia, podéis dejarla en Twitter, por mail o dejarnos un mensaje en los comentarios. Y recuerda que también tenemos un grupo de Telegram y un canal de YouTube a los que te puede unir. ¡Hasta la próxima!

Nov

2024

Obtener el plan de ejecución real de una consulta sin ejecutarla

La semana pasada hablamos del impacto de Query Store en el rendimiento de nuestros servidores. Veíamos que por norma general es prácticamente nulo pero, en casos concretos, con muchas consultas Ad Hoc podría llegar a ser un problema. Si está en uno de esos rarísimos casos donde los beneficios de Query Store no compensan su impacto en el rendimiento aun hay algo que vas a poder hacer para obtener el plan real de una consulta sin tener que llegar a ejecutarla.

Obtener el plan real es importante para medir el rendimiento de nuestras consultas y localizar problemas de rendimiento pero, el problema es que para ello necesitamos ejecutar la consulta y esta puede ser costosa. Por suerte, desde SQL Server 2019 tenemos una alternativa. Para poder hacer uso de ella es necesario configurar adecuadamente el servidor para que almacene el plan real en lugar de solo el estimado. En este artículo profundizaremos en cómo lograrlo, lo que facilita el acceso a estos planes sin impacto en el sistema productivo.

Introducción a los Planes de Ejecución en SQL Server

Como ya hemos visto en varias ocasiones en el blog, un plan de ejecución representa la secuencia de operaciones que SQL Server realiza para ejecutar una consulta. Estos planes se pueden clasificar en dos tipos:

Estimados: SQL Server los calcula antes de ejecutar la consulta, basándose en estadísticas de los índices y distribuciones de datos.
Reales: Se generan después de ejecutar la consulta, reflejando los datos y operaciones reales ejecutadas, incluyendo métricas de tiempo y consumo de recursos.

La posibilidad de acceder a un plan de ejecución real sin re-ejecutar la consulta es especialmente valiosa en entornos de producción. SQL Server 2019 introdujo una funcionalidad para almacenar estos planes reales en caché, permitiendo su análisis sin la sobrecarga de una nueva ejecución.

¿Por qué vas a querer almacenar el plan real de las consultas?

Guardar el plan de ejecución real en lugar del estimado nos ofrece una visión precisa del rendimiento y consumo de recursos en tiempo de ejecución de una consulta, algo crucial para entornos de producción. A diferencia del plan estimado, que se basa en estadísticas previas, el plan real refleja las operaciones exactas que SQL Server ejecuta, incluyendo ajustes específicos de paralelismo, decisiones en el uso de índices y variaciones en la ejecución según los datos y los recursos disponibles. Esta precisión nos permite identificar cuellos de botella y problemas de optimización, como el parameter sniffing, donde SQL Server puede seleccionar un plan ineficiente basado en parámetros iniciales de la consulta.

Además, el plan real permite observar el impacto de condiciones específicas del sistema, como bloqueos y alta carga de CPU o memoria, brindándonos una comprensión completa del rendimiento bajo diferentes estados. En resumen, esta visibilidad detallada nos facilita la optimización continua, ya que podemos detectar patrones de rendimiento y ajustar el sistema o la consulta para mejorar la eficiencia sin necesidad de re-ejecutar la consulta. En conjunto, el acceso a planes reales convierte el análisis de rendimiento en un proceso más efectivo, permitiéndonos una gestión más precisa y proactiva del rendimiento del sistema SQL Server.

Almacenar en caché planes de ejecución reales

SQL Server nos permite almacenar el plan de ejecución real en la caché de planes, en lugar de solo el estimado. Esta configuración es especialmente útil para detectar problemas de rendimiento, ya que proporciona información precisa sin la necesidad de una segunda ejecución de la consulta.

Para activar la traza globalmente en el servidor, podemos hacer uso de la traza 2451:

DBCC TRACEON(2451, -1);

Donde 2451 es el número de la traza que permite almacenar el plan real y -1 establece que la traza se active a nivel global para todas las bases de datos y usuarios.

Nota: Activar la traza puede aumentar el uso de recursos del sistema, ya que SQL Server debe registrar información adicional para cada consulta que se ejecuta. Además, activar esta función requiere vaciar la caché de planes por lo que es recomendable activarlo solo si es estrictamente necesario y preferiblemente en ventanas de mantenimiento programadas o aprovechando una intervención de un reinicio del servicio.

Otra opción es activar esta característica a nivel de base de datos para que no se aplique a todo el servidor. En este caso, aunque los riesgos son los mismos vamos a poder minimizar el impacto. Para activarlo a nivel de base de datos usaremos el siguiente comando sobre la base de datos donde queramos cambiar la configuración.

ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON;

Acceso al último plan de rjecución real

Una vez activada la traza o la configuración de base de datos, SQL Server almacenará los planes de ejecución reales en el caché de planes. Para acceder a ellos, utilizamos vistas de administración dinámica (DMVs) como sys.dm_exec_query_stats y sys.dm_exec_query_plan. Estas vistas nos proporcionan datos sobre las consultas y los planes almacenados en caché, permitiendo su análisis sin necesidad de re-ejecutar la consulta.

Ejemplo de consulta para obtener el último plan real

La siguiente consulta permite recuperar el último plan de ejecución real de las consultas en caché:

SELECT qs.creation_time, qs.last_execution_time, qs.execution_count, st.text AS query_text, qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.last_execution_time DESC;

Esta consulta devuelve el campo query_text con el texto SQL de la consulta y el campo query_plan con el plan de ejecución en formato XML. Para ver el plan de ejecución gráfico, podemos hacer clic en el XML en SQL Server Management Studio (SSMS), lo cual nos permite analizar visualmente las operaciones ejecutadas, los índices utilizados y el costo relativo de cada operación.

Filtrado de una consulta específica

Si estamos buscando el plan de ejecución de una consulta en particular, es posible restringir los resultados usando un filtro en el texto de la consulta, lo cual facilita la identificación del plan específico entre todas las consultas en el caché:

SELECT qs.creation_time, qs.last_execution_time, qs.execution_count, st.text AS query_text, qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
WHERE st.text LIKE '%Parte del texto de la consulta%'
ORDER BY qs.last_execution_time DESC;

Nota: A la hora de introducir parte del texto de la consulta que buscas en el filtro ten en cuenta que debe ser parte de la consulta (normalmente un nombre de una tabla o de un campo) pero nunca uno de los valores de los campos por los que estás filtrando ya que SQL Server remplaza esos valores por parámetros a la hora de almacenar el plan.

Es decir, si quieres buscar la consulta “SELECT Campo1 FROM MiTabla WHERE Campo2=’tutifruti’;” podrás buscar por ‘%Campo1%’, ‘%Campo2%’ o ‘%MiTabla%’ pero no por ‘%tutifruti%’. Cuando encuentres el plan de ejecución vas a ver que lo que se ha guardado es algo parecido a esto “SELECT Campo1 FROM MiTabla WHERE Campo2=@0”

Consideraciones Finales

La capacidad de acceder al último plan de ejecución real sin re-ejecutar la consulta es una capacidad poderosa para los DBAs, especialmente porque nos facilita la tarea de buscar problemas de rendimiento. Esta configuración nos ofrece un enfoque integral para monitorizar, analizar y optimizar el rendimiento de las consultas en entornos de producción sin comprometer la estabilidad del sistema. Sin embargo, aunque este enfoque nos permite detectar problemas de rendimiento con precisión y realizar ajustes informados para mejorar la eficiencia general del sistema para mi sigue siendo mejor la alternativa de Query Store donde vamos a poder ver todo el historial de planes de ejecución a lo largo del tiempo que hayamos definido y no solo el último plan real almacenado en caché.

Nov

2024

Bloqueos con un SPID negativo

Los bloqueos en SQL Server son un componente fundamental en la gestión de transacciones concurrentes, asegurando la integridad y consistencia de los datos. Sin embargo, algunos bloqueos pueden presentar características inusuales, como aquellos que involucran identificadores de sesión (SPID) negativos. Estos bloqueos negativos pueden generar confusión si no se comprenden a fondo, ya que están relacionados con operaciones internas de SQL Server o problemas específicos en el manejo de transacciones y recursos.

En este artículo, vamos a ver en profundidad los bloqueos con identificadores de sesión (SPID) negativos en SQL Server, incluyendo los valores del -1 al -5, explicando qué significan, qué los causa y cómo diagnosticarlos y resolverlos. Como vais a poder ver a lo largo del artículo, estos bloqueos no serán los más comunes que nos vamos a encontrar pero si que tienen una gran importancia y debemos conocerlos.

Introducción a los bloqueos en SQL Server

El sistema de gestión de bloqueos de SQL Server asegura que las transacciones accedan a los recursos de manera controlada, evitando conflictos que puedan comprometer la integridad de los datos. Los bloqueos suelen ser representados por un número positivo, que identifica la sesión (SPID) que sostiene el bloqueo. Sin embargo, en situaciones especiales, SQL Server puede mostrar bloqueos con identificadores de sesión negativos.

Estos SPID negativos no representan directamente sesiones de usuario, sino que están relacionados con tareas internas del motor de SQL Server, como la gestión de transacciones distribuidas (DTC), recuperación diferida, y la gestión de latches (mecanismos de sincronización de memoria). A continuación, vamos a explorar cada uno de estos identificadores negativos y sus implicaciones.

SPID -1: Bloqueo por Orphaned Lock (bloqueo huérfano)

El identificador -1 se asocia con bloqueos huérfanos (orphaned locks). Un bloqueo huérfano es un bloqueo que SQL Server ha perdido de vista, es decir, SQL Server ha perdido la referencia a qué sesión tenía el control de dicho bloqueo.

Descripción: El SPID -1 indica que el bloqueo ha sido dejado huérfano, lo que generalmente ocurre debido a errores en la gestión de bloqueos. En muchas ocasiones, este comportamiento es el resultado de un bug en SQL Server o de una desconexión inesperada de una sesión que estaba reteniendo el bloqueo.

Causas comunes: Esto puede suceder si una conexión de cliente se cierra inesperadamente mientras sostenía un bloqueo o si SQL Server experimenta un problema interno que afecta la gestión de los bloqueos. Estos bloqueos huérfanos pueden impedir que otros procesos accedan a los recursos bloqueados.

Diagnóstico y solución: En la mayoría de los casos, SQL Server debería liberar automáticamente los bloqueos huérfanos, pero si persisten, es recomendable revisar los registros de errores (SQL Server error logs) para identificar posibles fallos. Si el bloqueo huérfano afecta el rendimiento del servidor o causa problemas de acceso a los datos, es posible que se necesite reiniciar la instancia de SQL Server o aplicar parches que solucionen el problema.

SPID -2: Bloqueo por Pending DTC Transaction (transacción DTC pendiente)

El SPID -2 está relacionado con las transacciones distribuidas (DTC, Distributed Transaction Coordinator) que están pendientes de finalización. Esto ocurre cuando la conexión del cliente, que formaba parte de una transacción distribuida, se desconecta y la transacción queda en un estado incompleto, esperando que el administrador de DTC (MSDTC) procese el estado final de la transacción.

Descripción: Este identificador de sesión indica que SQL Server está esperando una notificación de cambio de estado por parte del coordinador de transacciones distribuidas (MSDTC). La transacción no puede completarse porque los clientes asociados con la transacción distribuida se han desconectado antes de invocar el commit o rollback.

Causas comunes: Esto suele ocurrir cuando una aplicación cliente que maneja transacciones DTC se desconecta inesperadamente sin finalizar correctamente la transacción. SQL Server queda a la espera de que el cliente confirme si debe comprometer o revertir la transacción.

Diagnóstico y solución: Para resolver el problema, el cliente debe reconectarse y completar la transacción invocando explícitamente un commit o rollback. En situaciones donde el cliente no puede reconectarse, el administrador de SQL Server puede finalizar la transacción manualmente utilizando las herramientas del coordinador de transacciones distribuidas (DTC). También es útil revisar los registros de errores de SQL Server y del DTC para identificar qué causó la desconexión.

SPID -3: Bloqueo por Deferred Recovery (recuperación diferida)

El SPID -3 está relacionado con transacciones diferidas. Una transacción diferida es una transacción que ha sido marcada para ser revertida o recuperada, pero que no puede completarse de inmediato debido a que los recursos necesarios no están disponibles.

Descripción: Este identificador indica que SQL Server mantiene el bloqueo porque una operación de recuperación o reversión ha sido pospuesta. Esto suele ocurrir cuando una transacción está en proceso de ser revertida (rollback), pero la operación no puede completarse debido a problemas con los recursos implicados, como archivos o tablas que ya no están disponibles.

Causas comunes: Las transacciones diferidas suelen ocurrir cuando SQL Server no puede acceder a los recursos necesarios para completar la reversión de la transacción. Un ejemplo sería si una tabla o índice fue eliminado mientras una transacción estaba activa sobre esos objetos. También puede ser resultado de problemas de espacio en disco o de memoria.

Diagnóstico y solución: Para diagnosticar este tipo de bloqueo, puedes utilizar la vista sys.dm_tran_active_transactions, que muestra información sobre las transacciones activas, incluidas las diferidas. Generalmente, SQL Server resolverá estas transacciones diferidas cuando los recursos se vuelvan disponibles, pero en algunos casos podría ser necesario realizar acciones manuales, como restaurar la base de datos o liberar espacio en disco.

SPID -4: Bloqueo por Latch Transition (transición de latch)

El SPID -4 se refiere a las transiciones de latch. Un latch es una estructura interna de sincronización que SQL Server utiliza para gestionar el acceso a los recursos en memoria, como las páginas de datos en la caché de búferes.

Descripción: Este SPID indica que SQL Server está en el proceso de liberar un latch, pero dicha liberación aún no ha finalizado. Estos bloqueos suelen aparecer cuando SQL Server está manejando una alta carga de trabajo que involucra operaciones intensivas de lectura o escritura en memoria.

Causas comunes: Las transiciones de latch son comunes en operaciones de alta concurrencia, como cuando múltiples sesiones intentan acceder a las mismas páginas en la caché de búferes. Esto puede ocurrir durante operaciones de entrada/salida intensivas o en escenarios donde se produce una contención por el acceso a recursos de memoria.

Diagnóstico y solución: Para diagnosticar estos bloqueos, puedes utilizar la vista sys.dm_os_latch_stats para monitorizar las estadísticas de latches. Si los bloqueos relacionados con latches son frecuentes, puede ser necesario optimizar las consultas, rediseñar los índices o ajustar los parámetros de configuración de memoria de SQL Server. También puedes revisar el tamaño de la caché de búferes para asegurarte de que SQL Server tenga suficientes recursos de memoria para manejar la carga de trabajo.

SPID -5: Bloqueo por Latch Task Releasor (liberación de latch)

El SPID -5 está relacionado con la liberación de latches en SQL Server, específicamente con tareas del sistema que están en proceso de liberar un latch. Los latches de entrada/salida (I/O) son los más comunes que se liberan bajo este identificador.

Descripción: Este identificador de sesión indica que SQL Server está esperando que una tarea del sistema libere un latch. Los I/O latches son los más comunes en este tipo de situación, protegiendo el acceso a las páginas de datos durante operaciones de lectura o escritura.

Causas comunes: Los bloqueos con SPID -5 suelen aparecer cuando hay contención en la memoria, particularmente en las páginas de datos almacenadas en la caché de búferes. Si varios hilos intentan acceder a la misma página simultáneamente, SQL Server utiliza un latch para asegurar que sólo un hilo acceda a la página en un momento dado, lo que puede generar tiempos de espera.

Diagnóstico y solución: Para diagnosticar este tipo de bloqueo, puedes utilizar las vistas sys.dm_exec_requests y sys.dm_os_waiting_tasks para identificar las sesiones que están esperando la liberación de latches. Optimizar el diseño de índices o ajustar los patrones de acceso a los datos puede ayudar a reducir la contención por los latches. Además, monitorizar el uso de la memoria y evaluar si SQL Server tiene suficientes recursos para manejar la carga de trabajo puede ser necesario.

Conclusión

Los bloqueos con identificadores de sesión negativos en SQL Server son indicadores de operaciones internas y procesos de administración de recursos dentro del motor de la base de datos. Desde bloqueos huérfanos hasta problemas con transacciones distribuidas y latches, cada uno de estos SPIDs negativos tiene un significado específico y un impacto potencial en el rendimiento y la estabilidad del servidor.

Comprender la naturaleza de estos bloqueos y cómo diagnosticarlos es clave para asegurar el buen funcionamiento de SQL Server. Utilizar herramientas como las vistas de administración dinámica (DMVs) y monitorizar los registros de errores te permitirá identificar problemas antes de que afecten el rendimiento general del sistema. En muchos casos, las soluciones implican ajustes en la configuración de recursos, la optimización de consultas y, en situaciones más complejas, la intervención manual para liberar o resolver los bloqueos pendientes.

SQL Server proporciona un entorno robusto para la gestión de transacciones y acceso concurrente, pero la correcta interpretación de estos SPIDs negativos puede marcar la diferencia en la identificación temprana de problemas que podrían afectar el rendimiento de la base de datos.

Nov

2024

Impacto en el rendimiento de Query Store

Cuando hablamos de Query Store en SQL Server, lo hacemos refiriéndonos a una de las herramientas más útiles para controlar y mejorar el rendimiento de nuestras consultas. No es ningún secreto que es una herramienta que personalmente me encanta y, no es para menos, nos permite capturar el historial de ejecución, los planes utilizados y, lo más importante, las métricas asociadas. Sin embargo, el otro día, un seguidor me trasladó una inquietud en un comentario de YouTube y no es otra que si la activación de Query Store puede tener un impacto negativo en el rendimiento. Ciertamente, Query Store conlleva una carga adicional que, en ciertos entornos, debemos saber gestionar con cuidado.

En este artículo, vamos a profundizar en el impacto real que tiene el Query Store en términos de CPU, memoria y disco, y cómo podemos configurarlo para obtener el máximo beneficio sin comprometer el rendimiento de nuestros sistemas.

¿Qué hace Query Store y por qué es tan útil?

En resumidas cuentas, Query Store se encarga de registrar el historial de todas las consultas que se ejecutan en nuestra base de datos, junto con los planes de ejecución y sus métricas de rendimiento. Es como tener un «registro médico» de nuestras consultas, donde podemos ver qué ha ido mal (o bien) y cuándo. Esto nos permite identificar rápidamente problemas de rendimiento y aplicar soluciones, como forzar el uso de un plan de ejecución específico. Es decir, cuando una consulta empieza a tardar más de lo habitual, Query Store nos permite ver qué ha cambiado, ya sea el plan de ejecución o las estadísticas.

Antes de seguir, si quieres saber más sobre el funcionamiento de Query Store, te recomiendo leer el artículo que le dedicamos.

¿Cuánto impacto tiene realmente Query Store en el rendimiento?

Aunque tener todo ese control suena genial, también hay que ser conscientes de que capturar tanta información tiene un coste. Y eso es justo lo que venimos a analizar hoy. Veamos cómo afecta a los distintos recursos del sistema.

Consumo de CPU y memoria

Lo primero que debemos tener claro es que Query Store no afecta a la ejecución directa de las consultas, ya que guarda la información de manera asíncrona. Pero eso no significa que no utilice recursos. El impacto más notable en CPU y memoria ocurre cuando se agregan los datos de las consultas para almacenarlos. Dependiendo de la cantidad de consultas y el tipo de carga (ad-hoc o batch, por ejemplo), este proceso puede requerir más recursos.

Por lo general, el impacto en CPU suele ser pequeño, pero no inexistente. El procesamiento de los datos para agregarlos y almacenarlos puede generar una pequeña sobrecarga, especialmente si el servidor ya está bajo presión por una alta carga de trabajo.

Hablando sobre la memoria RAM, Query Store usa su propia caché en memoria para almacenar temporalmente los datos antes de volcar (flush) al disco. En entornos con mucha actividad, puede necesitar más memoria de lo esperado si los intervalos de flush son largos o si el servidor está cerca de su límite de uso de memoria.

Espacio en disco

Aquí es donde más suele preocuparnos el impacto de Query Store. Todos los datos capturados tienen que almacenarse en disco, y si no tenemos cuidado, la cantidad de espacio utilizado puede crecer rápidamente. En Azure SQL Database, por ejemplo, el tamaño máximo que podemos asignar a Query Store es de 10 GB. En SQL Server on-premise, no hay un límite y, aunque el espacio propuesto por defecto es de 1 Gb podemos configurar manualmente el espacio que necesitemos y podamos asumir.

Si trabajamos en un entorno de producción con una base de datos que recibe muchas consultas por segundo, podríamos empezar a ver cómo el espacio en disco se reduce rápidamente si no gestionamos bien la configuración. En muchos casos, el crecimiento de los datos es gradual, pero en sistemas con grandes volúmenes de consultas, los datos pueden acumularse más rápido de lo esperado.

¿Cómo minimizar el impacto de Query Store?

Como hemos visto, es cierto que Query Store añade cierta carga al sistema, sin embargo, hay varias formas de mitigar su impacto y asegurarnos de que siga siendo beneficioso sin comprometer el rendimiento. Aquí os dejo algunas estrategias prácticas.

Ajustar el intervalo de flush

El flush es el proceso por el cual los datos que Query Store guarda en memoria en un primer momento se escriben en disco para persistirse. De manera predeterminada, este proceso ocurre cada 15 minutos, pero puedes ajustar este intervalo para reducir la carga en momentos críticos. Si trabajas con una base de datos muy activa, podrías reducir el intervalo para evitar que se acumule demasiada información en memoria y así dispersar la carga de escritura.

Una buena práctica es empezar con el valor por defecto y observar el comportamiento del servidor. Si notas que el servidor tiene picos de uso de disco en momentos específicos, reducir el intervalo de flush puede ayudar a que la escritura de datos sea más constante y menos impactante. Como ves, esta configuración afecta tanto a la cantidad de RAM que demanda Query Store como al consumo de CPU necesario para mover los datos de la memoria al disco.

Limitar el uso de espacio en disco

No todas las consultas tienen que ser almacenadas indefinidamente. SQL Server te permite configurar límites de espacio para Query Store y también establecer políticas de retención que eliminen los datos antiguos. Esto es especialmente útil para evitar que la información histórica crezca sin control.

Recomendación: Establece un límite de espacio en disco que sea acorde al tamaño y volumen de consultas de tu base de datos. Por ejemplo, para bases de datos medianas, un límite de 500 MB podría ser suficiente. En bases de datos más grandes, este valor puede aumentar, pero es esencial realizar una monitorización constante.

Configurar la retención de datos

Muy ligado al punto anterior. No tiene sentido almacenar datos de consultas por años si solo los vamos a analizar durante las últimas semanas. Configura un período de retención que se ajuste a tus necesidades de análisis. En la mayoría de los casos, mantener entre 30 y 60 días de historial es suficiente para identificar patrones y resolver problemas de rendimiento.

Usar el modo CUSTOM de captura

De forma predeterminada Query Store está configurado con un modo de captura automático que no captura todas las consultas sino solo aquellas que el motor de base de datos considera relevantes para el rendimiento. Esto, se puede cambiar para capturar todas las consultas lo que claramente incrementará el consumo de recursos.

Sin embargo, con SQL Server 2019, se introdujo un modo CUSTOM que nos permite capturar solo aquellas consultas que nos interesan, según criterios específicos que podemos definir. Esto es extremadamente útil en entornos donde se ejecutan miles de consultas ad-hoc o de bajo valor, que realmente no necesitamos monitorizar en detalle. Al usar este modo, reducimos el volumen de datos capturados, lo que disminuye la carga en disco y memoria.

Monitorizar el impacto de Query Store

Como siempre, es clave monitorizar cualquier herramienta que utilicemos en nuestros servidores. SQL Server Management Studio (SSMS) nos ofrece varias vistas que nos permiten ver el impacto que Query Store tiene en términos de uso de CPU, memoria y disco. Estas vistas nos ayudarán a realizar ajustes si detectamos que la carga es mayor de lo esperado. Otra opción sería usar el monitor de recursos de windows y comparar el consumo con la herramienta activada y sin ella

Conclusión

Habilitar Query Store puede ser un gran aliado para mejorar el rendimiento de nuestras bases de datos, pero requiere un enfoque cuidadoso para no generar sobrecarga en el servidor. Al ajustar el intervalo de flush, limitar el uso de espacio en disco, y capturar solo los datos necesarios, podemos beneficiarnos de su potencial sin afectar negativamente el rendimiento general.

La clave está en configurar y ajustar Query Store según las necesidades específicas de nuestro entorno. En la mayoría de los casos, unos pequeños ajustes pueden marcar una gran diferencia en términos de rendimiento y eficiencia. Como siempre, no olvides monitorizar y realizar pruebas periódicas para asegurarte de que todo funcione de manera óptima.

Nov

2024

Gestión de tablas temporales en SQL Server

El uso de tablas temporales en SQL Server es común para el almacenamiento de datos temporales durante el procesamiento de consultas. Aunque su uso es sencillo, la eliminación de estas tablas debe gestionarse adecuadamente. Seguro que estás acostumbrado a ver algo parecido a esto “DROP TABLE #TEMPTABLE;” al final de las consultas que usan tablas temporales. Yo también, sin embargo, el otro día encontré un artículo que lo desaconsejaba.
El resumen es simple, con decenas de sesiones creando y eliminando tablas temporales en bucle, al final nos encontramos con tiempos de espera de PAGELATHes pero si no borramos las tablas y cerramos la sesión esto no pasa. El artículo en cuestión no solo captó mi curiosidad, David, un seguidor del blog, también lo vio y me lo mandó para que opinase al respecto. Yo me debo a vosotros así que aquí van mis pruebas. En este artículo, veremos como no siempre es la mejor idea y cómo puede llegar a generar problemas de contención en tempdb.

Introducción a las tablas temporales

Las tablas temporales, identificadas con el prefijo # (locales) o ## (globales), son útiles para almacenar datos que solo necesitamos de manera transitoria. Son empleadas comúnmente en escenarios como el almacenamiento de resultados intermedios, la manipulación de datos en procesos ETL, o la mejora del rendimiento de consultas mediante la reducción del número de accesos a disco.

Estas tablas residen en la base de datos tempdb, lo que las convierte en una solución conveniente para manipular grandes volúmenes de datos sin afectar el esquema de las tablas principales. Sin embargo, tempdb es un recurso compartido, lo que significa que un uso incorrecto de las tablas temporales puede afectar el rendimiento global del servidor SQL.

Impacto de la eliminación de tablas temporales en tempdb

Eliminar tablas temporales manualmente usando DROP TABLE puede parecer una buena práctica para liberar recursos, pero en ciertos contextos, como sistemas de alta concurrencia, esto puede exacerbar los problemas de contención en tempdb. Cada vez que una tabla temporal se crea o elimina, tempdb necesita actualizar sus páginas de asignación, lo que incrementa la posibilidad de bloqueos de tipo PAGELATCH.

El uso del DROP TABLE, por tanto, desencadena este problema y si abusamos de ello, podemos llegar a notarlo.Por el contrario si dejamos que las tablas temporales se eliminen solas al cerrar la sesión esto no pasa. La clave está en cómo se elimina la tabla mediante DROP TABLE y en alguna optimización (no documentada, por supuesto) que tiene SQL Server para liberar las tablas temporales al cerrar la sesión. En teoría, cada operación que implique la creación, modificación o eliminación de tablas temporales añade carga a las páginas de asignación, lo que puede desencadenar los problemas de contención pero, lo cierto es que esto no pasa si cerramos la sesión y dejamos que las tablas temporales se eliminen solas.

Probando el problema de las tablas temporales

Como sé que es lo que la mayoría estáis esperando, no me voy a enrollar más, aquí va mi prueba. Lo primero que debéis saber es que estas pruebas las he realizado en mi máquina de pruebas más potente (16 procesadores lógicos a 3,8GHz y 32Gb de RAM), con un SQL Server 2022 completamente actualizado y la tempDB en un disco SSD.

Para la prueba he usado la herramienta Ostress de la suite RML Utilities de Microsoft. Esta herramienta está desarrollada para hacer pruebas de estrés contra servidores SQL y, si os parece, otro día hablaremos de ella.

Primera prueba: Borrar las tablas temporales

Para esta primera prueba he creado este procedimiento almacenado que veis a continuación. Simplemente crea una tabla temporal, inserta un valor y borra la tabla temporal.

CREATE PROC [dbo].[DropTempTable]
AS
BEGIN
CREATE TABLE #TempTable (ID INT null)
INSERT INTO #TempTable values(1)
DROP TABLE #TempTable 
END

A continuación, haciendo uso de Ostress he ejecutado el procedimiento 1000 veces en 50 sesiones paralelas. Es decir, he abierto 50 conexiones a la vez a la base de datos y en cada una de ellas he ejecutado 20 veces el procedimiento.

El resultado, como podéis ver a continuación han sido los comentados tiempos de espera de PAGELATACH

Siguientes pruebas

Bien, lo comentado por Haripriya Naidu en su artículo parece que es cierto pero, ¿será cierto que si no borramos la tabla temporal los problemas desaparecen?

Para esta segunda prueba simplemente he creado un procedimiento almacenado como el anterior pero sin la parte del borrado de la tabla temporal. Lo he llamado NoDropTempTable y de la misma manera que antes lo he ejecutado con Ostress y, ¿sabéis qué? No ha ocurrido nada. Cero contención. Ni un solo problema. He repetido la prueba con 1000 ejecuciones pero esta vez en 100 sesiones simultáneas y nada, mismo resultado, todo bien. Va a resultar que es cierto, alguna optimización tiene el motor de base de datos que no genera estos tiempos de espera.

En este punto, tenía que probar otra cosa, pero para ello, antes tenemos que comprender por qué nos hemos encontrado con este problema. Vamos a ello y luego os sigo contando.

¿Qué es PAGELATCH y cómo afecta a tempdb?

Un PAGELATCH es una forma de sincronización de acceso en memoria que SQL utiliza para garantizar la coherencia cuando múltiples procesos intentan acceder a las mismas páginas de datos. En el contexto de tempdb, estos bloqueos ocurren cuando múltiples sesiones intentan crear, modificar o eliminar tablas temporales simultáneamente, ya que todas residen en tempdb, un recurso compartido entre todas las sesiones.

En concreto, el problema que acabamos de ver surge porque muchas sesiones intentan acceder a las mismas páginas de administración en tempdb, en particular las páginas PFS (Page Free Space), GAM (Global Allocation Map) y SGAM (Shared Global Allocation Map), que gestionan la asignación de espacio en disco. Esto provoca contención de recursos y puede causar que las operaciones que dependen de estas páginas se ralenticen significativamente.

La última prueba, borrar temporales pero en memoria

Ahora que ya sabemos que los PAGELATCH que estabamos viendo son causados por el acceso a las páginas donde se almacenan los metadatos de las tablas, ¿que pasará si habilitamos la opción de Memory-Optimized TempDB Metadata que vimos en el pasado artículo? Pues bien, según mis pruebas con esta característica que se lleva a memoria las páginas de metadatos de TempDB el problema desaparece. La velocidad de la memoria es capaz de asumir las operaciones de lectura y escritura sin generar contención.

Estrategias para mitigar los problemas de contención en tempdb

Para reducir el riesgo de bloqueos PAGELATCH en tempdb, especialmente cuando trabajamos con tablas temporales en entornos altamente concurrentes, podemos implementar las siguientes estrategias:

Habilitar Memory-Optimized TempDB Metadata como acabamos de ver. El problema es que esto, en entornos cloud de Azure no es compatible, como vimos ayer.
Aumentar el número de archivos de tempdb: Una de las estrategias más efectivas es aumentar el número de archivos de datos de tempdb para distribuir la carga entre varias páginas de asignación. Se recomienda tener al menos un archivo de datos por cada núcleo lógico, hasta un máximo de ocho archivos, para aliviar la contención en las páginas PFS. Aunque en mi caso con esos 8 archivos no ha sido suficiente, lo cierto es que la prueba con 50 sesiones simultaneas sobre TempDB es un poco exagerada.
Optimizar el uso de tablas temporales: Limitar el uso de tablas temporales a los casos en los que sean estrictamente necesarias. Cuando sea posible, se deben utilizar alternativas como variables de tabla, que no generan la misma carga sobre tempdb.
Reutilización de tablas temporales: En lugar de crear y eliminar tablas temporales repetidamente en la misma sesión, es recomendable reutilizarlas dentro de la misma transacción o procedimiento almacenado, reduciendo así el número de operaciones de creación y eliminación en tempdb.

Otras ventajas de no borrar las tablas temporales

Además de todo lo que hemos visto hasta ahora, existe otra ventaja de la que no hemos hablado para no borrar las tablas temporales antes de tiempo. Cuando creamos y utilizamos tablas temporales en SQL Server, el optimizador de consultas puede reutilizarlas para generar y mantener planes de ejecución eficientes. Estos planes son cruciales, ya que permiten al servidor recordar la mejor manera de ejecutar consultas similares en el futuro, reduciendo la sobrecarga computacional.

Si eliminamos manualmente las tablas temporales mediante DROP TABLE y luego las recreamos, el optimizador pierde la capacidad de aprovechar estos planes previamente generados. Esto significa que, en consultas repetitivas, SQL Server tendría que volver a calcular el plan de ejecución desde cero, lo que incrementa el tiempo de procesamiento y reduce el rendimiento global. En casos donde las mismas tablas temporales son necesarias en múltiples ocasiones dentro de la transacción, dejar que SQL Server gestione automáticamente su ciclo de vida puede mejorar significativamente la eficiencia, ya que el sistema tiene más oportunidades de reutilizar los planes de ejecución optimizados y reducir la carga sobre tempdb.

Conclusión

El uso de tablas temporales en SQL Server puede ser una herramienta extremadamente útil, pero en entornos de alta concurrencia puede generar problemas de rendimiento si no se gestiona adecuadamente. El riesgo principal no reside en el DROP TABLE manual en sí, sino en la concurrencia y la carga sobre tempdb, pero evitando el borrado evitaremos el problema. Otras estrategias para mitigar los bloqueos PAGELATCH incluyen aumentar el número de archivos de tempdb, limitar el uso excesivo de tablas temporales y aprovechar las mejoras de las versiones más recientes de SQL Server. Con estas precauciones, podemos garantizar un uso más eficiente de las tablas temporales, evitando bloqueos y manteniendo un rendimiento óptimo del sistema.

Nov

2024

Optimizando TempDB: Metadata Optimizada en Memoria

Desde SQL Server 2019, Microsoft ha introducido varias mejoras en el manejo de la base de datos TempDB, una de las más destacadas es la funcionalidad Memory-Optimized TempDB Metadata. Esta característica está diseñada para reducir significativamente los cuellos de botella causados por la contención (bloqueos) en tablas internas de metadatos en entornos de alta concurrencia. En este artículo, quiero detallaros cómo funciona esta funcionalidad, sus beneficios, limitaciones y cómo implementarla en un entorno de producción.

Introducción a Memory-Optimized TempDB Metadata

TempDB es una base de datos que juega un papel crucial en el manejo de objetos temporales y tablas intermedias en SQL Server. Cuando trabajamos en entornos con múltiples usuarios concurrentes, la gestión de los metadatos de TempDB (estructuras como tablas temporales, índices y otros objetos transitorios) puede generar una gran contención y afectar negativamente el rendimiento.

Con la funcionalidad de Memory-Optimized TempDB Metadata, introducida en SQL Server 2019, las tablas del sistema que almacenan esta metadata se transforman en tablas en memoria no durables. Esto reduce significativamente la latencia, ya que elimina la necesidad de acceder a disco para gestionar estos objetos, un proceso que antes provocaba bloqueos en entornos altamente concurridos.

Ventajas de Memory-Optimized TempDB Metadata

Como acabamos de ver, uno de los principales problemas con TempDB en entornos de alta concurrencia es la contención en las estructuras de metadatos. Al mover estos datos a tablas en memoria, SQL Server minimiza los tiempos de espera provocados por bloqueos y esperas de recursos (latches), lo que permite a más transacciones concurrentes acceder y modificar objetos temporales de manera más eficiente

Además, al eliminar las operaciones de E/S de disco en los metadatos de TempDB, se mejora el rendimiento en las consultas que dependen de tablas temporales, como las utilizadas en procesos ETL, análisis de datos y operaciones transaccionales intensivas. Las tablas en memoria, al ser no durables, proporcionan tiempos de respuesta más rápidos y un mayor rendimiento en general.

Gracias a estas mejoras, SQL Server 2019 y versiones posteriores pueden escalar mucho mejor en términos de transacciones concurrentes. Básicamente, la reducción en la contención permite que el sistema gestione un mayor volumen de transacciones sin experimentar una degradación del rendimiento.

Disponibilidad y Soporte en Plataformas

La funcionalidad de Memory-Optimized TempDB Metadata está disponible desde SQL Server 2019. Sin embargo, algo incomprensible, no está implementada en entornos como Azure SQL Database o Azure SQL Managed Instance, lo que podría ser un inconveniente para aquellos que utilizan estas plataformas. Sin embargo, para aumentar aún más mi incomprensión, las instancias SQL Server RDS de AWS (Amazon) sí soportan esta funcionalidad, lo que supone una ventaja para los usuarios de AWS sobre los de Azure al poder implementar esta mejora en sus entornos administrados.

Limitaciones de Memory-Optimized TempDB Metadata

A pesar de las claras ventajas, es importante tener en cuenta algunas limitaciones al considerar habilitar esta funcionalidad. Si bien las tablas en memoria optimizadas para metadatos son una excelente mejora, cuando está habilitada esta funcionalidad no es posible crear índices columnstore en tablas temporales, lo que puede afectar a algunos escenarios de análisis de datos.

Siguiendo con los índices columnares, esto no es solo un problema a la hora de crear tablas temporales. El procedimiento sp_estimate_data_compression_savings no se va a poder ejecutar en tablas que utilicen índices columnstore cuando la metadata optimizada está habilitada. Esto puede limitar aún más el uso de esta funcionalidad en procesos que requieren estimar ganancias de compresión.

Otro de sus problemas y uno de los principales es que cuando se utilizan tablas en memoria optimizadas en una transacción, esas transacciones que acceden a estas tablas temporales no pueden acceder simultáneamente a otras bases de datos. Esto puede complicar ciertos patrones de uso donde se necesite trabajar con múltiples bases de datos en una sola transacción.

Para finalizar este apartado, un último problema (aunque para mi menor) es que para habilitar esta funcionalidad, es necesario reiniciar el servicio de SQL Server. Esto implica que su implementación debe planificarse durante una ventana de mantenimiento para evitar interrupciones en el servicio pero es una actuación que haremos una vez solamente.

Cómo Habilitar Memory-Optimized TempDB Metadata

Para habilitar esta funcionalidad vamos a seguir un proceso relativamente sencillo. Existen dos formas principales de habilitarla, mediante T-SQL o mediante sp_configure.

Mediante T-SQL:

ALTER SERVER CONFIGURATION SET MEMORY_OPTIMIZED TEMPDB_METADATA = ON;

Mediante sp_configure:

EXEC sys.sp_configure N'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
EXEC sys.sp_configure N'tempdb metadata memory-optimized', 1;
RECONFIGURE WITH OVERRIDE;

Después de ejecutar estos comandos, es necesario reiniciar el servicio de SQL Server para que los cambios surtan efecto

Casos de Uso Comunes

Una vez que hemos visto que sus limitaciones no nos afectan (o que podemos salvar esas limitaciones) y habiendo habilitado la característica, Memory-Optimized TempDB Metadata es particularmente beneficiosa en los siguientes escenarios:

Sistemas OLTP (transaccionales) de Alta Concurrencia: En estos entornos, la contención en TempDB puede ser un cuello de botella significativo. Al habilitar esta funcionalidad, se mejora la capacidad del sistema para manejar miles de transacciones concurrentes que dependen de tablas temporales.
Procesos ETL y Consultas Analíticas: Las consultas que dependen de grandes conjuntos de datos temporales pueden beneficiarse enormemente al eliminar la latencia asociada con la gestión de objetos temporales en disco.
Cargas de Trabajo Intensivas en TempDB: En sistemas donde TempDB juega un papel crucial para la creación de objetos temporales o transacciones de corta duración, esta funcionalidad puede marcar una diferencia significativa en el rendimiento.

Conclusión

La funcionalidad Memory-Optimized TempDB Metadata es una mejora poderosa para SQL Server 2019, diseñada para solucionar problemas de contención y mejorar el rendimiento en entornos con alta concurrencia. Si bien no está libre de limitaciones, su capacidad para reducir la contención y mejorar el rendimiento la convierte en una opción atractiva para entornos que dependen intensamente de TempDB. Con el tiempo, es probable que Microsoft siga refinando esta característica, ampliando su soporte y optimización para más escenarios.

Rendimiento

Optimizando TempDB: Metadata Optimizada en Memoria

Introducción a Memory-Optimized TempDB Metadata

Ventajas de Memory-Optimized TempDB Metadata

Disponibilidad y Soporte en Plataformas

Limitaciones de Memory-Optimized TempDB Metadata

Cómo Habilitar Memory-Optimized TempDB Metadata

Casos de Uso Comunes

Conclusión

NO TE PIERDAS NADA

Colabora con nosotros

Entradas recientes

Etiquetas

Comentarios recientes