Inlining di funzioni definite dall'utente scalari

  • Articolo

Si applica a: SQL Server 2019 (15.x) Database SQL di Azure Istanza gestita di SQL di Azure

Questo articolo presenta l'inlining di funzioni definite dall'utente scalari, una delle funzionalità incluse nel gruppo di funzionalità di elaborazione di query intelligenti nei database SQL. Questa funzionalità migliora le prestazioni delle query che chiamano funzioni definite dall'utente scalari in SQL Server a partire da SQL Server 2019 (15.x) e versioni successive.

Funzioni definite dall'utente scalari T-SQL

Le funzioni definite dall'utente implementate in Transact-SQL che restituiscono un unico valore di dati sono dette funzioni definite dall'utente scalari T-SQL. Le funzioni definite dall'utente T-SQL consentono di riutilizzare e modulare il codice in più query Transact-SQL in modo elegante. Alcuni calcoli (ad esempio regole business complesse) sono più facili da esprimere nella forma imperativa delle funzioni definite dall'utente. Le funzioni definite dall'utente consentono di creare una logica complessa senza richiedere l'esperienza necessaria per la scrittura di query SQL complesse. Per altre informazioni sulle funzioni definite dall'utente, vedere Creare funzioni definite dall'utente (motore di database).

Prestazioni delle funzioni definite dall'utente scalari

Le funzioni definite dall'utente scalari offrono in genere prestazioni scarse per i motivi seguenti:

  • Chiamata iterativa. Le funzioni definite dall'utente vengono chiamate in modo iterativo, una volta per ogni tupla idonea. Ciò comporta costi aggiuntivi a causa del cambio di contesto ripetuto dovuto alla chiamata di funzione. Questo aspetto interessa in modo particolarmente grave le funzioni definite dall'utente che eseguono query Transact-SQL all'interno della propria definizione.

  • Mancanza di determinazione costi. Durante l'ottimizzazione vengono definiti i costi dei soli operatori relazionali, non degli operatori scalari. Prima dell'introduzione delle funzioni definite dall'utente scalari, i costi degli altri operatori scalari erano in genere bassi e non richiedevano una determinazione costi. Per un'operazione scalare era sufficiente aggiungere un costo ridotto per la CPU. In alcuni scenari, il costo effettivo è significativo ma rimane comunque sottorappresentato.

  • Esecuzione interpretata. Le funzioni definite dall'utente vengono valutate come batch di istruzioni e vengono eseguite istruzione per istruzione. Ogni istruzione viene compilata e il piano compilato viene memorizzato nella cache. Questa strategia di memorizzazione nella cache consente di risparmiare tempo perché consente di evitare le ricompilazioni, ma ogni istruzione viene eseguita in modo isolato. Non vengono eseguite ottimizzazioni tra istruzioni diverse.

  • Esecuzione seriale. SQL Server non consente il parallelismo interno per le query che chiamano funzioni definite dall'utente.

Inlining automatico di funzioni definite dall'utente scalari

L'obiettivo della funzionalità di inlining delle funzioni definite dall'utente scalari è di migliorare le prestazioni delle query che chiamano funzioni definite dall'utente scalari T-SQL, in cui il collo di bottiglia principale è costituito dall'esecuzione di funzioni definite dall'utente.

Con questa nuova funzionalità, le funzioni definite dall'utente scalari vengono trasformate automaticamente in espressioni scalari o sottoquery scalari che sostituiscono l'operatore della funzione definita dall'utente nella query chiamante. Queste espressioni e sottoquery vengono quindi ottimizzate. Di conseguenza, il piano di query non ha più un operatore di funzione definito dall'utente, ma i relativi effetti vengono osservati nel piano, ad esempio viste o funzioni con valori di tabella inline (TVFS).

Esempi

Gli esempi in questa sezione usano il database di benchmark TPC-H. Per altre informazioni, vedere la home page di TPC-H.

R. Funzione definita dall'utente scalare a istruzione singola

