• Articolo

Scenario di utilizzo forzato del piano: Creazione di una guida di piano che specifica un piano della query

Quando si crea una guida di piano è possibile forzare un piano della query utilizzando la stored procedure di sistema sp_create_plan_guide e specificando un piano di query in formato Showplan XML per la query del parametro @hints. Le guide di piano consentono di applicare hint o piani di query alle query delle applicazioni distribuite quando non si può o non si desidera modificare direttamente l'applicazione. Per ulteriori informazioni sulle guide di piano, vedere Ottimizzazione delle query nelle applicazioni distribuite tramite le guide di piano. In questo scenario si associa un piano di query specifico alla guida di piano.

Si supponga che l'applicazione contenga la stored procedure seguente:

USE AdventureWorks2008R2;
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

Si presupponga che la maggior parte delle query che eseguono questa procedura fornisca prestazioni insufficienti perché il piano di query non è ottimizzato per un valore rappresentativo o che riflette il caso peggiore per il parametro @CountryRegion. È necessario forzare questa stored procedure affinché utilizzi un piano di query specifico ottimizzato per un paese specifico. Non è possibile modificare la stored procedure direttamente nell'applicazione poiché è stata acquistata da un fornitore di software indipendente. Tuttavia, è possibile creare una guida di piano per la query, specificando un piano di query nella guida di piano ottimizzata per il valore rappresentativo.

Per allegare un piano di query a una guida di piano, è innanzitutto necessario ottenere un piano di query ottimizzato per la query della stored procedure. A questo scopo, occorre eseguire la query definita nella stored procedure, sostituendo il parametro @CountryRegion con un valore costante rappresentativo o che riflette il caso peggiore. Si esegue quindi una query alla vista a gestione dinamica sys.dm_exec_query_stats per ottenere il piano di query dalla cache dei piani. Si consiglia di assegnare Showplan XML a una variabile; in caso contrario, è necessario utilizzare caratteri di escape per ogni virgoletta singola nello Showplan XML facendola precedere da un'altra virgoletta singola. Da ultimo, si crea una guida di piano che specifichi Showplan XML nel parametro @hints.

Esempio

Nel seguente esempio di codice sono illustrati i passaggi richiesti per la creazione di un piano di query ottimizzato per la stored procedure Sales.GetSalesOrderByCountryRegion e il suo successivo collegamento a una guida di piano. Al momento dell'esecuzione della stored procedure, la query definita nella procedura viene fatta corrispondere alla guida di piano e il Query Optimizer utilizza il piano di query specificato nella guida di piano.

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');