Paramétrage forcé

Mis à jour : 5 décembre 2005

Vous pouvez remplacer le comportement de paramétrage simple par défaut de SQL Server en spécifiant que toutes les instructions SELECT, INSERT, UPDATE et DELETE de la base de données doivent être paramétrées dans certaines limites. Le paramétrage forcé est activé lors de la définition de l'option PARAMETERIZATION sur FORCED dans l'instruction ALTER DATABASE. Ce type de paramétrage permet d'améliorer les performances de certaines bases de données en réduisant la fréquence des compilations et des recompilations des requêtes. Les bases de données qui peuvent tirer profit du paramétrage forcé sont généralement des bases de données devant gérer un nombre important de requêtes simultanées émanant de sources telles que des applications de point de vente.

Lorsque l'option PARAMETERIZATION a la valeur FORCED, toute valeur littérale apparaissant dans une instruction SELECT, INSERT, UPDATE ou DELETE, dans n'importe quel format, est convertie en paramètre au moment de la compilation de la requête. Les littéraux apparaissant dans les constructions de requêtes suivantes font toutefois exception :

• Les instructions INSERT...EXECUTE.
• Les instructions internes au corps de procédures stockées, de déclencheurs ou de fonctions définies par l'utilisateur, pour lesquelles SQL Server réutilise les plans d'exécution des requêtes.
• Les instructions préparées ayant déjà été paramétrées dans l'application cliente.
• Les instructions contenant des appels de méthode XQuery, où la méthode apparaît dans un contexte nécessitant généralement que ses arguments soient paramétrés (clause WHERE, par exemple). Si la méthode figure dans un contexte où le paramétrage de ses arguments n'est pas requis, le reste de l'instruction est paramétré.
• Les instructions à l'intérieur d'un curseur Transact-SQL. (Les instructions SELECT à l'intérieur des curseurs API sont paramétrées.)
• Les constructions de requêtes non approuvées.
• Les instructions exécutées dans un contexte de désactivation (valeur OFF) de ANSI_PADDING ou ANSI_NULLS.
• Les instructions contenant plus de 2 097 littéraux pouvant être paramétrées.
• Les instructions faisant référence à des variables comme WHERE T.col2 >= @bb.
• Les instructions contenant les indicateurs de requêtes RECOMPILE ou OPTIMIZE FOR.
• Les instructions contenant une clause COMPUTE.
• Les instructions contenant une clause WHERE CURRENT OF.

En outre, les clauses de requête suivantes ne sont pas paramétrées. Notez que dans de tels cas, seules les clauses ne sont pas paramétrées. D'autres clauses au sein de la même requête peuvent être l'objet d'un paramétrage forcé.

• La clause <select_list> d'une instruction SELECT. Notamment les listes SELECT des sous-requêtes et les listes SELECT des instructions INSERT.
• Les instructions SELECT de sous-requêtes apparaissant dans une instruction IF.
• Les clauses TOP, TABLESAMPLE, HAVING, GROUP BY, ORDER BY, OUTPUT...INTO et FOR XML d'une requête.
• Les arguments, qu'il s'agisse d'un argument direct ou d'une sous-expression, des opérateurs OPENROWSET, OPENQUERY, OPENDATASOURCE, OPENXML ou FULLTEXT.
• Les arguments pattern et escape_character d'une clause LIKE.
• L'argument style d'une clause CONVERT.
• Les constantes entières d'une clause IDENTITY.
• Les constantes spécifiées à l'aide de la syntaxe d'extension ODBC.
• Les expressions de constantes pouvant être évaluées lors de la compilation et qui sont des arguments des opérateurs +, -, *, / et %. Lors de l'examen d'un paramétrage forcé éventuel, SQL Server considère qu'une expression est constituée de constantes pouvant être évaluées lors de la compilation lorsque l'une des conditions suivantes est vraie :
  • l'expression ne contient pas de colonnes, de variables ou de sous-requêtes ;
  • l'expression contient une clause CASE.
    Pour plus d'informations sur les expressions transformable en constante, consultez Résolution des problèmes de performances des requêtes : assemblage de constantes et évaluation d'expressions pendant l'estimation de cardinalité.
• Les arguments des clauses d'indicateur de requêtes. Notamment l'argument number_of_rows de l'indicateur de requêtes FAST, l'argument number_of_processors de l'indicateur de requêtes MAXDOP et l'argument number de l'indicateur de requête MAXRECURSION.

Le paramétrage est effectué au niveau des instructions Transact-SQL individuelles. En d'autres termes, les instructions individuelles d'un lot sont paramétrées. Une fois la compilation terminée, la requête paramétrée est exécutée dans le contexte du lot pour lequel elle a été initialement soumise. Dans le cas d'un plan d'exécution mis en cache, vous pouvez déterminer si la requête a été paramétrée en référençant la colonne sql de la vue de gestion dynamique sys.syscacheobjects. Si la requête a été paramétrée, les noms et les types de données des paramètres sont spécifiés avant le texte de chaque lot soumis dans cette colonne, par exemple (@1 tinyint). Pour plus d'informations sur la mise en cache d'un plan de requête, consultez Mise en mémoire cache et réutilisation du plan d'exécution.

Remarque :
Les noms des paramètres sont arbitraires. Les utilisateurs et les applications ne doivent par conséquent pas se fier à un ordre particulier d'affectation des noms. En outre, les éléments suivants peuvent varier dans les versions de SQL Server et les mises à niveau des Service Packs. Les noms des paramètres, le choix des littéraux paramétrés et l'espacement dans le texte paramétré.

Types de données des paramètres

Lorsque SQL Server paramètre des littéraux, les paramètres sont convertis dans les types de données suivants :

• Les littéraux entiers dont la taille correspondrait en d'autres circonstances au type de données int sont paramétrés sur int. Les littéraux de taille plus importante qui font partie d'un prédicat impliquant un opérateur de comparaison quelconque (notamment <, <=, =, !=, >, >=, , !<, !>, <>, ALL, ANY, SOME, BETWEEN et IN) sont paramétrés sur numeric(38,0). Les littéraux de taille plus importante qui ne font pas partie d'un prédicat impliquant un opérateur de comparaison sont paramétrés sur numeric dont la précision suffit à prendre en charge leur taille et dont l'échelle correspond à 0.
• Les littéraux numériques à virgule fixe qui font partie d'un prédicat impliquant un opérateur de comparaison sont paramétrés sur numeric dont la précision est de 38 et dont l'échelle suffit à prendre en charge leur taille. Les littéraux numériques à virgule fixe qui ne font pas partie d'un prédicat impliquant un opérateur de comparaison sont paramétrés sur numeric dont la précision et l'échelle suffisent à prendre en charge leur taille.
• Les littéraux numériques à virgule flottante sont paramétrés sur float(53).
• Les littéraux de chaîne non-Unicode sont paramétrés sur varchar(8000) lorsqu'ils comptent moins de 8 000 caractères et sur varchar(max) lorsqu'ils en comptent plus de 8 000.
• Les littéraux de chaîne Unicode sont paramétrés sur nvarchar(4000) lorsqu'ils comptent moins de 4 000 caractères Unicode et sur nvarchar(max) lorsqu'ils en comptent plus de 4 000.
• Les littéraux binaires sont paramètres en varbinary(8000) si le littéral n'excède pas 8 000 octets. S'il dépasse 8 000 octets, il est converti en varbinary(max).
• Les littéraux de type monétaire sont paramétrés sur money.

Principes d'utilisation du paramétrage forcé

Lorsque vous affectez à l'option PARAMETERIZATION la valeur FORCED, tenez compte des points suivants :

• Le paramétrage forcé convertit les constantes des littéraux d'une requête en paramètres lors de la compilation d'une requête. Par conséquent, l'optimiseur de requête peut opter pour des plans d'exécution de requêtes non optimisés. Plus spécifiquement, il est moins probable que l'optimiseur de requête établisse une correspondance avec une vue indexée ou un index d'une colonne calculée. Il peut également opter pour des plans non optimisés dans le cas de requêtes soumises pour des tables partitionnées ou des vues partitionnées et distribuées. Il n'est pas recommandé d'utiliser le paramétrage forcé dans des environnements reposant principalement sur des vues indexées ou des index de colonnes calculées. Dans l'ensemble, l'option PARAMETERIZATION FORCED devrait être utilisée exclusivement par des administrateurs de bases de données expérimentés qui se seront assurés que cela n'affectera pas les performances.
• Les requêtes distribuées qui font référence à plusieurs bases de données sont éligibles pour le paramétrage forcé pour autant que l'option PARAMETERIZATION ait la valeur FORCED dans la base de données dont le contexte fait l'objet de la requête.
• L'affectation de la valeur FORCED à l'option PARAMETERIZATION vide tous les plans de requêtes du cache de plans d'une base de données, à l'exception de ceux qui sont en cours de compilation, de recompilation ou d'exécution. Tout plan d'une requête en cours de compilation ou d'exécution lors de la modification des paramètres est paramétré lors de la prochaine exécution de la requête.
• La définition de l'option PARAMETERIZATION est une opération en ligne qui ne requiert aucun verrou exclusif au niveau de la base de données.
• Le paramétrage forcé est désactivé (SIMPLE) lorsque la compatibilité d'une base de données SQL Server 2005 est définie sur 80 ou qu'une base de données sur une version antérieure du serveur est attaché à un serveur SQL Server 2005.
• Le paramètre actuel de l'option PARAMETERIZATION est conservé en cas de réattachement ou de restauration d'une base de données.

Pour remplacer le comportement de paramétrage forcé, il suffit de spécifier qu'un paramétrage simple soit tenté sur une requête unique, à l'instar des autres requêtes de syntaxe équivalente mais présentant des valeurs de paramètres différentes. À l'inverse, vous pouvez spécifier que le paramétrage forcé soit tenté sur seul jeu de requêtes de syntaxe équivalente, même en cas de désactivation de l'option de paramétrage forcé dans la base de données. Vous pouvez utiliser les repères de plan à cet effet. Pour plus d'informations, consultez Indication du comportement du paramétrage de requêtes grâce aux repères de plan.

Remarque :
Quand l'option PARAMETERIZATION est définie à la valeur FORCED, il se peut que le rapport des messages d'erreur diffère du paramétrage simple sur différents points : il est possible qu'il y ait un plus grand nombre de messages d'erreur que dans le cas d'un paramétrage simple et les numéros de ligne où interviennent les erreurs peuvent ne pas être corrects.

Voir aussi

Référence

Objet SQLServer:SQL Statistics

Concepts

Paramétrage simple

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications

Version	Historique
5 décembre 2005	Nouveau contenu : Remarque supplémentaire sur le message d'erreur établissant que l'option PARAMETERIZATION est définie avec la valeur FORCED. Contenu modifié : Réorganisation de la liste des exceptions par rapport à la valeur littérale qui sont converties en paramètres pendant la compilation des requêtes dans le cas d'un paramétrage forcé. Ajouts et suppressions dans la liste.