• 08/04/2020
• 12 minutes de lecture
• • s
  • M

  .

  • M
  • p
  • j
  • +6

S’applique à : SQL Server (toutes les versions prises en charge)

Cet article présente Scalar UDF Inlining, une fonctionnalité de la suite de fonctionnalités Intelligent Query Processing. Cette fonctionnalité améliore les performances des requêtes qui invoquent des UDF scalaires dans SQL Server (à partir de SQL Server 2019 (15.x)).

Fonctions utilisateur scalaires T-SQL

Les fonctions définies par l’utilisateur (UDF) qui sont mises en œuvre dans Transact-SQL et qui renvoient une seule valeur de données sont appelées fonctions utilisateur scalaires T-SQL. Les fonctions définies par l’utilisateur T-SQL constituent un moyen élégant de réutiliser le code et d’assurer la modularité des requêtes Transact-SQL. Certains calculs (tels que les règles de gestion complexes) sont plus faciles à exprimer sous forme impérative d’UDF. Les UDFs aident à construire une logique complexe sans nécessiter d’expertise dans l’écriture de requêtes SQL complexes. Pour plus d’informations sur les UDF, voir Créer des fonctions définies par l’utilisateur (moteur de base de données).

Performances des UDF scalaires

Les UDF scalaires finissent généralement par avoir des performances médiocres pour les raisons suivantes :

• Invocation itérative : Les UDF sont invoqués de manière itérative, une fois par tuple qualifiant. Cela entraîne des coûts supplémentaires de changement de contexte répété en raison de l’invocation de la fonction. En particulier, les UDF qui exécutent des requêtes Transact-SQL dans leur définition sont sévèrement affectés.

• L’absence de chiffrage : Lors de l’optimisation, seuls les opérateurs relationnels sont chiffrés, alors que les opérateurs scalaires ne le sont pas. Avant l’introduction des UDF scalaires, les autres opérateurs scalaires étaient généralement bon marché et ne nécessitaient pas de chiffrage. Un petit coût CPU ajouté pour une opération scalaire était suffisant. Il existe des scénarios où le coût réel est important, et reste pourtant sous-représenté.

• Exécution interprétée : Les UDF sont évalués comme un lot d’instructions, exécutés instruction par instruction. Chaque instruction elle-même est compilée, et le plan compilé est mis en cache. Bien que cette stratégie de mise en cache permette de gagner du temps en évitant les recompilations, chaque instruction est exécutée de manière isolée. Aucune optimisation inter-états n’est effectuée.

• Exécution en série : SQL Server ne permet pas le parallélisme intra-requête dans les requêtes qui invoquent des UDF.

Inlining automatique des UDF scalaires

L’objectif de la fonctionnalité d’inlining des UDF scalaires est d’améliorer les performances des requêtes qui invoquent des UDF scalaires T-SQL, où l’exécution des UDF est le principal goulot d’étranglement.

Avec cette nouvelle fonctionnalité, les UDF scalaires sont automatiquement transformés en expressions scalaires ou en sous-requêtes scalaires qui sont substituées dans la requête appelante à la place de l’opérateur UDF. Ces expressions et sous-requêtes sont ensuite optimisées. En conséquence, le plan de requête n’aura plus d’opérateur de fonction définie par l’utilisateur, mais ses effets seront observés dans le plan, comme les vues ou les TVF en ligne.

Exemple 1 – UDF scalaire à déclaration unique

Considérons la requête suivante.

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;

Cette requête calcule la somme des prix réduits pour les articles de ligne et présente les résultats groupés par la date d’expédition et la priorité d’expédition. L’expression L_EXTENDEDPRICE *(1 - L_DISCOUNT) est la formule du prix réduit pour un article de ligne donné. De telles formules peuvent être extraites dans des fonctions au profit de la modularité et de la réutilisation.

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

Maintenant, la requête peut être modifiée pour invoquer cet 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

Pour les raisons évoquées précédemment, la requête avec l’UDF est peu performante. Maintenant, avec le scalar UDF inlining, l’expression scalaire dans le corps de l’UDF est substituée directement dans la requête. Les résultats de l’exécution de cette requête sont présentés dans le tableau ci-dessous :

