Teoría BBDD

19
Feb
2025

Asignación AVANZADA de páginas en SQL Server

En nuestro pasado artículo vimos como SQL Server organizaba el almacenamiento de los datos en páginas de datos y como lo administraba gracias a páginas de metadatos. En ese artículo, os prometí que hablaríamos más de estas páginas de asignación. Es muy importante entender estos conceptos pues son imprescindibles para poder hacer una optimización avanzada y enfrentar con solvencia problemas complejos de almacenamiento. 

Como ya vimos en el pasado artículo, SQL Server administra el almacenamiento mediante estructuras internas diseñadas para optimizar la asignación y reutilización del espacio. Dentro de estas estructuras, las páginas de asignación juegan un papel clave en la gestión de las páginas y extensiones dentro de los archivos de datos (.mdf o .ndf). Estas páginas permiten un acceso eficiente a los datos y minimizan la fragmentación, asegurando el mejor uso posible del espacio disponible.

En este artículo, profundizaremos en la función y el comportamiento de las páginas de asignación en SQL Server, complementando la información que vimos y ajustando los detalles donde sea necesario para proporcionarnos una visión más completa.

Estructuras de asignación de espacio en SQL Server

Resumamos un poco lo que vimos en el pasado artículo. SQL Server organiza el almacenamiento en páginas de 8 KB, que se agrupan en extents de 64 KB (8 páginas). Para administrar la asignación de estas páginas y extensiones, SQL Server utiliza las siguientes estructuras especializadas:

  • PFS (Page Free Space) → Rastreo del uso del espacio en páginas individuales.
  • GAM (Global Allocation Map) → Rastreo de extensiones asignadas y libres.
  • SGAM (Shared Global Allocation Map) → Rastreo de extensiones mixtas con espacio libre.
  • IAM (Index Allocation Map) → Mapeo de asignaciones de páginas y extensiones a objetos específicos.

Ahora que ya hemos refrescado ideas vamos a  ver en detalle cada una de estas estructuras. Pero antes una pequeña aclaración. Todos los archivos de datos (mdf y ndf) empiezan siempre con la misma estructura, una primera página (página 0) con cabeceras, y a continuación una página PFS, una GAM y una SGAM (página 1, 2 y 3 respectivamente)

Asignación Páginas

Páginas PFS (Page Free Space)

Para gestionar correctamente la asignación de almacenamiento es imprescindible tener controlado en todo momento cuánto espacio tienes libre. Las PFS controlan cuánto espacio libre hay dentro de cada página individual en un archivo de base de datos. Son fundamentales para la inserción eficiente de datos, ya que SQL Server puede identificar rápidamente qué página tiene espacio disponible para nuevas filas sin necesidad de asignar nuevas páginas.

Características de las páginas PFS

Cada página PFS rastrea el estado de 8,088 de datos (~64 MB de datos). Es decir, si la página PFS es la página 1 del archivo de datos nos vamos a encontrar con una de estas cada 8088 páginas de datos, 

Estas PFS se actualizan cada vez que una página de datos cambia de estado, por ejemplo, cuando se insertan o eliminan filas.

La información se almacena en bits dentro de la página PFS, con los siguientes valores:

0x00 → Página vacía.
0x40 → Página 1-50% ocupada.
0x80 → Página 51-100% ocupada.
0xC0 → Página completamente llena.
0xE0 → Página reservada para IAM u otros propósitos.

Ubicación de las páginas PFS en el archivo de datos

SQL Server es capaz de registrar con un Byte de información la página y su estado por lo que en una página PFS que es de 8KB podremos almacenar información de 8 * 1024 páginas de datos. Esto significa que las PFS aparecen cada 8,088 páginas en un archivo de datos. Por ejemplo, si la primera página PFS sabemos que está en la página 1, la siguiente estará en la página 8,089, la siguiente en la página 16,177, y así sucesivamente.

Consulta para inspeccionar páginas PFS

Podemos analizar las PFS con DBCC PAGE pero para ello vamos a tener que activar primero la traza 3604 para ver el resultado en SSMS y que no vaya al log de errores de SQL Server. Al comando DBCC PAGE le vamos a pasar como parámetros primero el ID de la base de datos, seguido del ID del fichero de datos, el número de página que queremos ver y un 3.

DBCC TRACEON(3604);
DBCC PAGE(6, 1, 1, 3);
Asignación Páginas PFS

Páginas GAM (Global Allocation Map)

Las GAM rastrean la asignación de extensiones dentro del archivo de base de datos. Permiten a SQL Server identificar rápidamente qué extensiones están libres y cuáles han sido asignadas a objetos.

Características de las páginas GAM

Cada página GAM cubre 511,232 de datos (unos 3 GB de datos).

Utiliza 1 bit por extensión:
1 → Extensión libre.
0 → Extensión asignada.

Como sabéis, SQL Server no libera el espacio cuando los datos se borran sino que se lo queda y lo marca como disponible. Pues bien, GAM se usa cuando SQL Server necesita encontrar espacio disponible y no asignado para nuevos objetos. 

