Conception de repères de plan pour les requêtes paramétrées
Vous pouvez créer un repère de plan sur une requête qui est paramétrée. Une requête peut être paramétrée pour l'une des raisons suivantes :
- La requête est soumise à l'aide de sp_executesql.
- Le paramétrage forcé est activé dans la base de données. De ce fait, toutes les requêtes éligibles se retrouvent paramétrées.
- Un repère de plan distinct a été créé sur une classe de requêtes à laquelle cette requête appartient, indiquant que toutes les requêtes doivent être paramétrées.
Lorsque vous créez un repère de plan sur une requête paramétrée, vous créez en fait un repère de plan pour toutes les requêtes qui se paramètrent sous la même forme, et se différencient uniquement par leurs valeurs littérales constantes. Par exemple, dans une base de données où le paramétrage forcé est activé, les deux requêtes suivantes se paramètrent sous la même forme :
SELECT pi.ProductID, SUM(pi.Quantity) AS Total
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID
WHERE pi.ProductID = 101
GROUP BY pi.ProductID, pi.Quantity
HAVING sum(pi.Quantity) > 50
SELECT pi.ProductID, SUM(pi.Quantity) AS Total
FROM Production.ProductModel pm INNER JOIN Production.ProductInventory pi ON pm.ProductModelID = pi.ProductID
WHERE pi.ProductID = 101
GROUP BY pi.ProductID, pi.Quantity
HAVING sum(pi.Quantity) > 100
Pour créer un repère de plan sur des requêtes paramétrées, créez un repère de plan de type SQL et spécifiez la forme paramétrée de la requête dans la procédure stockée sp_create_plan_guide.
Par exemple, pour obtenir la forme paramétrée de l'une des deux requêtes de l'exemple précédent et créer un repère de plan sur cette dernière de façon à obliger l'optimiseur à utiliser une jointure hachée, procédez ainsi :
- Obtenez la forme paramétrée de la requête en exécutant sp_get_query_template.
- Si la requête n'est pas déjà paramétrée par SQL Server à l'aide de sp_executesql ou de l'option SET de base de données PARAMETERIZATION FORCED, créez un repère de plan de type TEMPLATE pour forcer le paramétrage.
- Créez un repère de plan de type SQL sur la requête paramétrée.
Le lot suivant exécute l'ensemble des trois étapes suivantes :
--Obtain the paramaterized form of the query:
DECLARE @stmt nvarchar(max)
DECLARE @params nvarchar(max)
EXEC sp_get_query_template N'SELECT pi.ProductID, SUM(pi.Quantity) AS Total FROM Production.ProductModel pm INNER JOIN Production.ProductInventory 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
--Force parameterization of the query. (This step is only required
--if the query is not already being parameterized.)
EXEC sp_create_plan_guide N'TemplateGuide1',
@stmt,
N'TEMPLATE',
NULL,
@params, N'OPTION(PARAMETERIZATION FORCED)'
--Create a plan guide on the parameterized query
EXEC sp_create_plan_guide N'GeneralizedGuide1',
@stmt,
N'SQL',
NULL,
@params,
N'OPTION(HASH JOIN)'
Le repère de plan s'appliquera désormais à toutes les requêtes qui se paramètrent sous la forme spécifiée, mais contiennent différentes valeurs littérales constantes.
Voir aussi
Concepts
Optimisation des requêtes dans les applications déployées à l'aide des repères de plan
Autres ressources
Performance des requêtes
sp_create_plan_guide (Transact-SQL)