SQL Server 2025 Optional Parameter Plan Optimization

Breaking-News, Rilasci
Sergio Govoni

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 Optional Parameter Plan Optimization (OPPO) che risolve una variante del problema che affligge i piani di esecuzione sensibili ai parametri. Parameter Sensitive Plan Optimization (PSPO), a partire da SQL Server 2022, permette di avere nella plan cache più piani di esecuzione attivi per una singola query parametrizzata; Optional Parameter Plan Optimization (OPPO) interviene, nelle query multi-plan, quando il valore sensibile del parametro presente durante l’esecuzione della query determina se è necessario eseguire una ricerca o una scansione di una tabella.

Consideriamo la seguente stored procedure che interroga la tabella dbo.Posts, del database StackOverflow2010, per alcuni dei suoi counter. Le colonne AnswerCount e CommentCount sono indicizzate.

CREATE OR ALTER PROCEDURE dbo.SearchPostsCounts
  @NAnswerCount INTEGER = NULL
  ,@NCommentCount INTEGER = NULL
AS
  SELECT
    Body
    ,AnswerCount
    ,CommentCount
  FROM
    dbo.Posts
  WHERE
    ((AnswerCount = @NAnswerCount)
     OR (@NAnswerCount IS NULL))
    AND
    ((CommentCount = @NCommentCount)
     OR (@NCommentCount IS NULL));

Eseguiamo la stored procedure per cercare i post che hanno AnswerCount uguale a 20.

-- Search posts for AnswerCount
EXECUTE dbo.SearchPostsCounts
  @NAnswerCount = 20
  ,@NCommentCount = NULL;

Quando Optional Parameter Plan Optimization non è attivo

Quando Optional Parameter Plan Optimization non è attivo, nei casi come questo, SQL Server sceglie sempre un piano di esecuzione che esegue la scansione della tabella, anche se è presente un indice che può supportare il predicato tipo ((Column = @P1) OR (@P1 IS NULL)). Un piano esecuzione che effettua una Seek sull’indice potrebbe non essere possibile con valori NULL del parametro. Anche l’utilizzo di OPTIMIZE FOR potrebbe non essere efficace per piani di esecuzione di questo tipo, sensibili ai parametri.

Attualmente non esiste un operatore in grado di convertire dinamicamente una ricerca puntuale sull’indice (Index Seek) in una scansione (Index Scan), e se anche ci fosse potrebbe non essere efficace perché le stime di cardinalità su tale operatore sarebbero probabilmente imprecise. Per questi tipi di query, il risultato è la scelta di un piano di esecuzione non ottimale che può consumare anche ingenti quantità di memoria per le query più complesse.

La figura seguente illustra il piano di esecuzione scelto per la stored procedure dbo.SearchPostsCounts quando Optional Parameter Plan Optimization non è attivo e viene passato un valore NOT NULL nel parametro @NAnswerCount.

Le statistiche di IO restituiscono le seguenti informazioni.

(502 rows affected)

Table 'Posts'. Scan count 3, logical reads 8593, physical reads 0, page server reads 0, read-ahead reads 6452, page server read-ahead reads 0, lob logical reads 21, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Quando Optional Parameter Plan Optimization è attivo

Quando Optional Parameter Plan Optimization è attivo può essere sfruttata l’infrastruttura multi-piano (adaptive plan optimization) introdotta con Parameter Sensitive Plan Optimization che genera più piani di esecuzione per una singola query. Questo consente di effettuare valutazioni diverse a seconda dei valori dei parametri usati nella query, viene quindi selezionato il piano di esecuzione più appropriato con queste possibilità:

  • Per valori NOT NULL del parametro, query optimizer sceglierà tipicamente una scansione dell’indice (seek plan) o in generale un accesso più ottimizzato rispetto ad una scansione totale della tabella

  • Per valori NULL del parametro verrà effettuata una scansione della tabella

La figura seguente illustra il piano di esecuzione scelto per la stored procedure dbo.SearchPostsCounts quando Optional Parameter Plan Optimization è attivo e viene passato un valore NOT NULL nel parametro @NAnswerCount.

Le statistiche di IO restituiscono le seguenti informazioni.

(502 rows affected)

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

In questo esempio, l’attivazione di Optional Parameter Plan Optimization ha ridotto di ¼ le pagine di memoria lette per la tabella dbo.Posts. Optional Parameter Plan Optimization fornisce quindi una soluzione ottimizzata alle ricerche dinamiche il cui predicato è espresso nella forma:

WHERE
  ((Column1 = @P1) OR (@P1 IS NULL))
  AND ((Column2 = @P2) OR (@P2 IS NOT NULL))

Attivazione di Optional Parameter Plan Optimization

L’attivazione di Optional Parameter Plan Optimization è a livello di database ed è subordinata alla presenza di livello di compatibilità 170 (SQL Server 2025) sul database.

Optional Parameter Plan Optimization è attivo by default quando il compatibility level del database è impostato a 170, default per SQL Server 2025.

Il compatibility level di SQL Server 2025 è prerequisito per l’attivazione di Optional Parameter Plan Optimization. È comunque possibile agire sulla configurazione attraverso l’opzione OPTIONAL_PARAMETER_OPTIMIZATION. L’attivazione di Optional Parameter Plan Optimization non è subordinata all’attivazione di Parameter Sensitive Plan Optimization.

Non vi resta che provare l’RC0 di SQL Server 2025 appena rilasciata!