Almacenamiento GAM

Cada página GAM es una página de 8 KB (como todas) que contiene información de 63,904 extents (ya que cada bit representa un extent). Como cada extent tiene 8 páginas, esto significa que una página GAM puede rastrear 511,232 páginas, lo que equivale a aproximadamente 3 GB de almacenamiento. Pero no tienes por qué creer ciegamente en lo que yo te diga, vamos a hacer los números.

1 GAM = 8 KB = 8 * 1024 bytes = 8192  bytes
De estos 8192 bytes, 204 bytes están reservados por lo que quedan 7988 en una página  GAM. ¿Cuántas páginas de datos pueden cubrir 7988 bytes?
1 GAM = 7988 bytes = 7988 * 8 bits = 63904 bits
Entonces si una página GAM tiene 63904 bits y necesita 1 bit para registrar 1 extent eso significa que puede almacenar información de 63904 extents. Convirtamos eso en tamaño.
1 GAM = 63904 extents = 63904 * 8 páginas = 511232 páginas
11232 páginas * 8 KB = 4,089,856 KB
4,089,856 KB / 1024 = 3994 MB / 1024 = 3,3144 GB

Ubicación de las páginas GAM en el archivo de datos

Si ya sabemos que las GAM aparecen cada 511,232 páginas y que la primera GAM está en la página 2, podemos saber que la siguiente en la página 511,234, etc.

Consulta para inspeccionar GAM

Igual que hemos hecho antes podemos usar DBCC PAGE para ver las GAM.

DBCC TRACEON(3604);
DBCC PAGE(6, 1, 2, 3);
Asignación Páginas GAM

Páginas SGAM (Shared Global Allocation Map)

Las SGAM funcionan de manera complementaria a las GAM y se utilizan para rastrear extents mixtos que aún tienen espacio disponible para nuevos datos.

Características de SGAM

Cada página SGAM cubre 511,232 páginas ( unos 3 GB de datos), igual que GAM. También utiliza 1 bit por extensión:
1 → Extensión mixta con espacio libre.
0 → Extensión completamente ocupada o no mixta.

SGAM se usa cuando SQL Server necesita encontrar espacio en extensiones mixtas para insertar nuevos datos sin asignar nuevas páginas.

Almacenamiento SGAM

Ubicación de las SGAM en el archivo de datos

Las SGAM aparecen cada 511,232 páginas, comenzando en la página 3, luego en la página 511,235, y así sucesivamente.

Consulta para inspeccionar SGAM

DBCC TRACEON(3604);
DBCC PAGE(6,1,3.,3);
Asignación Páginas SGAM

Páginas IAM (Index Allocation Map)

Las IAM rastrean qué páginas y extensiones pertenecen a un objeto específico, como una tabla o un índice. Cada página IAM cubre un solo objeto (una tabla o un índice) en un solo archivo. Incluso puede abarcar múltiples páginas y archivos si el objeto está distribuido. 

Las IAM almacenan la relación entre los objetos y sus páginas de datos distribuidas en el archivo de datos. A diferencia de las GAM y SGAM, las IAM no tienen una ubicación fija dentro del archivo, ya que se crean dinámicamente para cada objeto.

Consulta para inspeccionar IAM

En este caso es mucho más complicado porque las IAM no tienen ubicación fija pero si supiesemos el número de página podremos consultarlo igual con DBCC PAGE y en el resultado podríamos ver que páginas y extents pertenecen a ese objeto. 

DBCC TRACEON(3604); 
DBCC PAGE(6, 1, NúmeroDePáginaIAM, 3);

Conclusión

SQL Server administra el almacenamiento de manera eficiente utilizando páginas de asignación especializadas como PFS, GAM, SGAM e IAM. Estas estructuras permiten al motor de base de datos optimizar el uso del espacio, minimizar la fragmentación y mejorar el rendimiento. Comprender su funcionamiento es clave para administrar bases de datos de manera óptima y solucionar problemas de asignación de espacio en SQL Server.

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! 

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 1 comentario
17
Feb
2025

Gestión de almacenamiento en SQL Server

¿Alguna vez te has preguntado cómo hace SQL Server para gestionar los datos en disco? Y no sólo SQL Server, la administración eficiente del almacenamiento es fundamental para garantizar un rendimiento óptimo y una gestión eficaz de los datos en cualquier sistema gestor de bases de datos. El motor de base de datos tiene que utilizar estructuras bien definidas, como páginas y extensiones, para organizar y controlar el espacio en disco. Para nosotros, como DBAs o usuarios avanzados, comprender en profundidad estas estructuras y sus mecanismos asociados es esencial para diseñar, implementar y mantener bases de datos de alto rendimiento.

Páginas, la unidad atómica de almacenamiento en SQL Server 

Una página es la unidad básica de almacenamiento en SQL Server, en estas páginas se guardan nuestros datos y tienen un tamaño fijo de 8 KB, siempre tienen el mismo tamaño.

