Spécifier le comportement de paramétrage des requêtes à l’aide de repères de plan

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Lorsque vous définissez l’option de base de données PARAMETERIZATION sur SIMPLE, l’optimiseur de requêtes SQL Server peut choisir de paramétrer les requêtes. Ce paramétrage remplace toutes les valeurs littérales d’une requête par des paramètres. Ce processus s'appelle le paramétrage simple. Lorsque SIMPLE le paramétrage est en vigueur, vous ne pouvez pas contrôler les requêtes paramétrables et les requêtes qui ne le sont pas. Toutefois, vous pouvez spécifier que toutes les requêtes d’une base de données soient paramétrées en définissant l’option de base de données PARAMETERIZATION sur FORCED. Ce processus s'appelle le paramétrage forcé.

Vous pouvez outrepasser ce comportement de paramétrage d'une base de données par le biais de repères de plan des façons suivantes :

Supposons la requête suivante portant sur la base de données AdventureWorks2025 :

SELECT pi.ProductID,
       SUM(pi.Quantity) AS Total
FROM Production.ProductModel AS pm
     INNER JOIN Production.ProductInventory AS pi
         ON pm.ProductModelID = pi.ProductID
WHERE pi.ProductID = 101
GROUP BY pi.ProductID, pi.Quantity
HAVING SUM(pi.Quantity) > 50;

En tant qu’administrateur de base de données, vous déterminez que vous ne souhaitez pas activer le paramétrage forcé sur toutes les requêtes de la base de données. Toutefois, vous souhaitez éviter les coûts de compilation sur toutes les requêtes qui sont syntactiquement équivalentes à la requête précédente, mais diffèrent uniquement dans leurs valeurs littérales constantes. En d'autres termes, il peut être préférable que la requête soit paramétrable afin qu'un plan de requête correspondant à ce type de requête soit réutilisé. Dans ce cas, suivez les étapes ci-dessous :

1. Récupérez la forme paramétrable de la requête. La seule façon sûre d’obtenir cette valeur pour l’utiliser dans sp_create_plan_guide consiste à utiliser la procédure stockée système sp_get_query_template.

2. Créez le guide de plan sur la forme paramétrée de la requête, en spécifiant l’indicateur de requête PARAMETERIZATION FORCED.

   Important

   Dans le cadre du paramétrage d’une requête, SQL Server affecte un type de données aux paramètres remplaçant les valeurs littérales, en fonction de la valeur et de la taille du littéral. Le même processus se produit pour la valeur des littéraux constants passés au paramètre de sortie @stmt de sp_get_query_template. Étant donné que le type de données spécifié dans l@params'argument de sp_create_plan_guide doit correspondre à celui de la requête tel qu'il est paramétré par SQL Server, vous devrez peut-être créer plusieurs repères de plan pour couvrir la plage complète des valeurs de paramètre possibles pour la requête.

Le script suivant peut être utilisé aussi bien pour obtenir la requête paramétrable que pour créer plus tard un repère de plan s'y rapportant :

DECLARE @stmt AS NVARCHAR (MAX);
DECLARE @params AS NVARCHAR (MAX);

EXECUTE sp_get_query_template
    N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total
      FROM Production.ProductModel AS pm
      INNER JOIN Production.ProductInventory AS pi ON pm.ProductModelID = pi.ProductID
      WHERE pi.ProductID = 101
      GROUP BY pi.ProductID, pi.Quantity
      HAVING SUM(pi.Quantity) > 50',
@stmt OUTPUT, @params OUTPUT;

EXECUTE sp_create_plan_guide N'TemplateGuide1',
@stmt, N'TEMPLATE', NULL,
@params, N'OPTION(PARAMETERIZATION FORCED)';

Si le paramétrage forcé est déjà activé sur la base de données, vous pouvez le remplacer pour des requêtes spécifiques. Pour paramétrer l’exemple de requête et les requêtes syntaxiquement équivalentes selon les règles de paramétrage simple, spécifiez PARAMETERIZATION SIMPLE au lieu de PARAMETERIZATION FORCED dans la clause OPTION.

Contenu connexe

• Guide d’architecture de traitement des requêtes
• sp_create_plan_guide (Transact-SQL)