293
9.2 Maîtrise de la compilation
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE 'Ackerman'
-- 911 lignes à retourner
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE 'A%'
À l’évidence, le plan d’exécution sera différent : la sélectivité de l’index est
excellente pour répondre à la première requête, et SQL Server choisira un seek ; par
contre, la deuxième requête sera résolue par un scan, probablement moins coûteux.
Vous comprenez déjà le problème : si nous créons une procédure stockée de ce type :
CREATE PROCEDURE Person.GetContactByLastName
@LastNameStart nvarchar(50)
AS BEGIN
SET NOCOUNT ON
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE @LastNameStart
END
Nous pouvons aisément vérifier que le plan d’exécution mis en cache dépend du
paramètre envoyé lors du premier appel de la procédure. Exécutons une première fois
la procédure, et examinons le plan en cache :
EXEC Person.GetContactByLastName 'A%'
GO
SELECT cp.size_in_bytes, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE
st.dbid = DB_ID('Adventureworks') AND
st.objectid = OBJECT_ID('Adventureworks.Person.GetContactByLastName')
Dans le plan XML, nous trouvons l’opérateur de scan d’index clustered, donc de
table :
EstimateRebinds="0" EstimateRewinds="0">
Si nous appelons la procédure avec le paramètre 'Ackerman', et que nous examinons le plan d’exécution dans SSMS, nous constatons que l’opérateur est toujours
un scan : le plan a été réutilisé, alors qu’il n’est de loin pas, dans ce cas, le plus efficace par rapport au paramètre envoyé.
La procédure est compilée en utilisant une fonctionnalité appelée parameter sniffing (littéralement « flairage de paramètre ») : le moteur d’optimisation détecte la
9.2 Maîtrise de la compilation
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE 'Ackerman'
-- 911 lignes à retourner
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE 'A%'
À l’évidence, le plan d’exécution sera différent : la sélectivité de l’index est
excellente pour répondre à la première requête, et SQL Server choisira un seek ; par
contre, la deuxième requête sera résolue par un scan, probablement moins coûteux.
Vous comprenez déjà le problème : si nous créons une procédure stockée de ce type :
CREATE PROCEDURE Person.GetContactByLastName
@LastNameStart nvarchar(50)
AS BEGIN
SET NOCOUNT ON
SELECT FirstName, LastName, EmailAddress
FROM Person.Contact
WHERE LastName LIKE @LastNameStart
END
Nous pouvons aisément vérifier que le plan d’exécution mis en cache dépend du
paramètre envoyé lors du premier appel de la procédure. Exécutons une première fois
la procédure, et examinons le plan en cache :
EXEC Person.GetContactByLastName 'A%'
GO
SELECT cp.size_in_bytes, qp.query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
WHERE
st.dbid = DB_ID('Adventureworks') AND
st.objectid = OBJECT_ID('Adventureworks.Person.GetContactByLastName')
Dans le plan XML, nous trouvons l’opérateur de scan d’index clustered, donc de
table :
EstimateRebinds="0" EstimateRewinds="0">
Si nous appelons la procédure avec le paramètre 'Ackerman', et que nous examinons le plan d’exécution dans SSMS, nous constatons que l’opérateur est toujours
un scan : le plan a été réutilisé, alors qu’il n’est de loin pas, dans ce cas, le plus efficace par rapport au paramètre envoyé.
La procédure est compilée en utilisant une fonctionnalité appelée parameter sniffing (littéralement « flairage de paramètre ») : le moteur d’optimisation détecte la