Cada página comienza con un encabezado de 96 bytes que contiene información crucial, como el número de página, el tipo de página, la cantidad de espacio libre y el ID de la unidad de asignación del objeto propietario. Las filas de datos se almacenan secuencialmente después del encabezado, y al final de la página se encuentra una tabla de desplazamiento de filas que facilita el acceso rápido a cada fila.

Tipos de Páginas en función su almacenamiento

SQL Server utiliza diversos tipos de páginas para almacenar diferentes tipos de información.

Aunque el usuario medio podría pensar que todo lo que hay son páginas de datos lo cierto es que este es solo un tipo de página. En concreto, son las páginas que almacenan las filas de datos de las tablas pero, excluyendo los datos de tipos como text, ntext, image, nvarchar(max), varchar(max), varbinary(max) y xml cuando la opción «text in row» está activada. Esos datos veremos que van a otro tipo especial de página.

En concreto a páginas de texto o imagen que son las encargadas de almacenar datos de objetos grandes (LOB), incluyendo text, ntext, image, nvarchar(max), varchar(max), varbinary(max) y xml.

Para cerrar con las páginas que almacenan datos tenemos que nombrar las páginas de índice que contienen esas entradas de índices que facilitan el acceso rápido a los datos.

Pero esto no es todo, no sólo hay páginas con nuestros datos, SQL Server necesita una serie de metadatos para poder gestionar todo esto y los almacena en las páginas de asignación. En este tipo de páginas incluimos las páginas GAM (Global Allocation Map), SGAM (Shared Global Allocation Map), IAM (Index Allocation Map) y PFS (Page Free Space), que gestionan la asignación y el seguimiento del espacio en la base de datos.

Extensiones, como realmente SQL Server gestiona el almacenamiento

Una extensión (lo encontrarás también como extent) es un conjunto de ocho páginas físicamente contiguas, totalizando 64 KB. Las extensiones son la unidad básica mediante la cual SQL Server gestiona el espacio de almacenamiento. 

Permitidme hacer un parón en este punto para interiorizar esto, vuelve a leer el párrafo anterior y grábatelo a fuego así, la próxima vez que tengas que preparar un nuevo disco para almacenar datos de SQL Server entenderás por qué es una buena práctica formatear los discos con un tamaño de asignación de bloques de 64 KB.

Ahora sé, volvemos con los extent y, nuevamente, nos encontramos con que existen varios tipos. En este caso, no dependen del tipo de los datos que estamos almacenando sino de si son o no dedicados para un objeto. 

De esta manera, podemos encontrar extensiones uniformes donde las ocho páginas son propiedad de un único objeto o extensiones mixtas donde las ocho páginas pueden ser compartidas por hasta ocho objetos diferentes, permitiendo una asignación eficiente para objetos pequeños. Es importante destacar que aunque el extent puede ser mixto, las páginas siempre son dedicadas al 100% para el mismo objeto.

Almacenamiento GAM
Almacenamiento SGAM

A partir de SQL Server 2016, veremos que el comportamiento predeterminado es asignar extensiones uniformes para la mayoría de las operaciones. En principio para optimizar el rendimiento pero bueno, eso es un tema discutible. 

Gestión del almacenamiento

Como hemos comentado antes, SQL Server emplea varias estructuras internas (páginas de asignación) para gestionar la asignación y el uso del almacenamiento. Aquí va un pequeño resumen aunque ya os adelanto que a este tema le vamos a dedicar un artículo completo porque tiene mucho de lo que hablar.

Páginas GAM: mapa global de asignación de almacenamiento

Las páginas GAM (Global Allocation Map) registran qué extents han sido asignadas en el archivo de datos. Cada bit en una página GAM representa una extensión: un bit en 1 indica que la extensión está libre, mientras que un bit en 0 señala que está asignada. Cada página GAM puede rastrear aproximadamente 4 GB de datos, cubriendo 64,000 extents.

Páginas SGAM: mapa global de almacenamiento compartido

Las páginas SGAM (Shared Global Allocation Map) indican qué extents mixtos tienen al menos una página disponible para su uso. Un bit en 1 significa que la extensión mixta tiene espacio libre, mientras que un bit en 0 indica que todas sus páginas están ocupadas o que no es una extensión mixta. Al igual que las páginas GAM, cada página SGAM cubre cerca de 4 GB de datos.

Páginas PFS: almacenamiento disponible

Las páginas PFS (Page Free Space) rastrean el estado de cada página individual en términos de asignación y espacio libre. Cada bit en una página PFS representa una página de datos y señala si está libre, parcialmente llena o completamente ocupada. Cada página PFS puede rastrear 8,088 páginas, lo que equivale aproximadamente a 64 MB de datos.

Páginas IAM: Mapa de asignación de índices

Las páginas IAM (Index Allocation Map) mapean las extensiones utilizadas por un objeto específico, como una tabla o un índice, en una unidad de asignación particular. Estas páginas permiten a SQL Server identificar rápidamente qué páginas y extensiones pertenecen a cada objeto, facilitando operaciones eficientes de lectura y escritura.

Consideraciones Avanzadas

