Incidencias

Mar

2025

¿Reconstruir índices? Quizá ya no tiene tanto sentido como pensábamos

Durante años, una de las tareas de mantenimiento más comunes en nuestros servidores SQL Server ha sido la reconstrucción de índices. La idea de eliminar la fragmentación, mejorar el rendimiento de las consultas y, en ocasiones, recuperar espacio en disco ha estado firmemente arraigada en nuestras rutinas. Sin embargo, la evolución de SQL Server con la introducción de características como Accelerated Database Recovery (ADR) y Read Committed Snapshot Isolation (RCSI) nos obliga a replantearnos si esta práctica sigue teniendo el mismo sentido que antes. En este artículo, basándonos en un experimento que hice recientemente, veremos cómo estas nuevas funcionalidades impactan en la necesidad de reconstruir índices y por qué, en muchos casos, puede que estemos invirtiendo tiempo y recursos de forma innecesaria.

¿Reconstruir índices con ADR? Un nuevo paradigma en la recuperación

Para entender por qué la reconstrucción de índices podría ser menos relevante con ADR, primero debemos recordar cómo funciona esta característica. Sin ADR, cuando modificamos una fila, SQL Server guarda los valores antiguos en el registro de transacciones y actualiza la fila directamente. Si la transacción se revierte, el motor debe recuperar los valores antiguos del registro y aplicarlos de nuevo a la fila. Cuantas más filas se hayan modificado, más tiempo tardará la reversión.

Con ADR, esta operativa cambia radicalmente. En lugar de sobrescribir la fila original, SQL Server escribe una nueva versión de la fila dentro de la misma tabla, manteniendo la versión antigua intacta. Esta estrategia permite que las reversiones de transacciones sean casi instantáneas, ya que no es necesario leer y aplicar información del registro de transacciones.

Como seguramente ya habréis imaginado, almacenar múltiples versiones de una misma fila en la tabla tiene un impacto directo en el consumo de espacio. Para demostrarlo, hace unos días realicé una prueba creando dos bases de datos idénticas, una con ADR habilitado (Test_ADR) y otra sin él (Test), y cargué ambas con un millón de filas en tablas con la misma estructura. Inicialmente, como era de esperar, la tabla con ADR activado (Products_ADR) ocupó más espacio que la tabla normal (Products). Esto se debe a que, de forma similar a RCSI, ADR necesita añadir una marca de tiempo a cada fila para rastrear sus versiones.

¿Reconstruir índices con ADR y RCSI? Un experimento revelador

La primera sorpresa llegó al reconstruir los índices en ambas tablas. Tras la reconstrucción, el tamaño de la tabla Products_ADR, que inicialmente era mayor, se redujo drásticamente hasta igualar el tamaño de la tabla Products. Esto nos plantea una pregunta intrigante: si ADR ya estaba activo al cargar los datos, ¿por qué la reconstrucción de índices liberó tanto espacio? Se podría pensar que las marcas de tiempo de versionado deberían haberse insertado con los datos iniciales, sin causar una fragmentación excesiva.

Repetí este experimento varias veces, incluso en bases de datos con ADR y RCSI activados simultáneamente, y los resultados fueron consistentes. Después de la carga inicial de datos, las tablas con ADR y/o RCSI tendían a ser más grandes. Sin embargo, tras una reconstrucción de índices, todos los tamaños se normalizaban.

La verdadera diferencia se hizo evidente al simular actividad de escritura. Al actualizar un 10% de las filas en todas las tablas, observamos que en la base de datos “normal”, el tamaño de los objetos se mantenía relativamente estable, con un ligero aumento en el índice no clúster de la columna actualizada. Esto es comprensible, ya que las filas modificadas podrían necesitar moverse a nuevas páginas para mantener el orden del índice. No obstante, en las bases de datos con ADR y/o RCSI habilitados, el tamaño de los objetos explotó, llegando casi a duplicarse tras la primera actualización. Al realizar más rondas de actualizaciones, la tendencia se mantuvo: mientras que la base de datos sin ADR crecía de forma gradual, las bases de datos con ADR y RCSI experimentaban un crecimiento mucho más rápido.

¿Por qué crecen las bases de datos con el versionado de filas?

El crecimiento del tamaño de las bases de datos al habilitar funcionalidades como ADR (Accelerated Database Recovery) y RCSI (Read Committed Snapshot Isolation) se debe al mecanismo de versionado de filas, que permite lecturas consistentes sin bloqueos. Sin embargo, aunque la ubicación del almacén de versiones sea la TempDB como con RCSI existe un overhead por fila que explica este aumento de tamaño.

