Résolution des problèmes de performances des requêtes : assemblage de constantes et évaluation d'expressions pendant l'estimation de cardinalité
SQL Server évalue quelques expressions constantes à l'avance pour améliorer les performances des requêtes. On parle d'assemblage de constantes. Une constante est un littéral Transact-SQL tel que 3, 'ABC', '2005-12-31', 1.0e3 ou 0x12345678.
Expressions pouvant être assemblées
SQL Server utilise l'assemblage de constantes avec les types d'expressions suivants :
- Expressions arithmétiques telles que
1+1, 5/3*2ne contenant que des constantes. - Expressions logiques telles que
1=1et1>2 AND 3>4ne contenant que des constantes. - Fonctions intégrées considérées comme pouvant être assemblées par SQL Server, y compris CAST et CONVERT. En général, une fonction intrinsèque peut être assemblée si elle est fonction de ses données d'entrée uniquement, à l'exclusion de toute information contextuelle (options SET, paramètres de langue, options de base de données et clés de chiffrement). Les fonctions non déterministes ne peuvent pas être assemblées. Les fonctions intégrées déterministes peuvent être assemblées, à quelques exceptions près.
Les objets volumineux constituent une exception. Si le type de résultat du processus d'assemblage est un type d'objet volumineux (text, image, nvarchar(max), varchar(max) ou varbinary(max)), SQL Server n'assemble pas l'expression.
Expressions ne pouvant pas être assemblées
Aucun des autres types d'expressions ne peut être assemblé. C'est notamment le cas des types d'expressions suivants :
- Expressions non constantes, par exemple une expression dont le résultat dépend de la valeur d'une colonne.
- Expressions dont les résultats dépendent d'une variable locale ou d'un paramètre, par exemple
@x. - Fonctions non déterministes.
- Fonctions définies par l'utilisateur (Transact-SQL ou CLR).
- Expressions dont les résultats dépendent de paramètres de langue.
- Expressions dont les résultats dépendent d'options SET.
- Expressions dont les résultats dépendent d'options de configuration du serveur.
Exemples d'expressions constantes pouvant ou ne pouvant pas être assemblées
Considérez la requête suivante :
SELECT *
FROM Sales.SalesOrderHeader s JOIN sales.SalesOrderDetail d
ON s.SalesOrderID = d.SalesOrderID
WHERE TotalDue > 117.00 + 1000.00
Si l'option de base de données PARAMETERIZATION n'a pas la valeur FORCED pour cette requête (la valeur par défaut est SIMPLE), l'expression 117.00 + 1000.00 est évaluée et remplacée par son résultat, à savoir 1117.00, avant la compilation de la requête. Voici quelques avantages de l'assemblage des constantes :
- L'expression n'a pas à être évaluée à chaque exécution.
- Une fois évaluée, la valeur de l'expression est utilisée par l'optimiseur de requête pour estimer la taille de l'ensemble de résultats de cette partie de la requête
TotalDue > 117.00 + 1000.00.
En revanche, si dbo.f est une fonction scalaire définie par l'utilisateur, l'expression dbo.f(100) n'est pas assemblée parce que SQL Server n'assemble pas les expressions qui font intervenir des fonctions définies par l'utilisateur, même si elles sont déterministes.
Évaluation d'expressions au moment de la compilation pour l'estimation de cardinalité
Certaines expressions qui ne sont pas assemblées, mais dont les arguments (qu'il s'agisse de paramètres ou de constantes) sont connus au moment de la compilation, sont évaluées pendant l'optimisation par l'estimateur de la taille de l'ensemble de résultats (cardinalité) inclus dans l'optimiseur.
Par exemple, les fonctions intégrées et opérateurs spéciaux suivants sont évalués au moment de la compilation si leurs données d'entrée sont connues : UPPER, LOWER, RTRIM, DATEPART( YY seulement ), GetDate, CAST et CONVERT.
Les opérateurs suivants sont également évalués au moment de la compilation si toutes leurs données d'entrée sont connues :
- Opérateurs arithmétiques : +, -, *, /, - unaire,
- Opérateurs logiques : AND, OR, NOT
- Opérateurs de comparaison : <, >, <=, >=, <>, LIKE, IS NULL, IS NOT NULL
L'optimiseur n'évalue pas d'autres fonctions ou opérateurs pendant l'estimation de cardinalité.
Exemples d'évaluation d'expression au moment de la compilation
Considérez la procédure stockée Transact-SQL suivante :
USE AdventureWorks
GO
CREATE PROCEDURE MyProc( @d datetime )
AS
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d+1
Pendant l'optimisation de l'instruction SELECT de cette procédure, l'optimiseur essaie d'évaluer la cardinalité attendue de l'ensemble de résultats pour la condition OrderDate > @d+1. L'expression @d+1 n'est pas assemblée, puisque @d est un paramètre. Toutefois, la valeur de ce paramètre est connue au moment de l'optimisation. Cela permet à l'optimiseur d'estimer précisément la taille de l'ensemble de résultats et de sélectionner le plan de requête correct.
Examinons à présent un exemple similaire, mais dans lequel l'expression @d+1 est remplacée par une variable locale @d2 qui est évaluée dans une instruction SET et non dans la requête.
USE AdventureWorks
GO
CREATE PROCEDURE MyProc2( @d datetime )
AS
BEGIN
DECLARE @d2 datetime
SET @d2 = @d+1
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
WHERE OrderDate > @d2
END
Lorsque l'instruction SELECT de la procédure MyProc2 est optimisée dans SQL Server 2005, la valeur de @d2 n'est pas connue. L'optimiseur utilise donc une estimation par défaut pour la sélectivité de OrderDate > @d2, (en l'occurrence, 30 %).
Méthodes conseillées : Utilisation de l'assemblage de constantes et de l'évaluation d'expressions au moment de la compilation en vue de générer des plans de requête optimaux
Pour être sûr de générer des plans de requêtes optimaux, il est préférable de concevoir les requêtes, procédures stockées et lots de telle sorte que l'optimiseur de requête puisse estimer correctement la sélectivité des conditions en fonction de statistiques relatives à la distribution de vos données. Dans le cas contraire, l'optimiseur doit utiliser une estimation de sélectivité par défaut (30 % dans l'exemple précédent).
Pour garantir que l'estimation de cardinalité de l'optimiseur fournira des estimations correctes, vous devez commencer par vérifier que les options SET de la base de données AUTO_CREATE_STATISTICS et AUTO_UPDATE_STATISTICS ont la valeur ON (configuration par défaut) ou qu'il existe des statistiques créées manuellement pour toutes les colonnes référencées dans une condition de requête. Ensuite, lorsque vous créez les conditions dans vos requêtes, tenez si possible compte des recommandations suivantes :
- Évitez d'inclure des variables locales dans les requêtes. Utilisez plutôt des paramètres, des littéraux ou des expressions.
- Limitez l'emploi d'opérateurs et de fonctions incorporés dans une requête qui contient un paramètre, conformément à la liste fournie dans la section Évaluation d'expressions au moment de la compilation pour l'estimation de cardinalité.
- Vérifiez que les expressions constituées de constantes uniquement qui sont incluses dans la condition d'une requête peuvent être soit assemblées, soit évaluées au moment de la compilation.
- Si vous devez utiliser une variable locale pour évaluer une expression destinée à être incluse dans une requête, envisagez de l'évaluer dans une étendue différente de celle de la requête. Voici quelques méthodes utiles dans ce cas :
- Passez la valeur de la variable à une procédure stockée contenant la requête à évaluer, puis faites en sorte que cette requête utilise le paramètre de la procédure à la place d'une variable locale.
- Créez une chaîne contenant une requête basée en partie sur la valeur de la variable locale, puis exécutez cette chaîne via un code SQL dynamique (EXEC ou sp_executesql).
- Paramétrez la requête et exécutez-la à l'aide de sp_executesql, puis passez la valeur de la variable en tant que paramètre à la requête.
Voir aussi
Concepts
Autres ressources
Analyse de requêtes
Dépannage des requêtes