Antes de cerrar este artículo quería remarcar unos conceptos sobre los que he podido pasar de manera tangencial antes. 

De cara a entender la compatibilidad con filas largas, aunque una fila de datos no puede dividirse entre múltiples páginas, SQL Server maneja columnas de longitud variable que pueden exceder los 8 KB moviendo parte de los datos a páginas especiales denominadas ROW_OVERFLOW_DATA. Esto permite almacenar filas que, en conjunto, superan el tamaño de una página estándar.

Otra cosa que hemos comentado pero que quería matizar es que en versiones anteriores a SQL Server 2016, las tablas e índices pequeños comenzaban utilizando extensiones mixtas y, al crecer, se les asignaban extensiones uniformes. Desde SQL Server 2016, el comportamiento predeterminado es asignar extensiones uniformes desde el inicio para la mayoría de las operaciones, mejorando el rendimiento en la gestión del almacenamiento. Sin embargo, ahora mismo, con los discos SSD modernos, las lecturas secuenciales ya no representan tanta ventaja como cuando los discos eran mecánicos y esta supuesta ventaja en rendimiento puede ser un problema de desaprovechamiento del almacenamiento. Tampoco es que importe mucho, los discos duros, a la vez que se hacían más rápidos, también han visto crecer su capacidad total y el coste del almacenamiento se ha reducido mucho.

Conclusión

Una comprensión profunda de las estructuras de almacenamiento en SQL Server, incluyendo páginas, extensiones y mapas de asignación, es esencial para administradores y desarrolladores que buscan optimizar el rendimiento y la eficiencia de sus bases de datos. Al dominar estos conceptos, es posible diseñar estrategias de almacenamiento que maximicen el uso de los recursos y aseguren operaciones de base de datos robustas y escalables.

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! 

Publicado por Roberto Carrancio en Rendimiento, SQL Server, 1 comentario
16
Dic
2024

ID autoincrementales, GUID y secuencias: ¿cuál elegir?

ID incrementales o GUID ¿cuál elegir?, esta es la pregunta que me hizo uno de vosotros hace unos días. Y yo también añadiría a la pregunta las secuencias. Vamos a tratar de responder esta duda. 

Cuando diseñamos un modelo de datos en SQL Server o cualquier otro sistema de bases de datos relacional, una de las decisiones más importantes es la elección del tipo de identificador principal para nuestras tablas. ID autoincrementales, GUID y secuencias son opciones comunes, cada una con sus ventajas y limitaciones. En este artículo veremos las características de cada enfoque, sus diferencias y cómo afectan al rendimiento y a la fragmentación de índices para tratar de llegar a la respuesta ideal para cada escenario. Porque sí, como pasa siempre con las soluciones de bases de datos, vais a ver que no existe una respuesta única para todos los escenarios.

IDs autoincrementales

Los ID autoincrementales, conocidos como IDENTITY, son probablemente la solución más utilizada. Se generan de manera automática con cada inserción en la tabla, siguiendo un orden secuencial. Este tipo de identificador es ideal para sistemas centralizados donde no se necesita garantizar unicidad global. Su principal ventaja radica en el consumo reducido de espacio y el bajo impacto en la fragmentación de índices clustered, ya que las inserciones se producen siempre al final del índice.

Lo normal para este tipo de IDs es usar valores numéricos del tipo INT (desde -2.147.483.648 hasta 2.147.483.647) o BIGINT (desde – 9.223.372.036.854.775.808 a 9.223.372.036.854.775.807). Porque sí, los valores negativos también existen y son utilizables.

Sin embargo, los ID autoincrementales no están exentos de problemas. Por ejemplo, en sistemas distribuidos o replicados, la generación secuencial puede llevar a conflictos si diferentes nodos intentan generar los mismos valores. Además, al ser fácilmente predecibles, pueden ser problemáticos desde una perspectiva de seguridad.

GUID: ID con unicidad global 

Los GUID o identificadores únicos globales son valores generados al azar que garantizan unicidad, incluso entre sistemas distribuidos. Esta característica los hace indispensables en escenarios de replicación o cuando los datos se integran desde múltiples orígenes.

El problema de los GUID radica en su tamaño: 16 bytes por registro frente a los 4 u 8 bytes de un INT o BIGINT respectivamente. Esto aumenta significativamente el tamaño de las tablas y los índices y, en consecuencia, el coste de las consultas. Además, su naturaleza aleatoria introduce fragmentación en índices, afectando negativamente al rendimiento en sistemas con altas tasas de inserción.

Para mitigar estos problemas, SQL Server ofrece la función NEWSEQUENTIALID(), que genera GUID en orden secuencial, reduciendo la fragmentación pero sin eliminarla completamente.

Secuencias: ID compartidos

Las secuencias son una alternativa poderosa introducida en SQL Server 2012. Se definen como objetos independientes a las tablas que generan números únicos bajo demanda, ofreciendo un control total sobre cómo se producen los valores. A diferencia de los ID autoincrementales, las secuencias no están ligadas a una tabla específica, lo que las hace reutilizables en múltiples tablas o contextos. Una de sus ventajas clave es la posibilidad de configurarlas para satisfacer requisitos específicos, como usar valores iniciales personalizados o incrementos distintos de uno. Además, permiten generar identificadores únicos en sistemas distribuidos mediante estrategias como prefijos por nodo.