Cuando ADR está habilitado

La Recuperación Acelerada de Bases de Datos utiliza un almacén de versiones persistente (PVS) que se encuentra dentro de la propia base de datos de usuario. Esto significa que las versiones anteriores de las filas modificadas se almacenan en el mismo archivo de datos (.mdf) de la base de datos. Como resultado directo, el tamaño de la base de datos en disco aumenta para albergar estas versiones.

Adicionalmente, cada fila de la tabla contendrá un puntero de 14 bytes que apunta a la ubicación de su versión en el PVS, incluso si la fila no ha sido modificada recientemente. Este overhead por fila es el principal causante del aumento del tamaño de la base de datos.

Cuando RCSI está habilitado (sin ADR)

Si la base de datos tiene habilitado el aislamiento por instantánea de lectura confirmada (RCSI) pero no la Recuperación Acelerada de Bases de Datos (ADR), el almacén de versiones se crea y se mantiene en la base de datos del sistema TempDB. Esto significa que las versiones de las filas modificadas en la base de datos de usuario se almacenan temporalmente en TempDB. Por lo tanto, podriamos pensar que la base de datos de usuario en sí misma debería no experimentar un aumento tan drástico debido al almacenamiento de las versiones, aunque TempDB sí crecerá para acomodar estas versiones.

Sin embargo, al igual que con ADR, cada fila de la tabla en la base de datos de usuario seguirá teniendo el puntero de 14 bytes que apunta al almacén de versiones, aunque en este caso, el almacén esté ubicado en TempDB. Este overhead por fila en la base de datos de usuario hace que el crecimiento que veamos en la tabla sea igual que en las que están en una base de datos con ADR.

Otras funcionalidades afectadas por el versionado de filas

Además de ADR y RCSI que, como acabamos de ver, usan un almacén de versiones, hay más funcionalidades de SQL que lo necesitan. En concreto, las más comunes son las bases de datos secundarias legibles en configuraciones Always On que emplean un almacén de versiones para ofrecer lecturas consistentes en la réplica secundaria.

Otra característica son las vistas indexadas que utilizan el versionado de filas para mantener la consistencia y los Triggers AFTER UPDATE que pueden depender del versionado de filas para acceder a los estados anteriores de las filas modificadas.

En resumen, el crecimiento de las bases de datos con el versionado de filas se debe tanto al almacenamiento de las versiones anteriores de las filas en sí (dentro de la base de datos con ADR, o en TempDB con RCSI) como al overhead de un puntero de 14 bytes añadido a cada fila en la base de datos de usuario para referenciar este almacén de versiones. Es crucial tener en cuenta estas implicaciones de almacenamiento al planificar la implementación de estas funcionalidades.

¿Reconstruir índices para ahorrar espacio? Una ilusión temporal

Ante este crecimiento acelerado de las tablas con ADR y RCSI, la reacción natural sería pensar en la reconstrucción de índices como una solución para recuperar el espacio «perdido». Y, efectivamente, al reconstruir los índices en estas tablas infladas, su tamaño volvía a los valores iniciales, dando la sensación de haber «ahorrado» espacio en disco.

Sin embargo, esta ganancia de espacio es puramente ilusoria y temporal. En cuanto la carga de trabajo habitual se reanudaba y se volvían a realizar actualizaciones, el tamaño de las tablas con ADR y RCSI volvía a inflarse rápidamente. Nos encontrábamos en un ciclo sin fin de crecimiento y reconstrucción, sin abordar la causa fundamental del aumento de tamaño.

La clave para entender esta dinámica reside en la forma en que ADR y RCSI gestionan el versionado de filas. Al mantener las versiones antiguas de las filas modificadas, es inevitable que el espacio ocupado por la tabla crezca con la actividad de escritura. La reconstrucción de índices simplemente reorganiza los datos y elimina las versiones antiguas que ya no son necesarias en el momento de la reconstrucción, pero no evita que se generen nuevas versiones con futuras modificaciones. Por lo tanto, si nuestro objetivo es «ahorrar» espacio mediante la reconstrucción de índices en un entorno con ADR o RCSI, debemos entender que este ahorro será efímero. El espacio «ahorrado» volverá a ser necesario a medida que se generen nuevas versiones de las filas.

¿Reconstruir índices como en 2005? Los tiempos cambian

Esta observación nos lleva a una reflexión importante sobre nuestras prácticas de mantenimiento. Si seguimos reconstruyendo índices como si estuviéramos en 2005, pensando que estamos logrando una mejora significativa en términos de espacio en disco y rendimiento, es hora de detenernos y reconsiderar nuestra estrategia. Las mejores prácticas evolucionan con los nuevos avances de la tecnología.

