Partager via

  • Article

Exemple d'application forcée d'un plan : Créer un repère de plan qui spécifie un plan de requête

Vous pouvez imposer un plan de requête lorsque vous créez un repère de plan en utilisant la procédure stockée système sp_create_plan_guide et en spécifiant un plan de requête sous forme de plan d'exécution de requêtes pour la requête dans le paramètre @hints. Les repères de plan sont utilisés pour appliquer des indicateurs de requête ou des plans de requête aux requêtes dans les applications déployées si vous ne pouvez ou ne souhaitez pas modifier directement ces dernières. Pour plus d'informations sur les repères de plan, consultez Optimisation des requêtes dans les applications déployées à l'aide des repères de plan. Dans ce scénario, vous associez un plan de requête spécifique au repère de plan.

Supposons que votre application contienne la procédure stockée système suivante :

USE AdventureWorks;

GO

CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))

AS

BEGIN

SELECT h.SalesOrderID, h.OrderDate, h.Comment

FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t

WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion;

END;

GO

Supposons que la plupart des requêtes qui exécutent cette procédure ont une efficacité médiocre parce que le plan de requête n'est pas optimisé pour une valeur représentative ou « pessimiste » pour le paramètre @CountryRegion. Vous souhaitez forcer cette procédure stockée à utiliser un plan de requête spécifique qui a été optimisé pour un pays ou une région spécifique. Toutefois, vous ne pouvez pas modifier directement la procédure stockée dans l'application car vous vous êtes procuré celle-ci auprès d'un éditeur de logiciels. À la place, vous pouvez créer un repère de plan pour la requête, en spécifiant un plan de requête dans le repère de plan qui a été optimisé pour la valeur représentative.

Pour joindre un plan de requête à un repère de plan, vous devez obtenir en premier lieu un plan de requête optimisé pour la requête dans la procédure stockée. Pour cela, vous exécutez la requête définie dans la procédure stockée, en substituant une valeur constante représentative, ou « pessimiste », au paramètre @CountryRegion. Ensuite, vous interrogez la vue de gestion dynamique sys.dm_exec_query_stats pour obtenir le plan de requête à partir du cache des plans. Nous vous recommandons d'assigner le plan d'exécution de requêtes XML à une variable ; sinon, vous devez isoler tout guillemet simple dans le plan d'exécution de requêtes XML en le faisant précéder par un autre guillemet simple. Enfin, vous créez un repère de plan qui spécifie le plan d'exécution de requêtes XML dans le paramètre @hints.

Exemple

L'exemple de code suivant montre les étapes requises pour obtenir un plan de requête optimisé pour la procédure stockée Sales.GetSalesOrderByCountryRegion et le joindre à un repère de plan. Lorsque la procédure stockée est exécutée, la requête définie dans la procédure est mise en correspondance avec le repère de plan et l'optimiseur de requête utilise le plan de requête spécifié dans le repère de plan.

CREATE PROCEDURE Sales.GetSalesOrderByCountryRegion (@CountryRegion nvarchar(60))
AS
BEGIN
    SELECT h.SalesOrderID, h.OrderDate, h.Comment
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion;
END;
GO
-- Execute the query based on a representative or "worst-case" scenario.
GO
SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = N'US';
GO
-- Retrieve the query plan for the previous query. Assign the query plan to a variable and attach the query plan to a plan guide.
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs 
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = N''US'';%');

EXEC sp_create_plan_guide 
    @name = N'Guide_for_GetSalesByCountryRegion',
    @stmt = N'SELECT h.SalesOrderID, h.OrderDate, h.Comment
    FROM Sales.SalesOrderHeader AS h, Sales.Customer AS c, Sales.SalesTerritory AS t
    WHERE h.CustomerID = c.CustomerID AND c.TerritoryID = t.TerritoryID AND CountryRegionCode = @CountryRegion',
    @type = N'OBJECT',
    @module_or_batch = N'Sales.GetSalesOrderByCountryRegion',
    @params = NULL,
    @hints = @xml_showplan;
GO
SELECT * FROM sys.plan_guides
WHERE scope_object_id = OBJECT_ID(N'Sales.GetSalesOrderByCountryRegion');