Sin embargo, las secuencias también presentan limitaciones, como la posibilidad de generar brechas en caso de transacciones fallidas y una configuración inicial más compleja que los ID autoincrementales.

Comparativa: ID autoincrementales, GUID y secuencias

A continuación, os muestro una tabla resumen con una comparación detallada de las tres opciones:

 

CriterioAutoincrementalesGUIDsSecuencias
Tamaño4-8 bytes (INT, BIGINT)16 bytes (uniqueidentifier)4-8 bytes (INT, BIGINT)
FragmentaciónBajaAlta (aleatoria)Baja si se utiliza con cuidado
Unicidad globalNoSíSí (configurable)
FlexibilidadBajaAltaMuy alta
DesempeñoAltoMedio-bajoAlto
Compatibilidad distribuidaLimitadaAltaMedia-alta

 

Fragmentación de índices y su impacto

La fragmentación es un factor crucial en el rendimiento de una base de datos. En índices clustered, los valores secuenciales de ID autoincrementales o secuencias generan inserciones ordenadas, minimizando la fragmentación. Por el contrario, los GUID, debido a su naturaleza aleatoria, obligan a reordenamientos constantes en las páginas del índice, aumentando tanto la fragmentación como el coste de mantenimiento.

Para mitigar este problema con GUID, se recomienda usar índices no clustered (no exentos de fragmentación pero con menor impacto) o estrategias como NEWSEQUENTIALID() cuando sea posible. En el caso de secuencias, su comportamiento depende de cómo se configuren, los valores secuenciales preservan el orden, mientras que configuraciones más complejas pueden introducir fragmentación.

 

Conclusión

No hay una única solución ideal; la elección depende del contexto y los requisitos del sistema. Si el rendimiento y el espacio son prioritarios, los ID autoincrementales son la mejor opción en sistemas centralizados. Para entornos distribuidos donde la unicidad global es crucial, los GUID son indispensables, aunque con un coste en rendimiento y espacio. Finalmente, las secuencias ofrecen una alternativa flexible y controlada que puede adaptarse a múltiples escenarios, especialmente cuando se necesita compatibilidad entre tablas o nodos. En última instancia, el éxito radica en comprender las ventajas y limitaciones de cada enfoque, optimizando su uso según las necesidades específicas del proyecto.

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!

Publicado por Roberto Carrancio en Cloud, Rendimiento, SQL Server, 0 comentarios
22
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é.

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!

Publicado por Roberto Carrancio en Cloud, Rendimiento, SQL Server, 0 comentarios
13
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.

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!

Publicado por Roberto Carrancio en Cloud, SQL Server, 0 comentarios
04
Nov
2024
Vuelve SQL (DATA) Saturday a Madrid

Vuelve SQL (DATA) Saturday a Madrid

¡SQL Saturday Madrid está de vuelta, y lo hace con un nuevo nombre y una energía renovada! Después de la gran acogida de su edición anterior, y tras unos años de parón, SQL Saturday Madrid ahora se transforma en Data Saturday Madrid 2024, prometiendo ser el evento más destacado para todos los apasionados de los datos en España.

Este evento, que se celebrará el próximo 30 de noviembre de 2024 en la Universidad Politécnica de Madrid, Campus Sur, no solo retoma la esencia de las pasadas ediciones, sino que amplía su enfoque para cubrir las últimas tendencias y tecnologías del universo de los datos. Con el objetivo de reunir a profesionales y entusiastas del mundo de SQL, Big Data, la Inteligencia Artificial, la Ingeniería de Datos y todo lo relacionado con la plataforma de datos de Microsoft, Data Saturday Madrid viene cargado de novedades y sorpresas para todos los asistentes.

Un evento global para la comunidad de datos

Data Saturday Madrid 2024 es mucho más que un evento local; es parte de la iniciativa internacional Data Saturdays, que surge como una evolución del clásico SQL Saturday. Esta serie de eventos globales tiene como propósito reunir a la comunidad de profesionales de datos en distintos puntos del mundo, brindándoles la oportunidad de aprender, compartir y conectar en torno a las últimas tecnologías y tendencias en el mundo de la gestión de datos.

El evento de Madrid, al igual que otros Data Saturdays en ciudades de todo el mundo, se centra en temas como la ingeniería de datos, Big Data, inteligencia artificial y soluciones en la nube de Microsoft, como Azure y Power BI. Es un espacio que busca impulsar el crecimiento profesional de los asistentes a través de sesiones formativas de alto nivel y talleres prácticos que permiten una inmersión profunda en los conceptos más actuales​

Un evento para todos los niveles y en dos idiomas