La evolución de las mejores prácticas nos indica que la obsesión por la utilización del espacio en disco a menudo nos lleva a tratar los síntomas, como la hinchazón de las tablas, en lugar de la causa subyacente, que en entornos con ADR y RCSI es el versionado de filas necesario para su funcionamiento. Reconstruir índices regularmente en estos entornos puede ser una solución ilusoria para el espacio , ya que el espacio ganado se volverá a utilizar rápidamente a medida que la carga de trabajo genere nuevas versiones de las filas.

Incluso podría ser contraproducente a largo plazo si se realiza sin una justificación real de mejora del rendimiento, especialmente considerando la menor penalización por fragmentación en unidades de estado sólido (SSD), que ofrecen tiempos de acceso aleatorio mucho más rápidos que los discos duros tradicionales (HDD). Además en entornos con almacenamiento virtualizado, la contigüidad física de los datos es aún menos común y tiene menos relevancia la fragmentación de los índices.

Casos donde la reconstrucción sí tiene sentido

Existen casos específicos donde la reconstrucción sí tiene sentido, pero son menos comunes. Por ejemplo, cuando se insertan inicialmente filas con muchos valores nulos que posteriormente se actualizan y ya no se modifican. En estos casos una reconstrucción podría compactar las páginas y liberar espacio que ya no es necesario. Sin embargo, en la mayoría de los escenarios con ADR o RCSI habilitados, si nuestro principal objetivo al reconstruir índices es ganar espacio en disco, las ganancias serán en gran medida temporales e insignificantes. Debemos enfocarnos en el problema real que estamos tratando de resolver: ¿es el espacio en disco o el rendimiento de las consultas? En muchos casos, ADR y RCSI están diseñados para mejorar la concurrencia y la disponibilidad, lo que podría reducir la necesidad de reconstrucciones de índices frecuentes con fines de rendimiento, especialmente en combinación con un hardware de almacenamiento adecuado.

Conclusión

Los experimentos que he realizado nos muestran claramente que la reconstrucción de índices en bases de datos con ADR y/o RCSI activados tiene un impacto diferente al que estábamos acostumbrados. Si bien inicialmente puede parecer que recuperamos espacio en disco, este ahorro es fugaz, ya que la propia naturaleza del versionado de filas hará que las tablas vuelvan a crecer con la actividad de escritura.

Es fundamental que nosotros, como profesionales de bases de datos, comprendamos a fondo cómo funcionan estas nuevas características y cómo impactan en nuestras tareas de mantenimiento. En lugar de seguir ciegamente las prácticas del pasado, debemos analizar el problema real que intentamos resolver. Si el aumento de tamaño de nuestras tablas es una consecuencia directa del versionado de filas necesario para ADR y RCSI, quizás la solución no sea reconstruir índices constantemente, sino dimensionar adecuadamente nuestro almacenamiento y enfocar nuestros esfuerzos en otras áreas de optimización.

En definitiva, la llegada de ADR y RCSI nos invita a replantearnos nuestras rutinas de mantenimiento de índices. Entender el mecanismo subyacente del versionado de filas es crucial para tomar decisiones informadas y evitar invertir tiempo y recursos en acciones que nos ofrecen solo una sensación temporal de mejora. La evolución de SQL Server nos exige una evolución en nuestra forma de gestionarlo.

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!

Feb

2025

¿Por qué necesitas usar AUTO_DROP en tus estadísticas?

Hoy vamos a hablar de una de las novedades que nos llegaron con SQL Server 2022 pero, de la que se ha hablado poco y no mucha gente conoce. Estoy hablando de la opción AUTO_DROP de las estadísticas. El objetivo de este artículo es explorar esta característica y entender cómo funciona y por qué es beneficioso para ti.

El reto de las estadísticas manuales

Hubo un tiempo, no tan lejano, donde crear estadísticas manuales era un dolor de cabeza para cualquiera que trabajase con bases de datos SQL Server. Y principalmente era porque las estadísticas manuales eran vinculantes con el esquema. Es decir, si tu o un usuario o aplicación creabais una estadística manual en una tabla, esta estadística iba a bloquear la modificación de la estructura de la tabla. Esto era un problema a la hora de modificar o borrar columnas ya que la sola existencia de la estadística provocaba un error y requería pasos adicionales. Tenías que borrar la o las estadísticas, modificar la tabla y, después, recrear las estadísticas. Por este motivo, con estadísticas manuales, las modificaciones de tabla se convertían en un proceso manual la mayoría de las veces.

Introducción a la opción AUTO_DROP de las estadísticas

