Identificar el proceso de limpieza de Query Store con sp_WhoIsActive

En entornos de producción donde SQL Server actúa como motor principal para modelos de Power BI o arquitecturas de Microsoft Fabric, el rendimiento de las consultas es crítico. Query Store se ha convertido en una herramienta indispensable para monitorizar planes de ejecución y aplicar el ajuste automático en SQL Server: FORCE_LAST_GOOD_PLAN en la práctica. Sin embargo, como cualquier sistema de telemetría, tiene un coste operativo: la limpieza y el mantenimiento de los datos capturados.

Cuando un servidor experimenta picos de I/O o bloqueos inesperados durante las ventanas de carga de datos, es habitual recurrir a sp_WhoIsActive, el estándar de la industria desarrollado por Adam Machanic. El problema surge cuando vemos sesiones del sistema que no se identifican claramente. Identificar qué SPID (Server Process ID) pertenece a la tarea de limpieza de Query Store (Cleanup) es vital para no diagnosticar erróneamente un problema de aplicación cuando, en realidad, estamos ante un proceso de mantenimiento interno que puede ser optimizado o reprogramado.

Como consultor, he visto proyectos de retail e industria donde el refresco de un informe de Power BI fallaba por un timeout debido a que la limpieza de Query Store coincidía con la reconstrucción de índices o la carga de grandes volúmenes de ventas. Si no sabes identificar este proceso, podrías perder horas intentando optimizar una consulta DAX o un paso de Power Query que no es el culpable real del bloqueo en origen.

¿Por qué es difícil identificar el Cleanup de Query Store?

Por defecto, sp_WhoIsActive filtra los procesos del sistema para centrarse en la actividad del usuario. Incluso cuando activamos el parámetro @show_system_spids = 1, la información devuelta para las tareas de fondo de Query Store suele ser críptica. A diferencia de un proceso de usuario donde vemos la sentencia T-SQL exacta (como un SELECT sobre la tabla de Ventas), los procesos de mantenimiento de Query Store a menudo aparecen con comandos genéricos o sin una consulta SQL asociada en el búfer de entrada.

Este proceso es responsable de purgar datos antiguos basándose en la configuración de MAX_STORAGE_SIZE_MB y CLEANUP_POLICY. Si tu base de datos genera muchos planes de ejecución únicos (algo común en informes de Power BI con filtros dinámicos que no usan parametrización), el proceso de limpieza trabajará de forma mucho más agresiva.

El papel de sys.dm_exec_requests

Para identificar el SPID de limpieza, debemos mirar más allá de lo que ofrece sp_WhoIsActive por defecto. La clave reside en la columna command de la vista de sistema sys.dm_exec_requests. Mientras que las tareas de usuario muestran ‘SELECT’, ‘INSERT’ o ‘EXECUTE’, las tareas internas de Query Store se identifican por comandos específicos que el motor de SQL Server reserva para ellas.

-- Consulta para identificar procesos de Query Store activos
SELECT 
 session_id,
 status,
 command,
 wait_type,
 last_wait_type,
 cpu_time,
 total_elapsed_time
FROM sys.dm_exec_requests
WHERE command LIKE '%QDS%'; -- Filtramos por Query Data Store

Uso avanzado de sp_WhoIsActive para tareas del sistema

Para un consultor de BI, la visibilidad total es innegociable. No basta con saber que el servidor está lento; necesitamos saber exactamente qué está haciendo. Al ejecutar sp_WhoIsActive, debemos combinarlo con metadatos adicionales para confirmar que ese SPID con alto consumo de CPU o I/O es efectivamente el de limpieza.

Si observas un SPID que persiste y cuya columna de comando indica QDS_CLEANUP_STALE_QUERIES o QDS_PERSIST_TASK_MAIN_LOOP_SLEEP, has encontrado al responsable. Es fundamental entender que este proceso, aunque necesario, puede competir por recursos con tus procesos de extracción de datos (ETL). Si tu infraestructura de datos depende de la frescura de la información, como ocurre en escenarios de frescura de datos en Microsoft Fabric, un bloqueo por mantenimiento de Query Store puede degradar la experiencia del usuario final.

-- Ejecución recomendada para diagnóstico profundo
EXEC sp_WhoIsActive 
 @show_system_spids = 1, 
 @get_additional_info = 1, 
 @get_task_info = 2; -- Nivel 2 para obtener detalles de I/O por tarea