Una de las grandes fortalezas de Data Saturday Madrid 2024 es su compromiso con la accesibilidad y la diversidad. Las sesiones y talleres, que se impartirán inglés o en español, están diseñadas para todos los niveles de experiencia, desde aquellos que recién comienzan en el mundo de los datos hasta los expertos que buscan profundizar en temas avanzados. La agenda del evento incluye 33 sesiones en distintos tracks, permitiendo que cada participante personalice su experiencia según sus intereses y necesidades.

Innovación y conocimiento de la mano de expertos

La jornada del sábado promete ser intensa y emocionante, con ponencias de reconocidos MVPs, MCMs, mentores, ingenieros de Microsoft y especialistas técnicos de renombre. Entre los temas que se abordarán, destacan el ecosistema completo de Power BI, la Inteligencia Artificial Generativa (como Copilot y modelos de lenguaje), SQL Server, Azure SQL, Microsoft Fabric, y muchas más tecnologías que integran el ecosistema de datos de Microsoft, como Snowflake y Power Platform​.

Pero eso no es todo. En los días previos, el 28 y 29 de noviembre, se realizarán talleres especializados de cuatro horas, diseñados para profundizar en temas específicos. Estas sesiones prácticas brindan una oportunidad única de aprender de la mano de expertos y explorar a fondo las herramientas y técnicas que están transformando la manera en que gestionamos y analizamos los datos.

Networking, premios y la tradición de #sqlbeers

Más allá del conocimiento técnico, Data Saturday Madrid 2024 también es un punto de encuentro para la comunidad. Después de una intensa jornada de charlas y aprendizaje, los asistentes podrán disfrutar de momentos de networking, incluyendo el clásico DataBeers, donde la comunidad se reúne para compartir experiencias de forma más relajada. Y, como en ediciones anteriores, no faltarán los sorteos de premios, entre los que destaca la codiciada Xbox Series.

Un evento de la comunidad para la comunidad

Este evento, organizado por y para la comunidad, es una oportunidad excepcional para conectar, aprender y compartir con otros profesionales del sector. La pasión y el entusiasmo de los organizadores se refleja en cada detalle, desde la selección de los ponentes hasta la planificación de las actividades paralelas. No importa si eres un veterano de SQL Saturday o si es tu primera vez asistiendo a un evento de este tipo, Data Saturday Madrid 2024 promete ser una experiencia inolvidable.

En resumen, si te apasiona el mundo de los datos y quieres estar al día con las últimas novedades y tendencias, no puedes perderte Data Saturday Madrid 2024. ¡Nos vemos el 30 de noviembre para celebrar el regreso de la comunidad de datos más grande de España, ahora con un nombre renovado, pero con el mismo espíritu de siempre!

Talleres Data Saturday
Cartel Data Saturday

PREGUNTAS FRECUENTES

¿Qué es el Data Saturday?

Es un evento para profesionales y futuros profesionales relacionados con Big Data, Business Intelligence y Artificial Intelligence.

¿Dónde y cuando?

El evento se celebrará los días 28, 29 y 30 de Noviembre en la Escuela Técnica Superior de Ingeniería de Sistemas Informáticos UPM ETSI de Madrid (Calla de Alan Turing s/n , 28031 Madrid).
Los días 28 y 29 están destinados a talleres prácticos con una duración de 4 horas y el sábado 30 a las 33 charlas.

¿Cuánto cuesta?

El evento es gratuito. A la hora de reservar la entrada se pedirá una donación mínima de 1€ que irá íntegramente destinada a una ONG de la zona.
Los workshop (talleres de 4 horas) tienen un coste de 49€.

¿De qué temas se hablarán?

Podremos encontrar sesiones relativas a :
– Power BI (todo el ecosistema, todos los niveles)
– Microsoft Fabric
– Generative AI (Copilot, LLM, RAG, OpenAI)
– Motor relacional (cloud, optimización, bloqueos, índices)
– Azure Data Platform
– Azure Databricks
– Integracion con Snowflake
– Integration Services, Analysis Services, Paginated Reports, Data Warehousing
– Big Data, Python, PySpark
– Artificial Intelligence
– Streaming de datos y arquitecturas Lambda
– Machine Learning
– IoT
– Datos en la nube
– Y muchos más!

¿Cómo puedo participar?

Si quieres es asistir y disfrutar aprendiendo, puedes adquirir tu entrada aquí:
https://www.eventbrite.com/e/registro-data-saturday-madrid-2024-sqlsaturdaymadrid-1037072881907

Publicado por Roberto Carrancio en Otros, 0 comentarios
30
Oct
2024
El problema de Halloween

El problema de Halloween

El problema de Halloween es una anécdota clásica en el ámbito de las bases de datos y la informática, que además de ser técnicamente relevante, tiene una historia curiosa detrás de su descubrimiento. A menudo se lo menciona en el contexto de las transacciones concurrentes y la consistencia en los sistemas de bases de datos relacionales. En este artículo, y ya que mañana celebraremos Halloween, quiero contaros en detalle la historia de este problema, y que veamos cómo eso afectó a los sistemas actuales con los que trabajamos. Sentaos y poneos cómodos amigos que este joven aprendiz de abuelo cebolleta os va a contar una historia de los inicios de las bases de datos relacionales.