Con SQL Server 2022 llegó la opción AUTO_DROP de las estadísticas dispuesta a facilitarnos la vida un poco solucionando, en parte, el problema que mencionaba antes. En resumidas cuentas, cuando habilitamos esta opción para una estadística, esta se crea en un modo que permite que se borre automáticamente cuando se produzca una modificación de la estructura de la tabla.

Características clave de AUTO_DROP

La principal característica de esta funcionalidad es, como hemos visto, que no bloquea la estructura de la tabla. En lugar de eso, la estadística se borra automáticamente cuando es necesario. De esta manera, el comportamiento de las estadísticas manuales se equipara con el de las estadísticas creadas de manera automática por el motor de SQL Server, que también se borran cuando es necesario.

Además, es importante destacar, que esta es una de esas pocas novedades que SQL Server activa por defecto para todas las bases de datos por lo que si quieres mantener el comportamiento como hasta ahora deberás desactivarlo manualmente.

Buenas prácticas y consideraciones

Ten en cuenta que las estadísticas creadas automáticamente por el motor de base de datos siempre usan la opción AUTO_DROP y no se les puede deshabilitar, si intentas cambiarlo te va a dar error. Esta opción solo está disponible para las estadísticas creadas manualmente y, en bases de datos con nivel de compatibilidad 160, estará activada de manera predeterminada, esto aplica para todas las bases de datos creadas en este nivel de compatibilidad pero también para las que hayas migrado de versiones anteriores. Ten en cuenta este comportamiento y desactívalo si lo deseas.

¿Cómo usar la opción AUTO_DROP?

A la hora de crear una estadística manual podemos definir si queremos o no habilitar la opción AUTO DROP. Por ejemplo, este comando crea una estadística con AUTO_DROP:

CREATE STATISTICS [NombreEstadística] ON [Esquema].[Tabla]([Columna1], [Columna2]) WITH AUTO_DROP = ON;

Para crear una estadística SIN AUTO_DROP usaremos este:

CREATE STATISTICS [NombreEstadística] ON [Esquema].[Tabla]([Columna1], [Columna2]) WITH AUTO_DROP = OFF;

Si lo que quieres es cambiar la opción AUTO_DROP en una estadística existente puedes hacerlo también. Esta vez con UPDATE STATISTICS. Por ejemplo este sería el script para activar AUTO_DROP en una estadística que no lo tenga.

UPDATE STATISTICS [Esquema].[Tabla] [NombreEstadística] WITH AUTO_DROP = ON;

Para desactivarlo solo cambia el ON del final por un OFF

Para consultar la configuración AUTO_DROP de nuestras estadísticas podemos hacerlo con la vista sys.stats.

SELECT object_id, [name], auto_drop FROM sys.stats WHERE user_created = 1;

Os comparto también un último script para generar automáticamente estos últimos de cambiar la opción AUTO_DROP para todas las estadísticas de usuario.

SELECT 
  SCHEMA_NAME(t.[schema_id]) AS Esquema,
  t.[name] AS Tabla,
  s.[name] AS NombreEstadistica, 
  CASE WHEN s.auto_drop = 1 THEN 'Activado' ELSE 'DESACTIVADO' END AS [AUTO_DROP],
  'UPDATE STATISTICS '+QUOTENAME(SCHEMA_NAME(t.[schema_id]))+'.'+QUOTENAME(t.[name])+' '+QUOTENAME(s.[name])+' WITH AUTO_DROP = ON;' AS HabilitarAuto_Drop,
  'UPDATE STATISTICS '+QUOTENAME(SCHEMA_NAME(t.[schema_id]))+'.'+QUOTENAME(t.[name])+' '+QUOTENAME(s.[name])+' WITH AUTO_DROP = OFF;' AS DeshabilitarAuto_Drop
FROM sys.stats s
INNER JOIN sys.tables t 
 ON s.object_id = t.object_id
WHERE user_created = 1;

Conclusión

En conclusión, la opción AUTO_DROP de las estadísticas en SQL Server 2022 representa un avance significativo en la gestión de estadísticas manuales. Su implementación permite reducir la fricción en la modificación del esquema de las tablas, eliminando automáticamente las estadísticas cuando ya no son relevantes. Esto no solo simplifica la administración de la base de datos, sino que también evita errores comunes y la necesidad de procesos manuales adicionales.

Si bien esta funcionalidad está activada por defecto en bases de datos con nivel de compatibilidad 160, es importante conocer su impacto y decidir si se desea mantener o desactivar en cada caso. Al final, la correcta gestión de las estadísticas sigue siendo clave para optimizar el rendimiento de las consultas y garantizar un mantenimiento eficiente de nuestras bases de datos en SQL Server.