Impacto en la producción y toma de decisiones

Identificar el proceso es solo el primer paso. La verdadera labor del consultor senior es decidir qué hacer al respecto. En mis proyectos en el sector público y de energía, donde las bases de datos pueden ser masivas, la limpieza automática de Query Store a veces se vuelve contraproducente si no está bien configurada.

Si detectas que el proceso de limpieza se activa con demasiada frecuencia o consume demasiados recursos de I/O (similar a lo que ocurre con los checkpoints en SQL Server), tienes tres caminos principales:

1. Aumentar el tamaño máximo: Si el almacenamiento lo permite, incrementar MAX_STORAGE_SIZE_MB reduce la frecuencia con la que el proceso de limpieza debe ejecutarse para liberar espacio.
2. Ajustar el intervalo de captura: Cambiar el DATA_FLUSH_INTERVAL_SECONDS para que SQL Server no escriba en disco constantemente los datos de Query Store.
3. Limpieza manual: En casos extremos, ejecutar ALTER DATABASE [TuDB] SET QUERY_STORE CLEAR; durante una ventana de mantenimiento programada para evitar que el motor lo haga de forma automática en horas punta.

Comparativa de procesos de mantenimiento en SQL Server

Es común confundir la limpieza de Query Store con otras tareas automáticas. La siguiente tabla ayuda a diferenciar los comportamientos que verás en sp_WhoIsActive:

Errores frecuentes al gestionar Query Store

• Ignorar el modo READ_ONLY: Si la limpieza falla repetidamente por falta de espacio en disco o contención, Query Store puede pasar a modo READ_ONLY. En este estado, dejas de capturar regresiones de rendimiento críticas.
• Configurar un límite de almacenamiento demasiado bajo: En modelos de Power BI complejos con muchas subconsultas, el volumen de planes de ejecución es enorme. Un límite de 250MB es insuficiente para la mayoría de entornos DWH modernos.
• No monitorizar los tiempos de espera (Wait Types): Si ves QDS_ASYNC_QUEUE_FULL, significa que el sistema no puede seguir el ritmo de las consultas que intentas monitorizar. Antes de tocar el código DAX, revisa la infraestructura SQL.

Al igual que ocurre al leer planes de ejecución en SQL Server para identificar operadores costosos, el análisis de las tareas del sistema requiere una mirada analítica. Si una consulta utiliza un OR en el WHERE de forma ineficiente, Query Store lo registrará, pero si el propio proceso de registro está bloqueando al sistema, el diagnóstico será erróneo.

Conclusión y Checklist de acción

Identificar qué SPID es el Cleanup de Query Store con sp_WhoIsActive es una habilidad táctica que separa a un desarrollador de un consultor senior. No permitas que procesos de mantenimiento opacos afecten a los SLAs de tus informes de Power BI.

Sigue este checklist cuando sospeches de problemas de rendimiento causados por Query Store:

• Ejecuta sp_WhoIsActive @show_system_spids = 1 y busca comandos que contengan «QDS».
• Verifica el estado de Query Store con sys.database_query_store_options para ver si está en modo READ_WRITE.
• Comprueba si el tamaño actual se acerca al max_storage_size_mb.
• Analiza si los picos de CPU coinciden con la tarea QDS_CLEANUP_STALE_QUERIES.
• Si el impacto es inasumible, ajusta la política de retención de 30 días a un valor inferior o aumenta el umbral de almacenamiento.

Preguntas frecuentes

¿Puedo matar el SPID de limpieza de Query Store si bloquea mi carga ETL?

No se recomienda matar procesos del sistema manualmente. Si lo haces, SQL Server volverá a iniciarlo casi de inmediato. Lo ideal es ajustar la configuración de la base de datos para que la limpieza sea menos agresiva o ampliar los recursos de almacenamiento.

¿sp_WhoIsActive muestra el consumo de memoria de la limpieza de Query Store?

Sí, si usas el parámetro @get_task_info = 2, podrás ver la memoria concedida y utilizada, además de las operaciones de lectura y escritura. Esto es fundamental para distinguir si el cuello de botella es de disco o de memoria RAM.

¿Por qué mi Query Store se llena tan rápido?

Suele deberse a la falta de parametrización en las consultas que llegan desde Power BI u otras herramientas. Cada consulta con un valor de filtro distinto se trata como un plan único, llenando rápidamente el almacenamiento disponible y forzando al proceso de limpieza a trabajar constantemente.