Si consideri la query seguente.

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(L_EXTENDEDPRICE * (1 - L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE;

Questa query calcola la somma dei prezzi scontati per le linee e presenta i risultati raggruppati per data e priorità di spedizione. L'espressione L_EXTENDEDPRICE *(1 - L_DISCOUNT) è la formula del prezzo scontato per una linea specifica. Tali formule possono essere estratte in funzioni a scopo di modularità e riutilizzo.

CREATE FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
AS
BEGIN
    RETURN @price * (1 - @discount);
END

È ora possibile modificare la query per chiamare questa funzione definita dall'utente.

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE;

Per i motivi descritti in precedenza, la query con la funzione definita dall'utente offre prestazioni scarse. Con l'inlining della funzione definita dall'utente scalare, l'espressione scalare nel corpo della funzione definita dall'utente viene sostituita direttamente all'interno della query. I risultati dell’esecuzione di questa query sono riportati nella tabella seguente:

Query: Query senza funzione definita dall'utente Query con funzione definita dall'utente (senza inlining) Query con inlining della funzione definita dall'utente scalare
Tempo di esecuzione: 1,6 secondi 29 minuti 11 secondi 1,6 secondi

Queste cifre si basano su un database CCI di 10 GB (con schema TPC-H) in esecuzione in un computer a doppio processore (12 core), 96 GB di RAM e unità SSD. Le cifre includono il tempo di compilazione ed esecuzione con cache di routine a freddo e pool di buffer. È stata usata la configurazione predefinita e non sono stati creati altri indici.

B. Funzione definita dall'utente scalare a più istruzioni

È possibile eseguire l'inlining anche delle funzioni definite dall'utente scalari implementate con più istruzioni T-SQL, ad esempio assegnazioni di variabili e diramazioni condizionali. Si consideri la funzione definita dall'utente scalare seguente che, data una chiave cliente, determina la categoria di servizio per tale cliente. Per arrivare alla categoria, prima calcola il prezzo totale di tutti gli ordini effettuati dal cliente tramite una query SQL. Usa quindi la logica IF (...) ELSE per stabilire la categoria in base al prezzo totale.

CREATE OR ALTER FUNCTION dbo.customer_category (@ckey INT)
RETURNS CHAR (10)
AS
BEGIN
    DECLARE @total_price AS DECIMAL (18, 2);
    DECLARE @category AS CHAR (10);
    SELECT @total_price = SUM(O_TOTALPRICE)
    FROM ORDERS
    WHERE O_CUSTKEY = @ckey;
    IF @total_price < 500000
        SET @category = 'REGULAR';
    ELSE
        IF @total_price < 1000000
            SET @category = 'GOLD';
        ELSE
            SET @category = 'PLATINUM';
    RETURN @category;
END

Si consideri ora una query che chiami questa funzione definita dall'utente.

SELECT C_NAME,
       dbo.customer_category(C_CUSTKEY)
FROM CUSTOMER;

Il piano di esecuzione per questa query in SQL Server 2017 (14.x) (livello di compatibilità 140 e versioni precedenti) è il seguente:

Screenshot del piano di query senza inlining.

Come illustrato dal piano, SQL Server adotta una strategia semplice: per ogni tupla nella tabella CUSTOMER, chiama la funzione definita dall'utente e genera l'output dei risultati. Questa strategia è semplicistica e inefficiente. Con l'inlining, una funzione definita dall'utente di questo tipo viene trasformata in una sottoquery scalare equivalente, che viene inserita nella query chiamante al posto della funzione definita dall'utente.

Per la stessa query, il piano con l'inlining della funzione definita dall'utente ha l'aspetto seguente.

Screenshot del piano di query con inlining.

Come detto in precedenza, il piano di query non ha più un operatore per la funzione definita dall'utente, ma gli effetti di questo, ad esempio viste o funzioni con valori di tabella inline, sono osservabili nel piano. Ecco alcune osservazioni chiave del piano precedente:

  • SQL Server deduce il join implicito tra CUSTOMER e ORDERS e lo rende esplicito tramite un operatore join.

  • SQL Server ha anche dedotto la clausola GROUP BY O_CUSTKEY on ORDERS implicita e ha usato IndexSpool + StreamAggregate per implementarla.

  • SQL Server usa ora il parallelismo tra tutti gli operatori.

A seconda della complessità della logica della funzione definita dall'utente, il piano di query generato risultante può essere anche più grande e più complesso. Come si può vedere, le operazioni all'interno della funzione definita dall'utente non sono più una black box. Query Optimizer è quindi in grado di determinare i costi di queste operazioni e di ottimizzarle. Poiché, poi, la funzione definita dall'utente non è più all'interno del piano, la chiamata iterativa a tale funzione viene sostituita da un piano che evita completamente il sovraccarico delle chiamate di funzione.

Requisiti delle funzioni definite dall'utente scalari abilitate per l'inlining

È possibile inlining di una funzione definita dall'utente T-SQL scalare se la definizione di funzione usa costrutti consentiti e la funzione viene usata in un contesto che abilita l'inlining:

Tutte le condizioni seguenti della definizione della funzione definita dall'utente devono essere vere:

  • La funzione definita dall'utente è scritta con i costrutti seguenti:
    • DECLARE, SET: dichiarazione e assegnazione di variabili.
    • SELECT: query SQL con assegnazioni di variabili singole/multiple 1.
    • IF/ELSE: diramazione con livelli di annidamento arbitrari.
    • RETURN: istruzione return singola o istruzioni return multiple. A partire da SQL Server 2019 (15.x) CU5, l'UDF può contenere solo una singola istruzione RETURN da considerare per l'inlining 6.
    • UDF: chiamate di funzioni ricorsive/annidate 2.
    • Altro: operazioni relazionali, ad esempio EXISTS, IS NULL.
  • La funzione definita dall'utente non chiama alcuna funzione intrinseca dipendente dal tempo (ad esempio GETDATE()) o con effetti collaterali 3 (ad esempio NEWSEQUENTIALID()).
  • La funzione definita dall'utente usa la clausola EXECUTE AS CALLER (comportamento predefinito se la clausola EXECUTE AS non viene specificata).
  • La funzione definita dall'utente non fa riferimento a variabili di tabella o a parametri con valori di tabella.
  • La funzione definita dall'utente non è compilata in modo nativo (interoperabilità supportata).
  • La funzione definita dall'utente non fa riferimento a tipi definiti dall'utente.
  • Nessuna firma aggiunta alla funzione definita dall'utente 9.
  • La funzione definita dall'utente non è una funzione di partizione.
  • La funzione definita dall'utente non contiene riferimenti a espressioni di tabella comuni (CTE).
  • La funzione definita dall'utente non contiene riferimenti a funzioni intrinseche che potrebbero alterare i risultati quando sono inline (ad esempio, @@ROWCOUNT) 4.
  • La funzione definita dall'utente non contiene funzioni di aggregazione passate come parametri a una funzione definita dall'utente scalare 4.
  • La funzione definita dall'utente non fa riferimento a viste predefinite (ad esempio, OBJECT_ID) 4.
  • La funzione definita dall'utente non fa riferimento ai metodi XML 5.
  • La funzione definita dall'utente non contiene una query SELECT con ORDER BY senza una clausola TOP 1 5.
  • La funzione definita dall'utente non contiene una query SELECT che esegue un'assegnazione in combinazione con la clausola ORDER BY (ad esempio, SELECT @x = @x + 1 FROM table1 ORDER BY col1) 5.
  • La funzione definita dall'utente non contiene più istruzioni RETURN 6.
  • La funzione definita dall'utente non fa riferimento alla funzione STRING_AGG 6.
  • La funzione definita dall'utente non fa riferimento a tabelle remote 7.
  • La funzione definita dall'utente non fa riferimento alle colonne crittografate 8.
  • La funzione definita dall'utente non contiene riferimenti a WITH XMLNAMESPACES 8.
  • Se la definizione di una funzione definita dall'utente viene eseguita in migliaia di righe di codice, SQL Server può scegliere di non eseguire l'inlining.

1 SELECT con accumulo/aggregazione di variabili non è supportata per l'inlining (ad esempio, SELECT @val += col1 FROM table1).

2 L'inlining delle funzioni definite dall'utente ricorsive viene eseguito solo fino a una determinata profondità.

3 Le funzioni intrinseche i cui risultati dipendono dall'ora di sistema corrente sono dipendenti dall'ora. Un esempio di funzione con effetti collaterali può essere costituito da una funzione intrinseca in grado di aggiornare uno stato globale interno. Tali funzioni restituiscono risultati diversi ogni volta che vengono chiamate, a seconda dello stato interno.

4 Restrizione aggiunta in SQL Server 2019 (15.x) CU 2

5 Restrizione aggiunta in SQL Server 2019 (15.x) CU 4

6 Restrizione aggiunta in SQL Server 2019 (15.x) CU 5

7 Restrizione aggiunta in SQL Server 2019 (15.x) CU 6

8 Restrizione aggiunta in SQL Server 2019 (15.x) CU 11

9 Poiché è possibile aggiungere ed eliminare firme dopo la creazione di una funzione definita dall'utente, la decisione se inline viene eseguita quando la query che fa riferimento a una funzione definita dall'utente scalare viene compilata. Le funzioni di sistema, ad esempio, vengono in genere firmate con un certificato. È possibile usare sys.crypt_properties per individuare gli oggetti firmati.

Tutti i requisiti seguenti del contesto di esecuzione devono essere true:

  • La funzione definita dall'utente non viene usata nella clausola ORDER BY.
  • La query che chiama una funzione definita dall'utente scalare non fa riferimento a una chiamata di funzione definita dall'utente scalare nella relativa clausola GROUP BY.
  • La query che richiama una funzione definita dall'utente scalare nel relativo elenco di selezione con clausola DISTINCT non include la clausola ORDER BY.
  • La funzione definita dall'utente non viene chiamata da un'istruzione RETURN 1.
  • La query che richiama la funzione definita dall'utente non ha espressioni di tabella comuni (CTEs) 3.
  • La query di chiamata alla funzione definita dall'utente non usa GROUPING SETS, CUBEo ROLLUP 2.
  • La query che chiama la funzione definita dall'utente non contiene una variabile usata come parametro UDF per l'assegnazione ( ad esempio SELECT @y = 2, @x = UDF(@y)) 2.
  • La funzione definita dall'utente non viene usata in una colonna calcolata o in una definizione di vincolo di controllo.

1 Restrizione aggiunta in SQL Server 2019 (15.x) CU 5

2 Restrizione aggiunta in SQL Server 2019 (15.x) CU 6

3 Restrizione aggiunta in SQL Server 2019 (15.x) CU 11

Per informazioni sulle correzioni e sulle modifiche più recenti dell'inlining di funzioni definite dall'utente scalari di T-SQL in scenari di idoneità all'inlining, vedere l'articolo della Knowledge Base: FIX: Problemi relativi all'inlining di funzioni definite dall'utente scalari in SQL Server 2019.

Controllare se una funzione definita dall'utente può essere inlined

Per ogni funzione definita dall'utente scalare T-SQL, la vista del catalogo sys.sql_modules include una proprietà denominata is_inlineable, che indica se una funzione definita dall'utente è inline.

La proprietà is_inlineable è derivata dai costrutti presenti nella definizione della funzione definita dall'utente. Non controlla se la funzione definita dall'utente supporta effettivamente l'inlining in fase di compilazione. Per altre informazioni, vedere le condizioni per l'inlining.

Un valore indica 1 che la funzione definita dall'utente è inline e 0 indica in caso contrario. Questa proprietà ha un valore pari a 1 anche per tutte le funzioni con valori di tabella inline. Per tutti gli altri moduli, il valore è 0.

Se una funzione definita dall'utente scalare è idonea all'inlining, non significa che ne viene sempre eseguito l'inlining. SQL Server decide (per ogni query, per funzione definita dall'utente) se inline una funzione definita dall'utente. Fare riferimento agli elenchi dei requisiti descritti in precedenza in questo articolo.

SELECT *
FROM sys.crypt_properties AS cp
     INNER JOIN sys.objects AS o
         ON cp.major_id = o.object_id;

Controllare se l'inlining è avvenuto

Se vengono soddisfatte tutte le condizioni preliminari e SQL Server decide di eseguire l'inlining, la funzione definita dall'utente viene trasformata in un'espressione relazionale. Dal piano di query è possibile determinare se si è verificata l'inlining:

  • Per una funzione definita dall'utente in cui l'inlining è stato eseguito correttamente, il codice XML del piano non contiene un nodo XML <UserDefinedFunction>.
  • Vengono generati determinati eventi estesi.

Abilitare l'inlining di funzioni definite dall'utente scalari

È possibile impostare automaticamente i carichi di lavoro come idonei all'inlining di funzioni definite dall'utente scalari abilitando il livello di compatibilità 150 per il database. Questa opzione è impostabile con Transact-SQL. Ad esempio:

ALTER DATABASE [WideWorldImportersDW]
    SET COMPATIBILITY_LEVEL = 150;

A parte questo passaggio, per sfruttare i vantaggi di questa funzionalità non è necessario apportare altri cambiamenti alle funzioni definite dall'utente o alle query.

Disabilitare dell'inlining di funzioni definite dall'utente scalari senza modificare il livello di compatibilità

È possibile disabilitare l'inlining di funzioni definite dall'utente scalari nell'ambito del database, dell'istruzione o della funzione definita dall'utente mantenendo comunque il livello di compatibilità del database 150 o superiore. Per disabilitare l'inlining di funzioni definite dall'utente scalari nell'ambito del database, eseguire l'istruzione seguente all'interno del contesto del database applicabile:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Per riabilitare l'inlining di funzioni definite dall'utente scalari per il database, eseguire l'istruzione seguente all'interno del contesto del database applicabile:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Quando ON, questa impostazione viene visualizzata come abilitata in sys.database_scoped_configurations.

È anche possibile disabilitare l'inlining di funzioni definite dall'utente scalari per una query specifica designando DISABLE_TSQL_SCALAR_UDF_INLINING come hint per la query USE HINT.

Un hint per la query USE HINT ha la precedenza sulla configurazione con ambito database o sull'impostazione del livello di compatibilità.

Ad esempio:

SELECT L_SHIPDATE,
       O_SHIPPRIORITY,
       SUM(dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))
