El Sistema Operativo Interno (SQLOS): Planificación Cooperativa Non-Preemptive, Quantum de 4ms y Diagnóstico de Wait Stats

1. Por qué SQL Server Implementa su Propio Planificador de Hilos dentro de Windows/Linux

Para evitar costosos cambios de contexto en modo kernel (Preemptive Context Switches) miles de veces por segundo cuando existen $2,000$ sesiones concurrentes, SQLOS crea un Scheduler lógico por cada núcleo de CPU físico/vCPU y ejecuta una máquina de estados de Multitarea Cooperativa (Non-Preemptive Scheduling) con un Quantum estricto de $4\text{ ms}$:

stateDiagram-v2
    [*] --> RUNNING : Obtiene el CPU Scheduler (1 hilo por Core)
    RUNNING --> SUSPENDED : Espera un recurso I/O o Lock (Wait List)
    SUSPENDED --> RUNNABLE : Recurso disponible (Cola FIFO de CPU)
    RUNNING --> RUNNABLE : Agota su Quantum de 4ms (Yield voluntario -> SOS_SCHEDULER_YIELD)
    RUNNABLE --> RUNNING : Siguiente en la cola FIFO del Scheduler

2. Descomposición Matemática del Tiempo de Espera: Resource Wait vs Signal Wait

Cuando consultas sys.dm_os_wait_stats, el tiempo total acumulado wait_time_ms de un hilo se descompone en dos fases:

$T_{\text{wait\_total}} = T_{\text{resource\_wait}} + T_{\text{signal\_wait}}$

- $T_{\text{resource\_wait}} = \text{wait\_time\_ms} - \text{signal\_wait\_time\_ms}$: Tiempo que el hilo permaneció en la lista SUSPENDED esperando que el disco terminara de leer (PAGEIOLATCH_SH) o que otra transacción liberara un bloqueo de fila (LCK_M_X). - $T_{\text{signal\_wait}} = \text{signal\_wait\_time\_ms}$: Tiempo que el recurso ya estaba listo, pero el hilo permaneció haciendo fila en la cola RUNNABLE esperando que el procesador quedara libre (Presión pura de CPU).

| Tipo de Espera Dominante | Causa Raíz Arquitectónica | Acción de remediación de Nivel Principal | | :--- | :--- | :--- | | SOS_SCHEDULER_YIELD ($> 15\%$ Signal Wait) | Escaneos masivos en memoria RAM (Spinning sobre Buffer Pool por falta de índices cubrientes) | Eliminar Key Lookups / Scans en RAM con índices INCLUDE | | CXPACKET / CXCONSUMER | Paralelismo asimétrico (hilos paralelos esperando a un hilo rezagado con Data Skew) | Ajustar MAXDOP ($le 8$) y Cost Threshold for Parallelism ($ge 50$) | | WRITELOG | Latencia de flush síncrono del Transaction Log (LDF) a almacenamiento | Aislar archivo LDF en NVMe dedicado y evitar micro-commits unitarios |'

Anatomía Física de la Página de 8KB, Mapas de Asignación (PFS, GAM, SGAM) y Erradicación de Contención PAGELATCH en TempDB

1. Estructura Interna de una Página de Datos de 8,192 Bytes y el Peligro de los Forwarding Pointers

Toda página en SQL Server mide exactamente $8,192\text{ bytes}$: - Header ($96\text{ bytes}$): PageID, SlotCnt, FreeCnt, LSN (Log Sequence Number). - Data Rows ($8,060\text{ bytes}$ máx): Filas en estructura FixedVar. - Row Offset Array ($2\text{ bytes}$ por fila al final de la página): Crece de atrás hacia adelante apuntando al byte exacto donde inicia cada fila.

El Desastre de las Tablas Heap (Sin Clustered Index): Si actualizas una columna VARCHAR en un Heap y la fila ya no cabe en su página original, SQL Server mueve la fila a otra página y deja un Forwarding Pointer de $16\text{ bytes}$ en la ubicación original. Los escaneos subsiguientes sufren saltos aleatorios de lectura física.

2. Contención de Metadatos en TempDB (2:1:1 PFS, 2:1:2 GAM, 2:1:3 SGAM)