Feb

2025

El extraño error de conversión y su solución

Hoy vamos con un artículo distinto, nos vamos a centrar en un caso práctico que me ha pasado hoy en el trabajo y que me ha parecido lo suficientemente curioso como para traerlo aquí y compartirlo con todos vosotros. La idea es que si alguna vez encontráis este error o alguno parecido sepáis como actuar rápido y sin volveros locos.

Disclaimer 1: Todos los datos que veis son una reproducción de lo que ha pasado, no voy a mostrar ni estructuras ni datos reales.

Disclaimer 2: Vaya por delante que estamos hablando, no solo de un sistema totalmente Legacy, sino de un patio de recreo para todas las malas prácticas habidas y por haber en el mundo de los datos, documentadas y por descubrir. Vosotros me entendéis, ¿verdad? No sé como más decir que esto hay que borrarlo, os prometo que he recurrido a todos los métodos posibles antes de incurrir en ilícito penal. Porque hay veces que la tortura tendría que estar aceptada, ya veréis cuando os cuente…

El misterioso error

Como os digo estaba yo en un día tranquilo, de esos que aprovechas para poner la documentación al día, cuando los lloros de un usuario han perturbado mi paz. Un error salvaje había aparecido:

Hasta aquí todo normal, un error de conversión normal y corriente. “Deja de hacer esa conversión. ¡ Qué estás tratando de convertir a fecha y hora un texto que no es una fecha, ANIMAL !”

Pero no, iba a ser más complicado, no había ninguna conversión aparente. Era un select * de la tabla sin más.

El valor de la experiencia

Uno que ya es perro viejo ha ido directamente a mirar la definición de la tabla, ya sabes, más sabe el diablo por viejo que por diablo y, aquí, la experiencia es un grado. No es la primera vez que veo una cosa de estas. Y lo que he visto os sorprenderá.

Bingo. Tenemos una columna calculada que saca el dato de un fichero de texto. Seguro que uno de los campos del JSON tiene un valor en la fecha que realmente no es una fecha o, si lo es, no es de un rango válido. Pero, tenemos en la tabla más de 50 millones de registros, no es plan de ponerse a mirar todos los JSON.

Investigación y solución del error

No hay problema que frene al DBA. Localizar el error es sencillo si sabes como. Basta con usar la misma expresión de CONVERT que tenía la columna calculada pero con TRY_CONVERT que, como expliqué en este vídeo, hace lo mismo que CONVERT pero en caso de error devuelve nulo. Esto en un filtro del where y filtrando por resultados nulos nos va a señalar directamente al culpable, como cuando pillan al malo con restos de pólvora en las manos en nuestra serie favorita de policías americanos.

Como veis un 0 en la fecha era el causante de este expediente X, en mi caso real (no la demo que veis en las imágenes) no era uno sino 258 registros pero vamos, la solución es la misma. UPDATE de esas fechas y a funcionar.

Prevención de errores

Una vez arreglado el problema es momento de analizar las causas raíz y ver cómo evitar esto en un futuro. En este caso podríamos resumirlo en hacer las cosas bien pero oye, cuando a uno le están pagando por ello, hay que currarse las respuestas un poco más.

Veamos pues qué pasa:

Usar datos semiestructurados en la base de datos no es una buena idea por rendimiento. Pero es que tampoco tiene validaciones, como hemos podido comprobar. Con una columna de fecha para introducir el dato este error no habría pasado. Directamente este registro incorrecto no se habría escrito en la base de datos.
SQL Server no está preparado para trabajar con JSON, por eso lo del tipo de datos nvarchar(max) en la columna. Mientras que para XML si tenemos un tipo de datos específico para JSON no será hasta SQL Server 2025 que lo veamos (podemos probarlo en preview en Azure SQL Databases y Azure Managed Instance). Este futuro tipo de datos JSON nos permitirá añadir estos controles de los que hablábamos en el punto anterior.
Usar una función CONVERT en una columna calculada es una mala práctica pues, en caso de fallo de los datos, nos devuelve error. Para estos casos, siempre que sea posible es mejor usar TRY_CONVERT. Realmente aquí hay discrepancias de opiniones, y dependerá de vuestro caso. Depende a que deis prioridad, si a tener el resto de datos sin error y el registro incorrecto como nulo o si por el contrario preferís que salte el error para detectarlo y corregirlo.

Conclusión