Query :	Recherche sans UDF	Recherche avec UDF (sans inlining)	Recherche avec scalaire UDF inlining
Temps d’exécution:	1.6 secondes	29 minutes 11 secondes	1,6 secondes

Ces chiffres sont basés sur une base de données CCI de 10 Go (utilisant le schéma TPC-H), fonctionnant sur une machine à double processeur (12 cœurs), 96 Go de RAM, soutenue par un SSD. Les chiffres incluent les temps de compilation et d’exécution avec un cache de procédure froid et un pool de mémoire tampon. La configuration par défaut a été utilisée, et aucun autre index n’a été créé.

Exemple 2 – UDF scalaire à instructions multiples

Les UDF scalaires qui sont mis en œuvre à l’aide de plusieurs instructions T-SQL, telles que les affectations de variables et les branchements conditionnels, peuvent également être inlined. Considérons l’UDF scalaire suivant qui, étant donné une clé de client, détermine la catégorie de service pour ce client. Il arrive à la catégorie en calculant d’abord le prix total de toutes les commandes passées par le client à l’aide d’une requête SQL. Ensuite, il utilise une IF (...) ELSE logique pour décider de la catégorie en fonction du prix total.

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

Envisageons maintenant une requête qui invoque cet UDF.

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

Le plan d’exécution de cette requête dans SQL Server 2017 (14.x) (niveau de compatibilité 140 et antérieur) est le suivant :

Comme le montre le plan, SQL Server adopte ici une stratégie simple : pour chaque tuple de la table CUSTOMER, invoquer l’UDF et sortir les résultats. Cette stratégie est naïve et inefficace. Avec l’inlining, de tels UDF sont transformés en sous-requêtes scalaires équivalentes, qui sont substituées dans la requête appelante à la place de l’UDF.

Pour la même requête, le plan avec l’UDF inlined se présente comme ci-dessous.

Comme mentionné précédemment, le plan de requête n’a plus d’opérateur de fonction définie par l’utilisateur, mais ses effets sont maintenant observables dans le plan, comme les vues ou les TVF inline. Voici quelques observations clés du plan ci-dessus :

• SQL Server a déduit la jointure implicite entre CUSTOMER et ORDERS et l’a rendue explicite via un opérateur de jointure.
• Le serveur SQL a également déduit l’implicite GROUP BY O_CUSTKEY on ORDERS et a utilisé l’IndexSpool + StreamAggregate pour l’implémenter.
• Le serveur SQL utilise maintenant le parallélisme à travers tous les opérateurs.

Selon la complexité de la logique dans l’UDF, le plan de requête résultant pourrait également devenir plus grand et plus complexe. Comme nous pouvons le voir, les opérations à l’intérieur de l’UDF ne sont maintenant plus une boîte opaque, et donc l’optimiseur de requêtes est capable de chiffrer et d’optimiser ces opérations. De plus, puisque l’UDF n’est plus dans le plan, l’invocation itérative de l’UDF est remplacée par un plan qui évite complètement l’overhead des appels de fonction.

Exigences pour les UDF scalaires inlineables

Un UDF T-SQL scalaire peut être inline si toutes les conditions suivantes sont vraies:

• L’UDF est écrit en utilisant les constructions suivantes:
  • DECLARESET : Déclaration et affectations de variables.
  • SELECT : Requête SQL avec affectations de variables simples/multiples 1.
  • IFELSE : Branchement avec des niveaux d’imbrication arbitraires.
  • RETURN : déclarations de retour uniques ou multiples.
  • UDF : Appels de fonctions imbriquées/récursifs 2.
  • Autres : Opérations relationnelles telles que EXISTSISNULL.
