• 08/04/2020
• 12 minuti da leggere
• • s
  • M
  • M
  • p
  • j
  • +6

Si applica a: QL Server (tutte le versioni supportate)

Questo articolo introduce Scalar UDF Inlining, una caratteristica della suite di funzioni Intelligent Query Processing. Questa funzione migliora le prestazioni delle query che invocano le UDF scalari in SQL Server (a partire da SQL Server 2019 (15.x)).

Funzioni scalari T-SQL definite dall’utente

Le funzioni definite dall’utente (UDF) che sono implementate in Transact-SQL e restituiscono un singolo valore di dati sono denominate funzioni scalari T-SQL definite dall’utente. Le UDF T-SQL sono un modo elegante per ottenere il riutilizzo del codice e la modularità tra le query Transact-SQL. Alcuni calcoli (come le complesse regole di business) sono più facili da esprimere in forma imperativa UDF. Le UDF aiutano a costruire una logica complessa senza richiedere competenze nella scrittura di complesse query SQL. Per maggiori informazioni sulle UDF, vedere Creare funzioni definite dall’utente (Database Engine).

Performance delle UDF scalari

Le UDF scalari finiscono tipicamente per avere prestazioni scadenti a causa delle seguenti ragioni:

• Invocazione iterativa: Le UDF sono invocate in modo iterativo, una volta per ogni tupla qualificante. Questo incorre in costi aggiuntivi di ripetuti cambi di contesto dovuti all’invocazione della funzione. In particolare, le UDF che eseguono query Transact-SQL nella loro definizione sono gravemente colpite.

• Mancanza di costi: Durante l’ottimizzazione, solo gli operatori relazionali sono valutati, mentre gli operatori scalari non lo sono. Prima dell’introduzione delle UDF scalari, gli altri operatori scalari erano generalmente economici e non richiedevano costi. Un piccolo costo di CPU aggiunto per un’operazione scalare era sufficiente. Ci sono scenari in cui il costo effettivo è significativo, eppure rimane ancora sottorappresentato.

• Esecuzione interpretata: Le UDF sono valutate come un gruppo di istruzioni, eseguite istruzione per istruzione. Ogni istruzione stessa viene compilata e il piano compilato viene memorizzato nella cache. Anche se questa strategia di caching fa risparmiare un po’ di tempo perché evita le ricompilazioni, ogni dichiarazione viene eseguita in modo isolato. Nessuna ottimizzazione cross-statement viene effettuata.

• Esecuzione seriale: SQL Server non permette il parallelismo intra-query nelle query che invocano le UDF.

Inlining automatico delle UDF scalari

L’obiettivo della funzione di inlining delle UDF scalari è di migliorare le prestazioni delle query che invocano le UDF scalari T-SQL, dove l’esecuzione delle UDF è il principale collo di bottiglia.

Con questa nuova caratteristica, le UDF scalari vengono automaticamente trasformate in espressioni scalari o subquery scalari che vengono sostituite nella query chiamante al posto dell’operatore UDF. Queste espressioni e subquery vengono poi ottimizzate. Come risultato, il piano della query non avrà più un operatore di funzione definito dall’utente, ma i suoi effetti saranno osservati nel piano, come le viste o le TVF in linea.

Esempio 1 – UDF scalare a dichiarazione singola

Considera la seguente query.

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (L_EXTENDEDPRICE *(1 - L_DISCOUNT)) FROM LINEITEMINNER 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 gli articoli di linea e presenta i risultati raggruppati per data e priorità di spedizione. L’espressione L_EXTENDEDPRICE *(1 - L_DISCOUNT) è la formula per il prezzo scontato di un dato articolo. Tali formule possono essere estratte in funzioni a vantaggio della modularità e del riutilizzo.

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

Ora la query può essere modificata per invocare questa UDF.

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

