261
8.2 Gestion avancée des plans d’exécution
Vous spécifiez le type dans le paramètre @type à la création du guide. Voici un
exemple d’utilisation de guide de plan :
CREATE PROCEDURE dbo.GetContactsForPlanGuide
@LastName nvarchar(50) = NULL
AS BEGIN
SET NOCOUNT ON
SELECT FirstName, LastName
FROM Person.Contact
WHERE LastName LIKE @LastName;
END
GO
EXEC dbo.GetContactsForPlanGuide 'Abercrombie'
EXEC dbo.GetContactsForPlanGuide '%'
-- pas bon
GO
EXEC sys.sp_create_plan_guide
@name = N'Guide$GetContactsForPlanGuide$OptimizeForAll',
@stmt = N'SELECT FirstName, LastName
FROM Person.Contact
WHERE LastName LIKE @LastName',
@type = N'OBJECT',
@module_or_batch = N'dbo.GetContactsForPlanGuide',
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@LastName = ''%''))'
EXEC dbo.GetContactsForPlanGuide '%'
-- mieux !
GO
-- suppression
SELECT * FROM sys.plan_guides
EXEC sys.sp_control_plan_guide
@Operation = N'DROP',
@Name = N'Guide$GetContactsForPlanGuide$OptimizeForAll'
Dans cet exemple, nous avons créé une procédure stockée qui est exécutée la première fois avec un paramètre provoquant une compilation de plan d’exécution utilisant un seek d’index, par parameter sniffing (voir section 9.2.1). Le deuxième appel
utilisera un plan d’exécution très défavorable. Nous créons ensuite un guide de plan
qui utilise l’indicateur OPTIMIZE FOR pour forcer une compilation prenant en compte
une valeur de paramètre générique (un scénario du pire, qui sera désagréable pour les
valeurs de paramètre qui profiteraient d’un index, à cause de leur grande sélectivité,
mais qui limitera les dégâts en cas d’envoi de paramètre trop peu sélectif). Pour supprimer le guide de plan, nous utilisons le paramètre @Operation = N'DROP' en appel
de sp_control_plan_guide. Nous pourrions aussi désactiver le plan par la valeur
'DISABLE'.