Los errores de conversión como este pueden ser una pesadilla, pero la realidad es que suelen ser más culpa de un diseño regulero que de un usuario despistado. Aquí la clave es sencilla: si metemos datos como si fueran churros, que no nos sorprenda si luego nos encontramos un «churro» en los resultados. Por otro lado, usar TRY_CONVERT en lugar de CONVERT nos habría ahorrado el susto, pero el problema de fondo sigue siendo el mismo: SQL Server y JSON no son precisamente mejores amigos.

Aquí estamos, esperando que el tipo de datos JSON nativo llegue en SQL Server 2025. Hasta entonces, toca ser cuidadosos, validar lo que metemos en la base de datos y asumir que, si confiamos ciegamente en los datos, tarde o temprano nos van a dar un disgusto.

Así que ya sabéis: menos improvisación, más validación y, sobre todo, menos sustos en producción.

Ene

2025

Un día en la vida de un DBA: Consultas, Caos y Dudosa Capacidad Técnica (Artículo de HUMOR)

Como DBA de SQL Server, mi día es una mezcla de comedia, drama y suspense, con algunos toques de terror. Aquí os presento cómo transcurrió un día cualquiera en mi vida, repleto de personajes inolvidables, cada uno con su consulta particular y su manera única de contribuir al colapso del servidor. Abrid vuestra mente… y vuestro SQL Server Management Studio.

8:00 AM: Paco “El SELECTor” y la Consulta Infinita

Nada más abrir mi sesión, mi querido compañero Paco, el SELECTor (porque selecciona todo sin filtro), aparece con su ya clásica sonrisa de inocencia. Paco es de los que todavía piensan que SQL Server es algo mágico y que “si no devuelves todas las columnas, seguro te dejas algo importante”.

Me encuentro con su obra maestra, digna de un Anti Premio a la peor consulta del año:

SELECT *
FROM Sales.SalesOrderDetail SOD
JOIN Production.Product P ON SOD.ProductID = P.ProductID
WHERE YEAR(SOD.ModifiedDate) = 2024;

-“¡No entiendo por qué tarda tanto! ¡Si son solo los pedidos del año!”, exclama Paco, mientras agita su taza de café de “No es magia, soy desarrollador”.

Le explico con calma (una vez más) que cada vez que usa SELECT * en producción muere un gatito y que filtrar con funciones como YEAR() no permite que SQL Server use los índices. Con cara de «eso me lo enseñaron en un curso de YouTube», Paco se compromete a mejorar. Mientras tanto, optimizo la consulta:

Optimización: índice + eliminación de funciones

CREATE INDEX IX_SalesOrderDetail_ModifiedDate 
ON Sales.SalesOrderDetail(ModifiedDate)
WITH (ONLINE=ON, FILLFACTOR=100);

SELECT SOD.SalesOrderID, P.Name, SOD.OrderQty
FROM Sales.SalesOrderDetail SOD
JOIN Production.Product P ON SOD.ProductID = P.ProductID
WHERE SOD.ModifiedDate >= '2024-01-01' AND SOD.ModifiedDate < '2025-01-01';

Resultado: Consulta en segundos. Paco asiente con admiración y anota algo en su libreta, probablemente «Pedir más memoria al DBA».

10:00 AM: Toñi “SinWHERE” y el Script Nuclear

Toñi es de esas desarrolladoras que tienen una relación tóxica con el entorno de producción. Tiene un lema: «Si funciona en mi local, funciona en producción». Y hoy, decidió demostrarlo.

Recibo una alerta: «La tabla Employee está vacía». Con el corazón encogido, abro los logs y ahí está el script más temido:

DELETE FROM HumanResources.Employee;

-“¡Uy! Me olvidé el WHERE. Pero no pasa nada, para eso están los backups, ¿no?”, dice Toñi con una sonrisa que desearía no ver nunca más.

Intento explicarle que “no pasa nada” es lo que dice un soldado cuando pisa una mina. Mientras recupero los datos, aplico medidas preventivas:

ALTER ROLE db_datawiter DROP MEMBER [Toñi.SinWHERE]

ATPC. No me fío ni un pelo.

1:00 PM: Manolo “El Shuffle” y el Caos Aleatorio

Manolo, alias “El Shuffle”, es el encargado de los informes y estadísticas. Es fan del “orden aleatorio” porque, según él, “las listas aburren a la gente”. Y claro, cuando quiere algo “aleatorio”, lanza lo siguiente:

SELECT TOP 10 PERCENT *
FROM Sales.Customer
ORDER BY NEWID();

-“Es que así queda guay, aleatorio, como mi lista de Spotify”, argumenta Manolo mientras hace girar su silla de oficina cual plato de mesa de mezclas de DJ.