A causa delle ragioni descritte in precedenza, la query con la UDF ha prestazioni scadenti. Ora, con l’inlining delle UDF scalari, l’espressione scalare nel corpo dell’UDF viene sostituita direttamente nella query. I risultati dell’esecuzione di questa query sono mostrati nella tabella sottostante:

Query:	Query senza UDF	Query con UDF (senza inlining)	Query con inlining UDF scalare
Tempo di esecuzione:	1.6 secondi	29 minuti e 11 secondi	1.6 secondi

Questi numeri sono basati su un database CCI da 10 GB (utilizzando lo schema TPC-H), eseguito su una macchina con doppio processore (12 core), 96 GB di RAM, supportata da SSD. I numeri includono il tempo di compilazione e di esecuzione con una cache di procedura fredda e un pool di buffer. È stata usata la configurazione di default, e non sono stati creati altri indici.

Esempio 2 – UDF scalare multi-statement

Le UDF scalari che sono implementate usando più istruzioni T-SQL come assegnazioni di variabili e ramificazioni condizionali possono anche essere delineate. Consideriamo la seguente UDF scalare che, data una chiave cliente, determina la categoria di servizio per quel cliente. Arriva alla categoria calcolando prima il prezzo totale di tutti gli ordini effettuati dal cliente usando una query SQL. Poi, usa una logica IF (...) ELSE per decidere la categoria in base al prezzo totale.

CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT) RETURNS CHAR(10) ASBEGIN DECLARE @total_price DECIMAL(18,2); DECLARE @category 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

Ora, consideriamo una query che invoca questa UDF.

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 precedenti) è il seguente:

Come mostra il piano, SQL Server adotta qui una strategia semplice: per ogni tupla nella tabella CUSTOMER, invoca la UDF e produce i risultati. Questa strategia è ingenua e inefficiente. Con l’inlining, tali UDF vengono trasformate in sottoquery scalari equivalenti, che vengono sostituite nella query chiamante al posto dell’UDF.

Per la stessa query, il piano con l’UDF inlined appare come segue.

Come detto prima, il piano di query non ha più un operatore di funzioni definite dall’utente, ma i suoi effetti sono ora osservabili nel piano, come le viste o le TVF inline. Ecco alcune osservazioni chiave dal piano di cui sopra:

• QL Server ha dedotto il join implicito tra CUSTOMER e ORDERS e lo ha reso esplicito tramite un operatore di join.
• QL Server ha anche dedotto l’implicito GROUP BY O_CUSTKEY on ORDERS e ha usato l’IndexSpool + StreamAggregate per implementarlo.
• SQL Server sta ora usando il parallelismo tra tutti gli operatori.

A seconda della complessità della logica nella UDF, il piano di query risultante potrebbe anche diventare più grande e complesso. Come possiamo vedere, le operazioni all’interno della UDF non sono più una scatola opaca, e quindi l’ottimizzatore di query è in grado di costare e ottimizzare queste operazioni. Inoltre, poiché la UDF non è più nel piano, l’invocazione iterativa della UDF è sostituita da un piano che evita completamente l’overhead delle chiamate di funzione.

Requisiti delle UDF scalari inlineabili

Una UDF scalare T-SQL può essere inlineata se tutte le seguenti condizioni sono vere:

• La UDF è scritta usando i seguenti costrutti:
  • DECLARESET: Dichiarazione e assegnazioni di variabili.
  • SELECT: Query SQL con assegnazioni di variabili singole/multiple 1.
  • IFELSE: Branching con livelli di annidamento arbitrari.
  • RETURN: Dichiarazioni di ritorno singole o multiple.
  • UDF: Chiamate di funzioni annidate/ricorsive 2.
  • Altri: Operazioni relazionali come EXISTSISNULL.