Historia del problema de Halloween

Era 1976, y en los laboratorios de IBM en San José, California, un grupo de ingenieros trabajaba en el desarrollo de uno de los primeros sistemas de bases de datos relacionales: System R. Entre ellos estaban Don Chamberlin, Pat Selinger, Raymond Boyce y varios otros miembros de un equipo pionero. Su objetivo era crear un sistema eficiente que pudiera manejar grandes volúmenes de datos con integridad y consistencia, un reto tecnológico sin precedentes en aquella época.

La tarde de un viernes a finales de Octubre, mientras experimentaban con actualizaciones masivas de datos, Don Chamberlin, uno de los co-creadores del lenguaje SQL, notó un comportamiento extraño en una consulta que estaban probando. La consulta parecía simple: querían aumentar el salario de todos los empleados que ganaban menos de 3000 dólares. Así que Don escribió algo parecido a esto:

UPDATE Empleados
SET Salario = Salario + 500
WHERE Salario < 3000;

Esperaba que la consulta incrementara el salario de cada empleado que ganara menos de 3000 dólares, pero lo que vio fue desconcertante. Los sueldos de algunos empleados no solo se habían incrementado una vez, sino varias veces en la misma ejecución. Los salarios de los empleados que inicialmente ganaban 2800 dólares, por ejemplo, terminaron siendo mucho mayores de lo esperado.

¿Qué estaba pasando?

Don llamó a Pat Selinger, la ingeniera encargada de la optimización de consultas en System R, y juntos comenzaron a investigar qué estaba pasando. Descubrieron que el problema radicaba en cómo el motor de la base de datos estaba gestionando el índice de salarios. El sistema estaba utilizando el índice para seleccionar las filas que debían ser actualizadas, pero cada vez que se actualizaba el salario de un empleado, el índice no se actualizaba inmediatamente.

Esto significaba que el motor seguía viendo las mismas filas como si aún cumplieran la condición Salario < 3000, incluso después de que el salario ya hubiera sido incrementado. Así, el sistema volvía a seleccionar esas filas y aplicaba otra actualización, lo que provocaba un aumento repetido e inesperado de los salarios.

Pat, quien ya tenía una gran reputación por su trabajo en la optimización de bases de datos, se dio cuenta de que esto no era un simple error; era un problema fundamental en cómo las operaciones de actualización y selección interactuaban cuando se usaban índices. Rápidamente comprendieron que este problema podía ocurrir en cualquier situación donde los datos modificados afectaran al criterio de selección.

Al darse cuenta de la magnitud del problema, y siendo ya altas horas de la noche, el equipo decidió que no iban a poder dar una solución sencilla y que ya trabajarían en una solución al problema pasadas las fiestas de acción de gracias y Halloween, no sin antes poner un nombre al marrón que acababan de descubrir: “El problema de Halloween”.

Origen del nombre

Como acabamos de ver, el problema fue identificado por Don Chamberlin y otros miembros del equipo de investigación, quienes estaban analizando cómo el índice de salario se comportaba de manera inesperada. Curiosamente, este comportamiento erróneo fue descubierto en octubre de 1976, cerca de la fecha de Halloween. A partir de ese momento, el equipo decidió bautizarlo como el problema de Halloween, en honor a la época del año en que fue detectado.

El descubrimiento marcó un hito en la comprensión de cómo los sistemas de bases de datos podían comportarse de manera incorrecta bajo ciertas condiciones de concurrencia y actualización. Desde entonces, el problema de Halloween se convirtió en un caso de estudio clásico sobre los peligros de las transacciones concurrentes mal gestionadas.

Impacto técnico del problema de Halloween

Ya hemos contado historias, ahora pongámonos técnicos para entender por qué esto si da miedo. El problema de Halloween tiene lugar debido a la interacción entre el acceso a los datos mediante índices y las operaciones de actualización que afectan a los mismos índices. Al realizar una operación como la actualización de un campo indexado, el índice puede cambiar, lo que provoca que el plan de ejecución de la consulta vuelva a seleccionar las mismas filas que ya han sido modificadas. Esto es particularmente peligroso en operaciones que implican modificaciones masivas de registros, como las sentencias UPDATE o DELETE, ya que pueden conducir a un bucle interminable de modificaciones.

Uno de los aspectos más críticos del problema de Halloween es que afecta tanto a la consistencia de los datos como al rendimiento de la transacción. En un escenario en el que no se gestiona correctamente este problema, una operación de actualización podría realizar más cambios de los esperados, lo que lleva a resultados incorrectos. Además, la transacción podría consumir más recursos del sistema de los previstos, lo que degrada el rendimiento general.

Soluciones al problema de Halloween

Con el tiempo, los desarrolladores de motores de bases de datos han ideado varias soluciones para mitigar el problema de Halloween. Desde los bloqueos de claves y operadores de bloqueos de rango hasta el uso de los spools en los planes de ejecución de las consultas.

Bloqueos de claves

