297
9.2 Maîtrise de la compilation
L’inconvénient de cette approche est évidemment de coder en dur une valeur de
la colonne, qui peut évoluer à travers le temps, ou disparaître.
En SQL Server 2008, l’indicateur OPTIMIZE FOR est complété de la façon suivante :
OPTION (OPTIMIZE FOR (@variable = UNKNOWN))
ou OPTION (OPTIMIZE FOR UNKNOWN)
qui permettent, comme à travers l’utilisation de variables locales, de faire une optimisation
générique à partir d’une distribution moyenne des valeurs des colonnes, et donc d’assurer la
stabilité du plan, soit pour une variable, soit pour toutes les variables utilisées dans la requête.
Ces options sont très utiles dans les procédures stockées complexes, où le problème du parameter sniffing se fait le plus aigu, notamment lors de branchements conditionnels. Prenons le cas de cette procédure « modulaire » :
CREATE PROCEDURE Person.GetContactsByWhatever
@NamePartType tinyint,
@NamePart nvarchar(50)
AS BEGIN
SET NOCOUNT ON
IF (@NamePartType = 1)
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE FirstName LIKE @NamePart
ELSE IF (@NamePartType = 2)
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE @NamePart
ELSE IF (@NamePartType = 3)
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE EmailAddress LIKE @NamePart
END
Le but, louable au premier abord, est de créer une procédure générique. Malheureusement, la première valeur envoyée dans @NamePart conditionne le plan d’exécution des trois instructions, pour trois colonnes différentes ! Dans ce cas, la
recompilation sélective est très intéressante, car elle ne générera une compilation
que de l’instruction utilisée (dans laquelle on se branche), au lieu d’une recompilation générale comme avec un WITH RECOMPILE. Notons tout de même que la
meilleure solution reste d’éviter la création de procédures génériques.
sp_recompile
La procédure stockée système sp_recompile permet de marquer une procédure ou un
déclencheur pour être recompilée. Dans les faits, elle supprime la procédure du
cache. Vous pouvez aussi indiquer une table ou une vue. Dans ce cas, toutes les
procédures référençant cet objet seront recompilées à leur prochaine exécution.
Cette procédure est aujourd’hui peu utile, SQL Server faisant ce travail lui-même,
9.2 Maîtrise de la compilation
L’inconvénient de cette approche est évidemment de coder en dur une valeur de
la colonne, qui peut évoluer à travers le temps, ou disparaître.
En SQL Server 2008, l’indicateur OPTIMIZE FOR est complété de la façon suivante :
OPTION (OPTIMIZE FOR (@variable = UNKNOWN))
ou OPTION (OPTIMIZE FOR UNKNOWN)
qui permettent, comme à travers l’utilisation de variables locales, de faire une optimisation
générique à partir d’une distribution moyenne des valeurs des colonnes, et donc d’assurer la
stabilité du plan, soit pour une variable, soit pour toutes les variables utilisées dans la requête.
Ces options sont très utiles dans les procédures stockées complexes, où le problème du parameter sniffing se fait le plus aigu, notamment lors de branchements conditionnels. Prenons le cas de cette procédure « modulaire » :
CREATE PROCEDURE Person.GetContactsByWhatever
@NamePartType tinyint,
@NamePart nvarchar(50)
AS BEGIN
SET NOCOUNT ON
IF (@NamePartType = 1)
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE FirstName LIKE @NamePart
ELSE IF (@NamePartType = 2)
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE @NamePart
ELSE IF (@NamePartType = 3)
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE EmailAddress LIKE @NamePart
END
Le but, louable au premier abord, est de créer une procédure générique. Malheureusement, la première valeur envoyée dans @NamePart conditionne le plan d’exécution des trois instructions, pour trois colonnes différentes ! Dans ce cas, la
recompilation sélective est très intéressante, car elle ne générera une compilation
que de l’instruction utilisée (dans laquelle on se branche), au lieu d’une recompilation générale comme avec un WITH RECOMPILE. Notons tout de même que la
meilleure solution reste d’éviter la création de procédures génériques.
sp_recompile
La procédure stockée système sp_recompile permet de marquer une procédure ou un
déclencheur pour être recompilée. Dans les faits, elle supprime la procédure du
cache. Vous pouvez aussi indiquer une table ou une vue. Dans ce cas, toutes les
procédures référençant cet objet seront recompilées à leur prochaine exécution.
Cette procédure est aujourd’hui peu utile, SQL Server faisant ce travail lui-même,