• L’UDF non invoca nessuna funzione intrinseca che sia dipendente dal tempo (come GETDATE()) o abbia effetti collaterali 3 (come NEWSEQUENTIALID()).
• L’UDF usa la clausola EXECUTE AS CALLER (comportamento predefinito se la clausola EXECUTE AS non è specificata).
• L’UDF non fa riferimento a variabili di tabella o a parametri con valore di tabella.
• La query che invoca una UDF scalare non fa riferimento a una chiamata UDF scalare nella sua clausola GROUP BY.
• La query che invoca una UDF scalare nella sua lista select con DISTINCT clausola non ha ORDER BY clausola.
• L’UDF non è usata nella clausola ORDER BY.
• L’UDF non è compilata nativamente (l’interop è supportato).
• L’UDF non è usata in una colonna calcolata o nella definizione di un vincolo di controllo.
• La UDF non fa riferimento a tipi definiti dall’utente.
• Non ci sono firme aggiunte alla UDF.
• La UDF non è una funzione di partizione.
• L’UDF non contiene riferimenti a espressioni di tabelle comuni (CTE).
• L’UDF non contiene riferimenti a funzioni intrinseche che possono alterare i risultati quando vengono delineate (come @@ROWCOUNT) 4.
• L’UDF non contiene funzioni aggregate passate come parametri a un’UDF scalare 4.
• L’UDF non fa riferimento a viste incorporate (come OBJECT_ID) 4.
• L’UDF non fa riferimento a metodi XML 5.
• L’UDF non contiene una SELECT con ORDER BY senza una clausola TOP 1 5.
• L’UDF non contiene una query SELECT che esegue un’assegnazione insieme alla clausola ORDER BY (come SELECT @x = @x + 1 FROM table1 ORDER BY col1) 5.
• L’UDF non contiene dichiarazioni RETURN multiple 6.
• L’UDF non è chiamata da una dichiarazione RETURN 6.
• L’UDF non fa riferimento alla funzione STRING_AGG 6.
• L’UDF non fa riferimento a tabelle remote 7.
• La query che chiama l’UDF non usa GROUPING SETSCUBE, o ROLLUP
• La query di chiamata UDF non contiene una variabile utilizzata come parametro UDF per l’assegnazione (per esempio, SELECT @y = 2@x = UDF(@y))7.

1 SELECT con accumulo/aggregazione variabile non è supportato per l’inlining (come SELECT @val += col1 FROM table1).

2 Le UDF ricorsive saranno inlinate solo fino ad una certa profondità.

3 Le funzioni intrinseche i cui risultati dipendono dal tempo corrente del sistema sono dipendenti dal tempo. Una funzione intrinseca che può aggiornare qualche stato globale interno è un esempio di funzione con effetti collaterali. Tali funzioni restituiscono risultati diversi ogni volta che vengono chiamate, in base allo stato interno.

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

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

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

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

Verificare se una UDF può essere inlining

Per ogni UDF scalare T-SQL, la vista catalogo sys.sql_modules include una proprietà chiamata is_inlineable, che indica se una UDF è inlineabile o meno.

Un valore di 1 indica che è inlineabile, e 0 indica il contrario. Questa proprietà avrà un valore di 1 anche per tutti i TVF in linea. Per tutti gli altri moduli, il valore sarà 0.

Se una UDF scalare è inlineabile, ciò non implica che sarà sempre inlineata. SQL Server deciderà (su una base per-query, per-UDF) se inlineare o meno una UDF. Alcuni esempi di quando una UDF potrebbe non essere compilata includono:

• Se la definizione della UDF si estende per migliaia di righe di codice, SQL Server potrebbe scegliere di non compilarla.

• Un’invocazione UDF in una clausola GROUP BY non sarà compilata. Questa decisione viene presa quando la query che fa riferimento a una UDF scalare viene compilata.

• Se la UDF è firmata con un certificato. Poiché le firme possono essere aggiunte e rimosse dopo che una UDF è stata creata, la decisione di inlineare o meno viene presa quando la query che fa riferimento a una UDF scalare viene compilata. Per esempio, le funzioni di sistema sono tipicamente firmate con un certificato. Puoi usare sys.crypt_properties per trovare quali oggetti sono firmati.

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