FROM LINEITEM
     INNER JOIN ORDERS
         ON O_ORDERKEY = L_ORDERKEY
GROUP BY L_SHIPDATE, O_SHIPPRIORITY
ORDER BY L_SHIPDATE
OPTION (USE HINT('DISABLE_TSQL_SCALAR_UDF_INLINING'));

È anche possibile disabilitare l'inlining di funzioni definite dall'utente scalari per una funzione definita dall'utente specifica tramite la clausola INLINE nell'istruzione CREATE FUNCTION o ALTER FUNCTION. Ad esempio:

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = OFF
AS
BEGIN
    RETURN @price * (1 - @discount);
END

Dopo l'esecuzione dell'istruzione precedente, non viene mai eseguito l'inlining di questa funzione definita dall'utente in alcuna delle query che la chiameranno. Per riabilitare l'inlining per questa funzione definita dall'utente, eseguire l'istruzione seguente:

CREATE OR ALTER FUNCTION dbo.discount_price
(
    @price DECIMAL (12, 2),
    @discount DECIMAL (12, 2)
)
RETURNS DECIMAL (12, 2)
WITH INLINE = ON
AS
BEGIN
    RETURN @price * (1 - @discount);
END

La clausola INLINE non è obbligatoria. Se la clausola INLINE viene omessa, viene automaticamente impostata su ON/OFF in base al fatto che possa essere eseguito l'inline della funzione. Se viene specificato INLINE = ON, ma la funzione definita dall'utente non è idonea per l'inlining, viene generato un errore.