• L’UDF n’invoque aucune fonction intrinsèque qui soit dépendante du temps (comme GETDATE()) ou qui ait des effets secondaires 3 (comme NEWSEQUENTIALID()).
• L’UDF utilise la clause EXECUTE AS CALLER (comportement par défaut si la clause EXECUTE AS n’est pas spécifiée).
• L’UDF ne fait pas référence à des variables de table ou à des paramètres à valeur de table.
• La requête invoquant un UDF scalaire ne fait pas référence à un appel UDF scalaire dans sa clause GROUP BY.
• La requête invoquant un UDF scalaire dans sa liste de sélection avec DISTINCT clause ne possède pas ORDER BY clause.
• L’UDF n’est pas utilisé dans ORDER BY clause.
• L’UDF n’est pas compilé nativement (l’interop est supporté).
• L’UDF n’est pas utilisé dans une colonne calculée ou une définition de contrainte de contrôle.
• L’UDF ne fait pas référence à des types définis par l’utilisateur.
• Il n’y a pas de signatures ajoutées à l’UDF.
• L’UDF n’est pas une fonction de partition.
• L’UDF ne contient pas de références à des expressions de table communes (CTE).
• L’UDF ne contient pas de références à des fonctions intrinsèques qui peuvent modifier les résultats lorsqu’elles sont inlined (comme @@ROWCOUNT) 4.
• L’UDF ne contient pas de fonctions agrégées passées comme paramètres à un UDF scalaire 4.
• L’UDF ne fait pas référence à des vues intégrées (telles que OBJECT_ID) 4.
• L’UDF ne référence pas de méthodes XML 5.
• L’UDF ne contient pas de SELECT avec ORDER BY sans une clause TOP 1 5.
• L’UDF ne contient pas de requête SELECT qui effectue une affectation en conjonction avec la clause ORDER BY (telle que SELECT @x = @x + 1 FROM table1 ORDER BY col1) 5.
• L’UDF ne contient pas de multiples instructions RETURN 6.
• L’UDF n’est pas appelé à partir d’une instruction RETURN 6.
• L’UDF ne fait pas référence à la fonction STRING_AGG 6.
• L’UDF ne fait pas référence à des tables distantes 7.
• La requête appelant l’UDF n’utilise pas GROUPING SETSCUBE, ou ROLLUP
• La requête d’appel UDF ne contient pas de variable utilisée comme paramètre UDF pour l’affectation (par exemple, SELECT @y = 2@x = UDF(@y))7.

1 SELECT avec accumulation/agrégation variable n’est pas pris en charge pour l’inlining (comme SELECT @val += col1 FROM table1).

2 Les UDF récursifs seront inlined jusqu’à une certaine profondeur seulement.

3 Les fonctions intrinsèques dont les résultats dépendent du temps système actuel sont dépendantes du temps. Une fonction intrinsèque qui peut mettre à jour un certain état global interne est un exemple de fonction avec des effets secondaires. De telles fonctions renvoient des résultats différents chaque fois qu’elles sont appelées, en fonction de l’état interne.

4 Restriction ajoutée dans SQL Server 2019 (15.x) CU2

5 Restriction ajoutée dans SQL Server 2019 (15.x) CU4

6 Restriction ajoutée dans SQL Server 2019 (15.x) CU5

7 Restriction ajoutée dans SQL Server 2019 (15.x) CU6

Checking whether or not a UDF can be inlined

Pour chaque UDF scalaire T-SQL, la vue du catalogue sys.sql_modules catalog view comprend une propriété appelée is_inlineable, qui indique si un UDF est inlineable ou non.

Une valeur de 1 indique qu’il est inlineable, et 0 indique le contraire. Cette propriété aura une valeur de 1 pour tous les TVFs inline également. Pour tous les autres modules, la valeur sera de 0.

Si un UDF scalaire est inlineable, cela n’implique pas qu’il sera toujours inline. Le serveur SQL décidera (sur une base par requête, par UDF) s’il faut ou non mettre en ligne un UDF. Voici quelques exemples de cas où un UDF peut ne pas être inline :

• Si la définition de l’UDF s’étend sur des milliers de lignes de code, SQL Server pourrait choisir de ne pas l’inline.

• Une invocation d’UDF dans une clause GROUP BY ne sera pas inline. Cette décision est prise lorsque la requête référençant un UDF scalaire est compilée.

• Si l’UDF est signé avec un certificat. Parce que les signatures pourraient être ajoutées et abandonnées après la création d’un UDF, la décision d’inline ou non est prise lorsque la requête référençant un UDF scalaire est compilée. Par exemple, les fonctions système sont généralement signées avec un certificat. Vous pouvez utiliser sys.crypt_properties pour trouver quels objets sont signés.

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

Vérifier si l’inlining a eu lieu ou non