Controllare se l’inlining è avvenuto o meno

Se tutte le precondizioni sono soddisfatte e SQL Server decide di eseguire l’inlining, trasforma la UDF in un’espressione relazionale. Dal piano della query, è facile capire se l’inlining è avvenuto o meno:

• Il piano xml non avrà un nodo xml <UserDefinedFunction> per una UDF che è stata inlinata con successo.
• Certi XEvents sono emessi.

Abilitare Scalar UDF Inlining

Puoi rendere i carichi di lavoro automaticamente idonei per Scalar UDF Inlining abilitando il livello di compatibilità 150 per il database. Puoi impostarlo usando Transact-SQL. Per esempio:

ALTER DATABASE SET COMPATIBILITY_LEVEL = 150;

A parte questo, non ci sono altre modifiche da fare alle UDF o alle query per trarre vantaggio da questa caratteristica.

Disabilitare Scalar UDF Inlining senza cambiare il livello di compatibilità

Scalar UDF inlining può essere disabilitato a livello di database, statement, o UDF mantenendo comunque il livello di compatibilità 150 e superiore. Per disabilitare l’inlining delle UDF scalari nell’ambito del database, esegui la seguente istruzione nel contesto del database applicabile:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Per riattivare lo Scalar UDF Inlining per il database, eseguire la seguente istruzione nel contesto del database applicabile:

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Quando è attiva, questa impostazione appare come abilitata in sys.database_scoped_configurations.Si può anche disabilitare lo Scalar UDF Inlining per una specifica query designando DISABLE_TSQL_SCALAR_UDF_INLINING come USE HINT query hint. Per esempio:

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

Scalar UDF Inlining può anche essere disabilitato per una specifica UDF usando la clausola INLINE nella dichiarazione CREATE FUNCTION o ALTER FUNCTION.Per esempio:

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

Una volta che l’istruzione di cui sopra viene eseguita, questa UDF non sarà più inlining in nessuna query che la invochi. Per riattivare l’inlining per questa UDF, esegui la seguente istruzione:

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

Note importanti

Come descritto in questo articolo, l’inlining delle UDF scalari trasforma una query con UDF scalari in una query con una sottoquery scalare equivalente. A causa di questa trasformazione, gli utenti potrebbero notare alcune differenze di comportamento nei seguenti scenari:

1. L’inlining risulterà in un diverso hash della query per lo stesso testo della query.
2. Certi avvertimenti nelle dichiarazioni all’interno dell’UDF (come il dividi per zero ecc.) che potrebbero essere stati nascosti in precedenza, potrebbero apparire a causa dell’inlining.
3. I suggerimenti di join a livello di query potrebbero non essere più validi, poiché l’inlining potrebbe introdurre nuovi join. Dovranno invece essere usati i suggerimenti locali di join.
4. Le viste che fanno riferimento alle UDF scalari in linea non possono essere indicizzate. Se hai bisogno di creare un indice su tali viste, disabilita l’inlining per le UDF referenziate.
5. Ci potrebbero essere alcune differenze nel comportamento del mascheramento dei dati dinamici con l’inlining delle UDF. In certe situazioni (a seconda della logica della UDF), l’inlining potrebbe essere più conservativo per quanto riguarda il mascheramento delle colonne di output. In scenari in cui le colonne referenziate in una UDF non sono colonne di output, non saranno mascherate.
6. Se una UDF fa riferimento a funzioni integrate come SCOPE_IDENTITY()@@ROWCOUNT, o @@ERROR, il valore restituito dalla funzione integrata cambierà con l’inlining. Questo cambiamento di comportamento è dovuto al fatto che l’inlining cambia l’ambito delle dichiarazioni all’interno della UDF. A partire da SQL Server 2019 (15.x) CU2, l’inlining viene bloccato se la UDF fa riferimento a determinate funzioni intrinseche (ad esempio @@ROWCOUNT).

Vedi anche