Cuando cientos de procedimientos almacenados crean y destruyen tablas temporales (#temp) por segundo, todos los hilos compiten por el Latch exclusivo (PAGELATCH_UP / PAGELATCH_EX) sobre la primera página PFS (Page Free Space, página 1), GAM (Global Allocation Map, página 2) y SGAM (Shared GAM, página 3) del archivo tempdb.mdf:

1. Proporcionar $N$ archivos de datos de igual tamaño en TempDB ($N = \min(\text{Cores}, 8)$) para que el algoritmo Proportional Fill reparta las asignaciones en Round-Robin entre múltiples páginas PFS/GAM. 2. Memory-Optimized TempDB Metadata (SQL Server 2019/2022): Migra las tablas del sistema de TempDB a estructuras Latch-Free en memoria (In-Memory OLTP Hekaton).'

Concurrencia sin Bloqueos de Lectura: Read Committed Snapshot Isolation (RCSI) y Accelerated Database Recovery (ADR)

1. Por qué el Modo por Defecto de SQL Server (READ COMMITTED Pesimista) Causa Deadlocks Masivos

Por razones históricas de los años 90, el aislamiento por defecto en SQL Server on-premise adquiere bloqueos compartidos (S-Locks) durante las lecturas: los lectores bloquean a los escritores y los escritores bloquean a los lectores. Activar READ_COMMITTED_SNAPSHOT ON (RCSI) cambia el motor a concurrencia multiversión optimista (MVCC) idéntica a PostgreSQL y Oracle, donde los lectores jamás adquieren S-Locks.

2. Accelerated Database Recovery (ADR) y el Persistent Version Store (PVS)

En bases de datos tradicionales, si una transacción masiva que lleva $2\text{ horas}$ corriendo es cancelada (ROLLBACK), el servidor tarda otras $2\text{ horas}$ deshaciendo registro por registro del log (Long-Running Undo). Con ADR: - Las versiones previas de las filas se guardan en el Persistent Version Store (PVS) dentro de la misma base de datos (no en TempDB). - Un Logical Revert (Rollback) marca la transacción como abortada en la tabla sLog en menos de 1 milisegundo ($O(1)$), y los lectores ignoran instantáneamente sus modificaciones sin esperar el Undo físico.'

Anatomía del Parameter Sniffing, Regresión de Planes en Caché y Parameter Sensitive Plan (PSP) Optimization

1. Cómo una Consulta de 3 Milisegundos Pasa a Tardar 45 Segundos a las 09:00 AM

Cuando un procedimiento almacenado se compila por primera vez tras un desalojo de caché, el optimizador inspecciona (sniffs) el valor del parámetro de esa primera ejecución y usa el histograma de estadísticas (hasta $200$ escalones) para estimar la cardinalidad: - Si la primera llamada fue para un cliente pequeño (@TenantId = 99, $12$ filas estimadas), compila y guarda en caché un plan con Index Seek + Nested Loops (Key Lookup). - Cinco segundos después, un cliente corporativo (@TenantId = 1, $4,000,000$ de filas) reutiliza ese mismo plan cacheado, ejecutando 4 millones de lecturas aleatorias Key Lookup en lugar de un Clustered Index Scan + Hash Match, saturando el CPU al $100\%$.

2. Estrategias Quirúrgicas de Resolución y PSP Optimization (SQL Server 2022)

| Estrategia | Efecto en el Optimizador | Cuándo Usarla | | :--- | :--- | :--- | | OPTION (RECOMPILE) | No guarda el plan en caché; compila un plan a medida con los valores exactos en cada ejecución | Reportes analíticos de baja frecuencia ($< 10\text{ exec/s}$) con distribución altamente asimétrica | | OPTION (OPTIMIZE FOR (@p = val)) | Fuerza a compilar siempre para el valor representativo de alto volumen | Cuando se prefiere un plan estable de Hash Join sin sorpresas de Key Lookup | | PSP Optimization (Compat Level 160) | El motor mantiene hasta 3 variantes de plan activas simultáneamente en caché (Low, Medium, High cardinality buckets) | Automático en SQL Server 2022 para predicados de igualdad con sesgo extremo |

Cálculo de Memory Grants para Sort/Hash, Spills a TempDB y Memory Grant Feedback por Percentiles

1. Las Dos Patologías de una Estimación de Cardinalidad Incorrecta en Memoria de Trabajo

Los operadores bloqueantes como Sort y Hash Match solicitan una reserva fija de RAM (Memory Grant) antes de iniciar su ejecución: 1. Subestimación ($\text{Rows}_{\text{est}} = 100$ vs $\text{Rows}_{\text{real}} = 5,000,000$): El motor reserva solo $1\text{ MB}$ de RAM; en tiempo de ejecución la tabla hash desborda y vuelca gigabytes a TempDB (Sort/Hash Warning Spill), degradando el tiempo de respuesta en dos órdenes de magnitud. 2. Sobreestimación ($\text{Rows}_{\text{est}} = 10,000,000$ vs $\text{Rows}_{\text{real}} = 50$, p. ej. por columnas NVARCHAR(MAX)): La consulta acapara $8\text{ GB}$ de RAM que no usa, dejando a las demás consultas bloqueadas en la espera RESOURCE_SEMAPHORE.

export interface QueryMemoryGrantExecution {
  queryHash: string;
  grantedMemoryKb: number;
  usedMemoryKb: number;
  spilledPagesToTempDb: number;
}
/
 * Emula el algoritmo de Memory Grant Feedback (SQL Server IQP)
 * ajustando la concesión de memoria de la siguiente ejecución.
 */
export function calculateAdjustedMemoryGrantKb(
  exec: QueryMemoryGrantExecution,
  safetyBufferRatio = 1.15
): { action: 'INCREASE_FOR_SPILL' | 'DECREASE_WASTE' | 'KEEP_STABLE'; nextGrantKb: number } {
  if (exec.spilledPagesToTempDb > 0) {
    const spillBytesKb = exec.spilledPagesToTempDb * 8; // 8 KB por página
    const nextGrantKb = Math.ceil((exec.grantedMemoryKb + spillBytesKb) * safetyBufferRatio);
    return { action: 'INCREASE_FOR_SPILL', nextGrantKb };
  }
  const utilization = exec.usedMemoryKb / Math.max(1, exec.grantedMemoryKb);
  if (utilization < 0.5 && exec.grantedMemoryKb > 10240) {
    const nextGrantKb = Math.max(1024, Math.ceil(exec.usedMemoryKb * safetyBufferRatio));
    return { action: 'DECREASE_WASTE', nextGrantKb };
  }
  return { action: 'KEEP_STABLE', nextGrantKb: exec.grantedMemoryKb };
}

Ingeniería de Índices B+Tree, Predicados SARGables, Índices Filtrados y Eliminación de Funciones Escalares (UDF Inlining)

1. Leyes de SARGability (Search Argument Able) y Conversión Implícita de Tipos

Un predicado rompe la capacidad de hacer Index Seek cuando envuelve la columna indexada en una expresión o provoca una Conversión Implícita de mayor precedencia de tipo: - Trampa Clásica VARCHAR vs NVARCHAR: Si la columna dni en la tabla es VARCHAR(20) (ASCII $1\text{ byte/char}$), pero el ORM (Entity Framework / Prisma / JDBC) envía el parámetro como NVARCHAR(20) (Unicode UTF-16 $2\text{ bytes/char}$), dado que NVARCHAR tiene mayor precedencia en la jerarquía de tipos de SQL Server, el motor ejecuta WHERE CONVERT_IMPLICIT(NVARCHAR(20), dni, 0) = @p0, convirtiendo cada fila de la tabla en un Index Scan completo.

2. El Asesino Silencioso: Scalar User-Defined Functions (T-SQL Scalar UDFs)

Antes de Scalar UDF Inlining (SQL Server 2019+), invocar una función escalar dbo.CalcularImpuesto(monto) en un SELECT de $1,000,000$ de filas: 1. Se ejecuta de forma interpretada fila por fila ($1,000,000$ de invocaciones de contexto). 2. Prohíbe por completo el paralelismo en toda la consulta (fuerza MAXDOP = 1). - Solución Arquitectónica: Reescribir siempre como Inline Table-Valued Function (iTVF) invocada con CROSS APPLY, la cual el optimizador expande como una vista en línea permitiendo paralelismo y Batch Mode.'

Arquitectura Interna de Clustered Columnstore: Rowgroups de $1,048,576$ Filas, Deltastore, Delete Bitmap y Tuple Mover

1. Anatomía de un Índice Columnstore en SQL Server

Un índice Clustered Columnstore (CCI) divide la tabla en Rowgroups de hasta $2^{20} = 1,048,576$ filas, donde cada columna se comprime independientemente en Column Segments usando codificación de diccionario, RLE y compresión de bits VertiPaq, logrando reducciones de espacio de $10\times$ frente a tablas B+Tree tradicionales.

flowchart LR
    INS["Bulk Insert >= 102,400 filas"] --> COMP["Compresión Directa a Rowgroup Columnar (COMPRESSED)"]
    SMALL["Trickle Insert < 102,400 filas"] --> DELTA["Deltastore (Árbol B+ Rowstore OPEN)"]
    DELTA -->|Al llegar a 1,048,576 filas| CLOSED["Deltastore CLOSED"]
    CLOSED -->|Tuple Mover / REORGANIZE| COMP
    DEL["Operación DELETE"] --> BMAP["Delete Bitmap (Marca lógica sin descomprimir segmento)"]

2. El Umbral Mágico de Carga Masiva: $102,400$ Filas

- Si un lote BULK INSERT o INSERT INTO ... SELECT contiene $\ge 102,400$ filas, SQL Server comprime los datos directamente en memoria hacia un Rowgroup columnar comprimido sin tocar el Deltastore. - Si el lote tiene $< 102,400$ filas (Trickle Insert), las filas aterrizan en un árbol B+ tradicional llamado Deltastore hasta acumular $1,048,576$ filas.'

Ejecución Vectorizada (Batch Mode de 900 Filas), Rowgroup Elimination y Arquitecturas HTAP Sin ETL

1. De Ejecución Fila-por-Fila (Row Mode) a Ejecución Vectorizada (Batch Mode)

En Row Mode tradicional, los operadores del plan llaman a GetNext() por cada fila individual, incurriendo en millones de llamadas virtuales y fallos de caché de instrucciones de CPU. En Batch Mode, SQL Server procesa vectores contiguos de hasta $900$ filas a la vez que caben íntegramente en la caché L1/L2 del procesador, operando directamente sobre los datos comprimidos con instrucciones SIMD AVX-512 y reduciendo el consumo de CPU entre $5\times$ y $20\times$.

2. Arquitectura HTAP (Hybrid Transactional/Analytical Processing) en Tiempo Real

Al crear un Nonclustered Columnstore Index (NCCI) filtrado con retardo de compresión (COMPRESSION_DELAY = 30 MINUTES) sobre una tabla OLTP crítica B+Tree, las transacciones de alta frecuencia modifican las filas calientes de los últimos $30$ minutos sin fricción de compresión columnar, mientras que los reportes analíticos agregan cientos de millones de filas históricas directamente sobre los segmentos columnares en tiempo real.

Diagnóstico de Fragmentación en Columnstore: Purga de Filas Borradas y Compactación Online

1. Por qué un Índice Columnstore se Degrada con DELETE y UPDATE

En un Columnstore Index: - Un DELETE NO libera espacio físico en el segmento comprimido: solo enciende un bit en el Delete Bitmap. Las consultas analíticas aún deben descomprimir y leer de disco todas las filas borradas para luego descartarlas con el bitmap. - Un UPDATE se ejecuta internamente como DELETE (en el Delete Bitmap) + INSERT (en el Deltastore).

Cuando un Rowgroup acumula más del $15\%$–$20\%$ de filas marcadas en el Delete Bitmap, o existen múltiples Rowgroups recortados (Trimmed Rowgroups con $< 500,000$ filas), ejecutamos en caliente (100% Online):

-- Fuerza la compresión de todos los Deltastores abiertos y fusiona Rowgroups fragmentados/con borrados
ALTER INDEX CCI_FactTransactions ON dbo.FactTransactions
REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

Arquitectura de Alta Disponibilidad: Always On Availability Groups, Synchronous Commit y Read-Only Routing

1. Flujo del Protocolo de Confirmación Síncrona (Log Block Hardening)

En un Always On Availability Group (AG) con réplica secundaria en modo SYNCHRONOUS_COMMIT (para objetivo $\text{RPO} = 0$, cero pérdida de datos): 1. El cliente emite COMMIT en la Réplica Primaria. 2. La Primaria envía el bloque de Transaction Log simultáneamente a su propio disco local (LDF) y por red TCP cifrada hacia la Réplica Secundaria. 3. La Secundaria solo necesita endurecer (Harden) el bloque de log en su propio archivo LDF en disco (aún no necesita terminar el Redo sobre las páginas de datos) y envía el mensaje ACK de vuelta a la Primaria. 4. La Primaria confirma el COMMIT al cliente y enruta todas las consultas de reportería con ApplicationIntent=ReadOnly hacia las réplicas secundarias legibles.

Laboratorio Práctico SQL: Auditoría de Fragmentación de Rowgroups Columnstore y Planificación de REORGANIZE

Contexto del Laboratorio

Administras un Data Warehouse financiero sobre SQL Server Enterprise. La tabla de telemetría columnstore_rowgroup_stats emula la vista del sistema sys.dm_db_column_store_row_group_physical_stats con las columnas: table_name, row_group_id, state_desc ('COMPRESSED' o 'OPEN'), total_rows, deleted_rows y size_in_bytes.

Reglas de Negocio a Consultar

Examen de Certificación: Principal SQL Server Engine & HTAP Architect (DB-801)

Certifica tu dominio de nivel Principal sobre la arquitectura interna de SQLOS, estructura de páginas y TempDB, optimización de consultas T-SQL (Parameter Sniffing / IQP), índices Columnstore y Always On Availability Groups.