Le explico (otra vez) que NEWID() obliga a SQL Server a generar GUIDs para todas las filas de la tabla y luego ordenarlos. Todas estas operaciones se hacen en memoria y con su maravillosa tabla de 200 millones de registros eso no es una buena idea. Así que le doy una alternativa más eficiente para obtener una muestra aleatoria:

SELECT *
FROM Sales.Customer
TABLESAMPLE (10 PERCENT);

¡Magia! “¿Ves? Es que tú lo complicas todo”, dice con su típica confianza de «yo lo leí en Stack Overflow».

4:00 PM: Javi “El SinÍndices” y la Carga Masiva

Cuando el servidor ya está medio recuperado, aparece Javi, “El SinÍndices”. Su teoría es que los índices ralentizan la carga de datos (y no le falta razón… a medias). Así que en su infinita sabiduría, ha borrado todos los índices de la tabla Invoices para cargar más rápido.

– “¡La carga ha ido como un rayo!”, dice orgulloso.

– “¿Y las consultas que dependen de esa tabla? ¿Les has preguntado cómo van?”, respondo yo con una mirada de fuego.

Para evitar futuros desastres, le tatuo en el antebrazo que si vuelve a borrar índices no respetaré los derechos humanos y recurriré a la tortura en nuestra próxima interacción.

6:00 PM: El Jefe “Sin Backup no Hay Paraíso”

Justo cuando creo que puedo irme, mi jefe aparece con cara de preocupación:

– “¿Tenemos backup de todo esto, verdad?”.

Le muestro mi carpeta de backups, replicados hasta en la luna si es necesario. Porque si algo tengo claro después de años de sufrimiento es que sin backup, no hay DBA que sobreviva.

Conclusión: La vida del DBA es un Reality Show

Un día más salvando a Paco, Toñi, Manolo y Javi de ellos mismos. Un día más optimizando consultas, corrigiendo scripts desastrosos y asegurando que el servidor no arda en llamas. La próxima vez que escuches «¿pero qué hace un DBA?», recuérdales que sin nosotros, el caos reinaría en el mundo de los datos.

Y si alguno de estos personajes te suena… ¡ánimo! Seguro que en tu día a día también te encuentras con alguno de ellos.

Espero que este artículo te haya resultado divertido y ameno. Si tienes alguna duda o comentario, no dudes en contactarnos en Twitter o por mail o dejarnos un mensaje en los comentarios de aquí abajo. Y recuerda que también tenemos un grupo de LinkedIn al que te puedes unir.

Dic

2024

Always On y el mito de la perdida de datos cero

Cuando hablamos de Alta Disponibilidad en SQL Server, los Grupos de Disponibilidad Always On suelen ser la opción que se menciona con mayor frecuencia. No es para menos, realmente son la solución de alta disponibilidad más completa que ofrece SQL Server. Sin embargo, existe una idea errónea generalizada: que el modo sincrónico de AlwaysOn garantiza la pérdida cero de datos. A primera vista, esta suposición puede parecer razonable, pero en este artículo explicaré por qué no es necesariamente cierto y analizaremos las implicaciones técnicas detrás de esta afirmación.

El mito de la pérdida cero de datos en Always On

El modo sincrónico en los Grupos de Disponibilidad AlwaysOn está diseñado para garantizar que los datos se escriban en todas las réplicas sincrónicas antes de confirmar una transacción. Esto implica que las transacciones no se considerarán completadas hasta que los datos se escriban tanto en la réplica principal como en las secundarias configuradas en modo sincrónico. A simple vista, parece que este comportamiento elimina cualquier posibilidad de pérdida de datos, pero hay ciertos escenarios en los que esto no es así.

Cómo funciona el Always On en modo síncrono

En el modo síncrono, el proceso sigue estos pasos:

El nodo primario recibe una transacción.
Los datos de la transacción se envían a todas las réplicas secundarias configuradas en modo sincrónico.
Las réplicas secundarias confirman que los datos han sido escritos en su registro de transacciones (log).
Solo después de recibir las confirmaciones de todas las réplicas, el nodo primario completa la transacción. Realmente esto se puede ajustar para que no sea necesario esperar a todas las replicas secundarias con la opción REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT.

Aunque este flujo parece muy robusto, hay ciertas limitaciones y condiciones que pueden comprometer la integridad de los datos.

Excepciones

Todo esto suena muy bonito, precioso diría yo. Y así funciona realmente, excepto cuando algo no va bien. Si la réplica secundaria deja de estar disponible, llámalo reinicio, parcheo o cualquiera de los múltiples otros motivos que puedan surgir dejamos de tener alta disponibilidad. Realmente está contemplado, mirad. Si leemos la documentación nos encontramos con algo que ya no suena tan bien:

