SQL Server 2025 Optimized Halloween Protection

Introduzione

SQL Server 2025 introduce importanti innovazioni nell’Engine per migliorare performance e scalabilità. In particolare, la famiglia di funzionalità nota come Intelligent Query Processing si arricchisce di nuove feature in grado di ottimizzare i carichi di lavoro esistenti con uno sforzo di implementazione minimo.

Questo articolo descrive il funzionamento di Optimized Halloween Protection che riduce l’uso del tempdb e migliora le prestazioni delle query grazie a un approccio più efficiente alla gestione del problema di Halloween all’interno dell’Engine.

Questo problema si verifica quando un comando DML modifica i dati in modo tale che la stessa istruzione elabori inaspettatamente una riga più di una volta. L’Engine di SQL Server, da sempre protegge i comandi DML dal problema di Halloween introducendo l’operatore Spool nel piano di esecuzione della query o sfruttando un altro operatore di blocco eventualmente già presente nel piano di esecuzione, come gli operatori Sort o Hash Match.

Se viene utilizzato l’operatore Spool, quest’ultimo crea una copia temporanea (nel tempdb) dei dati da modificare prima che vengano applicate le modifiche. Questa tecnica protegge i dati, evita il problema di Halloween ma presenta alcuni svantaggi:

• Lo spool richiede spazio aggiuntivo nel tempdb, ulteriore I/O su disco e cicli di CPU per scrivere e leggere i dati, e memoria aggiuntiva per memorizzare temporaneamente i dati in cache
• L’operatore Spool blocca l’elaborazione degli operatori a valle che dovranno attendere il completamento della scrittura nello spool
• La gestione dello spool aggiunge complessità al piano di esecuzione, a causa di questo il Query Optimizer può generare un piano di esecuzione non ottimale

Optimized Halloween Protection in SQL Server 2025, elimina questi svantaggi, rendendo superfluo l’uso dell’operatore Spool.

Consideriamo la query seguente che aggiorna la colonna ViewCount della tabella dbo.Posts, del database StackOverflow2010.

UPDATE
  P1
SET
  P1.ViewCount = (P1.ViewCount + 1)
FROM
  dbo.Posts AS P1
WHERE
  (P1.ViewCount > 100000);

La colonna ViewCount è indicizzata (indice non clustered) e, poiché il predicato filtra le righe proprio in base a ViewCount, SQL Server potrebbe utilizzare questo indice per individuare le righe da aggiornare. Durante l’aggiornamento di ViewCount, una o più righe potrebbero spostarsi all’interno delle pagine dell’indice ed essere quindi rilevate più volte (come è successo a Don Chamberlin, Pat Selinger, e Morton Astrahan a metà degli anni ’70 nel giorno di Halloween).

Quando Optimized Halloween Protection non è attivo

Quando Optimized Halloween Protection non è attivo, per evitare il problema di Halloween, SQL Server inserisce l’operatore Table Spool (Eager Spool) nel piano di esecuzione per prevenire aggiornamenti multipli sulla stessa riga. Questo operatore materializza le righe qualificate in una struttura temporanea prima che inizi l’aggiornamento. In questo modo, anche se la posizione di una riga nell’indice dovesse cambiare, essa non verrebbe rielaborata.

La figura seguente illustra il piano di esecuzione della query di UPDATE quando Optimized Halloween Protection non è attivo.

Le statistiche di IO restituiscono le seguenti informazioni.

Table 'Posts'. Scan count 1, logical reads 194622, physical reads 4, page server reads 0, read-ahead reads 118677, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Worktable'. Scan count 1, logical reads 31860, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(15785 rows affected)

 SQL Server Execution Times:
   CPU time = 937 ms, elapsed time = 24726 ms.

Quando Optimized Halloween Protection è attivo

Quando Optimized Halloween Protection è attivo, ogni istruzione in una transazione ottiene un identificatore univoco dell’istruzione, noto come nest ID. Ogni riga modificata viene quindi contrassegnata con il nest ID dell’istruzione. Durante l’elaborazione di un comando DML, quando l’Engine legge i dati, salta qualsiasi riga che abbia lo stesso nest ID del comando DML corrente. Ciò significa che il Query Processor non vede le righe già elaborate dall’istruzione, evitando così il problema di Halloween.

La figura seguente illustra il piano di esecuzione della query di UPDATE quando Optimized Halloween Protection è attivo.

Si noti l’impostazione a True dell’opzione OptimizedHalloweenProtectionUsed tra i dettagli degli operatori Index Update e Clustered Index Update.

Le statistiche di IO restituiscono le seguenti informazioni.

Table 'Posts'. Scan count 1, logical reads 164870, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

(15785 rows affected)

SQL Server Execution Times:
   CPU time = 266 ms, elapsed time = 2342 ms.

L’analisi delle statistiche di runtime e dei piani di esecuzione relativi a due run della stessa query, uno con Optimized Halloween Protection attivo e l’altro senza, mostra che, per questo esempio, l’uso di questa funzionalità elimina la necessità di spazio in tempdb, riduce il tempo di utilizzo della CPU e accorcia il tempo totale di esecuzione; senza modifiche alla query e all’applicazione.

Attivazione di Optimized Halloween Protection

L’attivazione di Optimized Halloween Protection è subordinata ai seguenti prerequisiti:

• Accelerated Database Recovery (ADR) deve essere abilitato a livello di database
• La query deve essere compilata con livello di compatibilità 170 (SQL Server 2025)
• La configurazione a livello di database OPTIMIZED_HALLOWEEN_PROTECTION deve essere abilitata

In SQL Server 2025, l’opzione OPTIMIZED_HALLOWEEN_PROTECTION è abilitata by default, ciò significa che, quando si abilita ADR per un database utilizzando il livello di compatibilità 170, le query nel database iniziano a utilizzare Optimized Halloween Protection by default.

Attivazione di Optimized Halloween Protection:

ALTER DATABASE [CURRENT] SET ACCELERATED_DATABASE_RECOVERY = ON;
ALTER DATABASE [CURRENT] SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = ON;

Disattivazione di Optimized Halloween Protection:

ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZED_HALLOWEEN_PROTECTION = OFF;

Limitazioni

Al rilascio, con la preview di SQL Server 2025, Optimized Halloween Protection presenta alcune limitazioni che si presume vengano ridotte nel corso del tempo. L’elenco aggiornato delle limitazioni in essere è disponibile a questo link. Detto ciò, e considerando che l’attivazione della funzionalità non richiede modifiche al codice delle applicazioni, il consiglio è di valutarne l’attivazione: in molti casi, infatti, può portare miglioramenti significativi nelle performance delle query, soprattutto negli scenari dove la protezione Halloween tradizionale rappresenta un collo di bottiglia.