Osservazioni:

Come descritto in questo articolo, l'inlining di una funzione definita dall'utente scalare trasforma una query con funzioni definite dall'utente scalari in una query con una sottoquery scalare equivalente. A causa di questa trasformazione, si possono notare alcune differenze di comportamento negli scenari seguenti:

  • L'inlining ha come risultato un hash di query diverso per lo stesso testo della query.

  • Alcuni avvisi nelle istruzioni all'interno della funzione definita dall'utente (ad esempio divisione per zero e così via), che in precedenza erano nascosti, possono essere visualizzati a causa dell'inlining.

  • Gli hint di join a livello di query possono non essere più validi, poiché l'inlining può introdurre nuovi join. È necessario usare hint di join locale.

  • Non è possibile indicizzare le viste che fanno riferimento a funzioni definite dall'utente scalari. Se è necessario creare un indice per tali viste, disabilitare l'inlining per le funzioni definite dall'utente interessate.

  • Con l'inlining di funzioni definite dall'utente possono presentarsi alcune differenze nel comportamento del Dynamic Data Masking.

    In determinate situazioni (a seconda della logica della funzione definita dall'utente), l'inlining può essere più conservativo rispetto alla maschera delle colonne di output. Negli scenari in cui le colonne a cui si fa riferimento in una funzione definita dall'utente non sono colonne di output, queste non vengono mascherate.

  • Se una funzione definita dall'utente fa riferimento a funzioni predefinite, ad esempio SCOPE_IDENTITY(), @@ROWCOUNT o @@ERROR, il valore restituito dalla funzione predefinita cambia con l'inlining. Questa modifica nel comportamento è dovuta al fatto che l'inlining modifica l'ambito delle istruzioni all'interno della funzione definita dall'utente. A partire da SQL Server 2019 (15.x) CU2, l'inlining è bloccato se la funzione definita dall'utente fa riferimento a determinate funzioni intrinseche (ad esempio, @@ROWCOUNT).

  • Se una variabile viene assegnata con il risultato di una funzione definita dall'utente inlined e usata anche come index_column_name negli FORCESEEK hint di query, viene restituito l'errore 8622, a indicare che Query Processor non è riuscito a produrre un piano di query a causa degli hint definiti nella query.

Contenuto correlato