Si toutes les conditions préalables sont satisfaites et que SQL Server décide d’effectuer l’inlining, il transforme l’UDF en une expression relationnelle. A partir du plan de requête, il est facile de savoir si l’inlining a eu lieu ou non :

• Le plan xml n’aura pas de nœud xml <UserDefinedFunction> pour un UDF qui a été inlined avec succès.
• Certains XEvents sont émis.

Activation de Scalar UDF Inlining

Vous pouvez rendre les charges de travail automatiquement éligibles à Scalar UDF Inlining en activant le niveau de compatibilité 150 pour la base de données. Vous pouvez définir cette option à l’aide de Transact-SQL. Par exemple :

ALTER DATABASE SET COMPATIBILITY_LEVEL = 150;

À part cela, il n’y a pas d’autres modifications à apporter aux UDF ou aux requêtes pour profiter de cette fonctionnalité.

Désactivation du Scalar UDF Inlining sans modifier le niveau de compatibilité

Le Scalar UDF inlining peut être désactivé au niveau de la base de données, de l’instruction ou de la portée de l’UDF tout en conservant le niveau de compatibilité 150 et plus de la base de données. Pour désactiver l’inlining UDF scalaire au niveau de la portée de la base de données, exécutez l’instruction suivante dans le contexte de la base de données applicable :

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = OFF;

Pour réactiver le Scalar UDF Inlining au niveau de la base de données, exécutez l’instruction suivante dans le contexte de la base de données applicable :

ALTER DATABASE SCOPED CONFIGURATION SET TSQL_SCALAR_UDF_INLINING = ON;

Quand il est activé, ce paramètre apparaîtra comme activé dans sys.database_scoped_configurations.Vous pouvez également désactiver Scalar UDF Inlining pour une requête spécifique en désignant DISABLE_TSQL_SCALAR_UDF_INLINING comme un USE HINT query hint. Par exemple :

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'));

L’internalisation des UDF scalaires peut également être désactivée pour un UDF spécifique à l’aide de la clause INLINE dans l’instruction CREATE FUNCTION ou ALTER FUNCTION.Par exemple :

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;

Une fois que l’instruction ci-dessus est exécutée, cet UDF ne sera jamais inlined dans toute requête qui l’invoque. Pour réactiver l’inlining pour cet UDF, exécutez l’instruction suivante :

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

Notes importantes

Comme décrit dans cet article, l’inlining des UDF scalaires transforme une requête avec des UDF scalaires en une requête avec une sous-requête scalaire équivalente. En raison de cette transformation, les utilisateurs peuvent remarquer certaines différences de comportement dans les scénarios suivants :

1. L’inlining entraînera un hachage de requête différent pour le même texte de requête.
2. Certains avertissements dans les déclarations à l’intérieur de l’UDF (comme la division par zéro, etc.) qui auraient pu être cachés auparavant, pourraient apparaître en raison de l’inlining.
3. Les indices de jointure au niveau de la requête pourraient ne plus être valides, car l’inlining peut introduire de nouvelles jointures. Des astuces de jointure locales devront être utilisées à la place.
4. Les vues qui font référence à des UDF scalaires en ligne ne peuvent pas être indexées. Si vous devez créer un index sur de telles vues, désactivez l’inlining pour les UDF référencés.
5. Il pourrait y avoir quelques différences dans le comportement du masquage des données dynamiques avec l’inlining des UDF.

Dans certaines situations (selon la logique de l’UDF), l’inlining pourrait être plus conservateur en ce qui concerne le masquage des colonnes de sortie. Dans les scénarios où les colonnes référencées dans un UDF ne sont pas des colonnes de sortie, elles ne seront pas masquées.

6. Si un UDF référence des fonctions intégrées telles que SCOPE_IDENTITY()@@ROWCOUNT, ou @@ERROR, la valeur renvoyée par la fonction intégrée changera avec l’inlining. Ce changement de comportement est dû au fait que l’inlining modifie la portée des déclarations à l’intérieur de l’UDF. À partir de SQL Server 2019 (15.x) CU2, l’inlining est bloqué si l’UDF fait référence à certaines fonctions intrinsèques (par exemple @@ROWCOUNT).

See Also