Exemple d'application forcée d'un plan : Création d'un repère de plan pour forcer l'application d'un plan obtenu par la réécriture d'une requête
Souvent, la solution la plus pratique pour améliorer le plan d'une requête consiste à réécrire manuellement cette dernière pour forcer l'utilisation des index, des algorithmes de jointures et des ordres de jointures en utilisant des indicateurs de requête, sans toutefois modifier la signification logique de la requête. Cette méthode peut cependant être exclue si la requête fait partie d'une application déployée. L'utilisation de repères de plan peut aider dans cette situation. Les repères de plan fonctionnent en attachant des indicateurs de requête ou des plans de requête aux requêtes quand il n'est pas possible ou souhaitable de modifier directement le texte de ces dernières. Pour plus d'informations, consultez Optimisation des requêtes dans les applications déployées à l'aide des repères de plan.
Pour réécrire manuellement une requête, capturez son plan puis appliquez le plan capturé à la requête d'origine avec un repère de plan contenant le plan capturé, en procédant comme suit :
Déterminez comment modifier la requête (en changeant l'ordre de jointure, en utilisant FORCE ORDER, des indicateurs de jointure, des indicateurs d'index ou d'autres techniques), de façon à créer un plan correct sans toutefois modifier la signification logique de la requête.
Capturez le plan pour la requête réécrite, soumise comme la requête d'origine (par exemple à l'aide de sp_executesql, de sp_cursorprepexec ou au moyen d'un traitement autonome).
Créez un repère de plan forçant l'application du plan convenable obtenu à la requête initiale.
À l'aide du SQL Server Profiler, sélectionnez les événements Plan Guide Successful et Plan Guide Unsuccessful de la catégorie Performance, puis exécutez la requête d'origine. Examinez le SQL Server Profiler pour vérifier que la requête utilise le repère de plan
Exemple
Supposons que la requête ci-dessous génère un plan de requête trop lent :
USE AdventureWorks;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
,e.Title
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson sp
INNER JOIN Sales.SalesOrderHeader soh ON sp.SalesPersonID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID
INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
WHERE st.[Group] = @p1',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
Vous pouvez réécrire la requête comme suit pour qu'elle garde la même signification logique tout en utilisant un ordre de jointure différent, lequel sera forcé.
USE AdventureWorks;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
,e.Title
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson sp
INNER JOIN Sales.SalesOrderHeader soh ON sp.SalesPersonID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID
INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
WHERE st.[Group] = @p1
OPTION (FORCE ORDER)',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
Pour créer un repère de plan pour imposer le plan pour la requête réécrite sur la requête d'origine, capturez le plan dans une variable et spécifiez la variable dans l'instruction de repère de plan comme cela est illustré dans le code ci-dessous.
DBCC FREEPROCCACHE;
GO
USE AdventureWorks;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
,e.Title
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson sp
INNER JOIN Sales.SalesOrderHeader soh ON sp.SalesPersonID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID
INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
WHERE st.[Group] = @p1
OPTION (FORCE ORDER)',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
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'%Sales.SalesPerson%' AND st.text LIKE N'%OPTION (FORCE ORDER)%');
EXEC sp_create_plan_guide
@name = N'ForceOrderGuide1',
@stmt = N'SELECT
soh.SalesPersonID
,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
,e.Title
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson sp
INNER JOIN Sales.SalesOrderHeader soh ON sp.SalesPersonID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID
INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
WHERE st.[Group] = @p1',
@type = N'SQL',
@module_or_batch = NULL,
@params = N'@p1 nvarchar(80)',
@hints = @xml_showplan;
GO
SELECT * FROM sys.plan_guides;
GO
Avant d'exécuter la requête d'origine, créez une trace en utilisant le SQL Server Profiler et sélectionnez les événements Plan Guide Successful et Plan Guide Unsuccessful dans la catégorie Performance. Exécutez la requête d'origine et vérifiez les résultats de la requête dans la sortie de la trace.
USE AdventureWorks;
GO
EXEC sp_executesql
@stmt = N'SELECT
soh.SalesPersonID
,c.FirstName + '' '' + COALESCE(c.MiddleName, '''') + '' '' + c.LastName AS FullName
,e.Title
,st.Name AS SalesTerritory
,soh.SubTotal
,YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
FROM Sales.SalesPerson sp
INNER JOIN Sales.SalesOrderHeader soh ON sp.SalesPersonID = soh.SalesPersonID
INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.EmployeeID
INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
WHERE st.[Group] = @p1',
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
Voir aussi