El bloqueo de claves es una técnica que asegura que las filas que están siendo leídas o modificadas en una transacción no puedan ser afectadas por otras transacciones concurrentes. En el contexto del problema de Halloween, el bloqueo de claves se utiliza para prevenir que una fila que ha sido modificada vuelva a ser seleccionada por la misma transacción. Pero, ¿Cómo funciona?

Cuando una transacción selecciona una fila basada en un índice, el motor de la base de datos coloca un bloqueo en la clave de índice asociada con esa fila. Este bloqueo persiste hasta que la transacción termina, lo que garantiza que ninguna otra transacción o la misma transacción pueda seleccionar o modificar esa fila hasta que el bloqueo se libere. Esto evita que una fila que ha sido actualizada vuelva a ser seleccionada, ya que la clave correspondiente al índice sigue bloqueada durante la ejecución de la transacción.

Operadores de bloqueos de rango

Los bloqueos de rango (range locks) son otra técnica avanzada que utilizan algunos motores de bases de datos, como SQL Server, para evitar el problema de Halloween. Estos bloqueos no solo afectan a las filas individuales, sino que bloquean un rango completo de valores de un índice para evitar que otras transacciones modifiquen o inserten filas en ese rango.

En lugar de bloquear solo una fila específica, el sistema bloquea un rango de valores en el índice. De esta manera, se garantiza que ninguna otra transacción pueda modificar o insertar nuevas filas en ese rango hasta que la transacción actual se complete.

Los bloqueos de rango previenen que las filas modificadas vuelvan a seleccionarse en la misma transacción y aseguran que las operaciones concurrentes no interfieran con la transacción actual.

Operadores Spool

El spool es un operador que actúa como una especie de «almacén temporal» de los datos que han sido leídos durante la ejecución de una consulta. Su principal función es almacenar los resultados intermedios de una operación, de modo que el motor de base de datos pueda trabajar con ellos sin necesidad de volver a acceder a la fuente de datos original. Esto es particularmente útil en casos donde la selección y la actualización de las filas podrían interactuar de manera perjudicial, como ocurre en el problema de Halloween. Existen varios tipos de spools que se utilizan en SQL Server, no vamos a entrar en detalle ahora pues ya le dedicamos un artículo completo.

Cuando SQL Server detecta que una consulta tiene el potencial de sufrir el problema de Halloween, el optimizador de consultas puede introducir un spool en el plan de ejecución para evitar que las filas modificadas sean seleccionadas de nuevo. El spool actúa como una «instantánea» de las filas seleccionadas al inicio de la transacción, de modo que las actualizaciones no afecten a la selección de filas en curso.

Dicho de otra forma, en un escenario donde SQL Server detecta el riesgo de un problema de Halloween, el plan de ejecución podría incluir un Table Spool para garantizar que las filas se procesen de forma correcta, almacenando temporalmente las filas que cumplen con la condición inicial (por ejemplo Salario < 3000). Una vez que todas las filas han sido seleccionadas, el motor aplica la actualización sin el riesgo de que las filas se vuelvan a seleccionar debido a la modificación del índice.

MVCC, la solución de Oracle

El Control de Concurrencia por Múltiples Versiones (MVCC) es un enfoque utilizado por varios sistemas de bases de datos, como PostgreSQL y Oracle. MVCC permite que múltiples versiones de las filas coexistan, lo que permite que las transacciones lean los datos sin interferir con las modificaciones de otras transacciones y así evitar el problema de Halloween.

Cuando una transacción modifica una fila, se crea una nueva versión de esa fila. Las otras transacciones que están leyendo los datos continúan accediendo a la versión anterior de la fila, mientras que la transacción que la está modificando trabaja con la nueva versión. Esto previene que las modificaciones concurrentes afecten a las lecturas en curso.

De esta manera, MVCC elimina el riesgo de que las filas actualizadas sean seleccionadas de nuevo dentro de la misma transacción, ya que las transacciones no leen las versiones modificadas hasta que estén completamente confirmadas (commit). Esto previene el problema de Halloween sin necesidad de bloqueos explícitos.

Conclusión

El problema de Halloween es un claro recordatorio de cómo los detalles técnicos de la ejecución de consultas y las actualizaciones concurrentes pueden generar comportamientos inesperados en los sistemas de bases de datos. A lo largo de los años, los avances en los motores de bases de datos han permitido mitigar este problema, y uno de los mecanismos más efectivos es el uso de operadores spool en los planes de ejecución.

Aunque el problema de Halloween fue descubierto hace más de cuatro décadas, sigue siendo relevante en los sistemas modernos que manejan grandes volúmenes de datos y transacciones concurrentes. Con el uso de operadores como los spools, los motores de bases de datos garantizan que las actualizaciones se realicen de manera eficiente y sin comprometer la consistencia de los datos, lo que permite a los desarrolladores y administradores de bases de datos centrarse en mejorar el rendimiento y la escalabilidad de sus sistemas sin preocuparse por este problema clásico.

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!

Publicado por Roberto Carrancio en Otros, 0 comentarios