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

Article
12/15/2008

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'association de repères de plan à l'indicateur de requête USE PLAN peut dans ce cas vous tirer d'affaire. Les repères de plan lient des indicateurs de requête aux requêtes quand il n'est pas possible ou pas 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 et appliquez ensuite le plan capturé à la requête d'origine avec un repère de plan contenant l'indicateur USE PLAN, 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 et 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 lot d'instructions autonome).
Modifiez une copie de la requête d'origine en attachant une clause d'indicateur de requête OPTION (USE PLAN) contenant le plan capturé, puis vérifiez si vous pouvez forcer l'application du plan capturé à la requête.
Si le test échoue, réécrivez la requête ou déboguez-la jusqu'à obtenir un plan correct dont l'application à la requête d'origine peut être forcée.
Créez un repère de plan forçant l'application du plan convenable obtenu à la requête initiale.

Exemple

Supposons que la requête suivante génère un plan de requête trop lent :

USE AdventureWorks;
GO
SET STATISTICS XML ON;
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
SET STATISTICS XML OFF;
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
SET STATISTICS XML ON;
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].[SalesTerritory] st -- Moved this join earlier 
    ON sp.[TerritoryID] = st.[TerritoryID] 
    INNER JOIN [Sales].[SalesOrderHeader] soh 
    ON sp.[SalesPersonID] = soh.[SalesPersonID]
    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)',  -- force join order to be as specified in FROM list
@params = N'@p1 nvarchar(80)',
@p1 = N'North America';
GO
SET STATISTICS XML OFF
GO

Après la capture du plan de requête STATISTICS XML pour la requête réécrite et après avoir vérifié qu'il fonctionnait avec la requête initiale, un repère de plan est créé pour forcer l'application du plan à cette dernière, comme suit :

EXEC sp_create_plan_guide
@name = N'ForceOrderGuide',
@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 = N'OPTION (USE PLAN 
N''… put XML showplan for modified query here …'')'

Vous devez vous assurez de l'échappement de tout guillemet simple (') à l'intérieur du plan de requête XML, en le remplaçant par quatre guillemets simples (''''), avant de substituer le plan de requête dans la chaîne @hints. En effet, ce plan est imbriqué dans deux littéraux de chaîne.

Voir aussi

Partager via

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

Exemple

Voir aussi

Tâches

Concepts

Autres ressources

Aide et Informations

Ressources supplémentaires