«Si la réplica principal y una réplica secundaria determinada se configuran ambas para el modo de confirmación sincrónica, la réplica principal espera a que la réplica secundaria confirme que ha reforzado el registro (a menos que la réplica secundaria no pueda hacer ping a la réplica principal en el período de tiempo de espera de sesión de la principal).
Si el período de tiempo de espera de sesión de la réplica principal es superado por una réplica secundaria, la replicación principal pasa temporalmente al modo de confirmación asincrónica para esa replicación secundaria. Cuando la replicación secundaria vuelva a conectarse con la replicación primaria, se reanuda el modo de confirmación sincrónica.«

En resumidas cuentas, y tiene hasta sentido, si la réplica secundaria no está disponible, por el motivo que sea, las transacciones no se detendrán y seguiremos trabajando con normalidad sobre la réplica principal sin notar nada pero no tendremos alta disponibilidad. Cuando la réplica secundaria nuevamente esté disponible empezará a replicar todas las transacciones pendientes y, un failover, antes de que termine, tendrá pérdida de datos.

Casos prácticos donde puede ocurrir pérdida de datos con Always On

Hemos nombrado ya alguno de los escenarios en los que podríamos tener una pérdida de datos con Always On, pero hay más, estos son los más comunes.

Latencia de red alta: Si la red entre las réplicas tiene una latencia significativa, puede aumentar la probabilidad de inconsistencias. En casos extremos, una réplica secundaria podría quedar rezagada y, como dice la documentación, pasar a modo asíncrono hasta que se recupere la normalidad.
Fallos simultáneos en nodos múltiples: En un entorno de clúster, si tanto el nodo primario como las réplicas sincrónicas fallan al mismo tiempo (por ejemplo, por un corte de energía en el data center), se pueden perder datos que no hayan sido escritos en disco.
Problemas en el subsistema de almacenamiento: Si el almacenamiento subyacente es compartido para todos los nodos y experimenta corrupción o retrasos significativos, incluso las transacciones confirmadas podrían estar en riesgo.

Prácticas recomendadas en Always On para mitigar riesgos

Si bien sabemos que no es teóricamente imposible la pérdida de datos, también existen una serie de medidas que, como DBAs, podemos tomar para reducir el riesgo. La primera y más eficaz es configurar múltiples réplicas síncronas. Tener más de una réplica puede reducir las probabilidades de pérdida de datos, ya que sería improbable que todas las réplicas fallen simultáneamente. Recuerda que Always On admite un total de 8 réplicas secundarias.

Las siguientes medidas, aunque imprescindibles, no van a tener un impacto tan directo en la reducción del riesgo, simplemente nos permitirán localizar el problema y tomar medidas antes de que sea tarde. Como habrás adivinado ya, estoy hablando de monitorizar la latencia de replicación: Es crucial monitorizar continuamente la latencia entre el nodo primario y las réplicas y tener un buen sistema de alerta para detectar problemas potenciales. También deberemos realizar pruebas regulares de failover: Realizar pruebas regulares ayuda a garantizar que los nodos secundarios estén configurados correctamente y puedan asumir el rol de primario sin perder datos.

Por último, pero no menos importante deberemos tener una solución de respaldo complementaria. Aunque los Grupos de Disponibilidad AlwaysOn son poderosos, una estrategia de copias de seguridad sólida sigue siendo indispensable. No solo para afrontar fallos de la infraestructura, también porque un borrado o actualización incorrecta se replicará inmediatamente por todas las réplicas y las copias de seguridad serán lo único que nos salve.

Conclusión

Los Grupos de Disponibilidad Always On son una solución robusta para alta disponibilidad y recuperación ante desastres. Sin embargo, como hemos visto, el modo sincrónico no es una garantía absoluta de pérdida cero de datos. Comprender estas limitaciones y diseñar una arquitectura con redundancias adicionales es fundamental para minimizar riesgos y garantizar la integridad de los datos. Siempre debemos complementar nuestras configuraciones con monitorización proactiva, pruebas de failover y estrategias de respaldo adecuadas.

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.

Incidencias

Always On y el mito de la perdida de datos cero

El mito de la pérdida cero de datos en Always On

Cómo funciona el Always On en modo síncrono

Excepciones

Casos prácticos donde puede ocurrir pérdida de datos con Always On

Prácticas recomendadas en Always On para mitigar riesgos

Conclusión

NO TE PIERDAS NADA

Colabora con nosotros

Entradas recientes

Etiquetas

Comentarios recientes