306
Chapitre 9. Optimisation des procédures stockées
Deux conseils s’imposent donc :
1. Maintenez des états de session consistants en affectant les options à la connexion et éviter de les changer en cours de route, surtout à l’intérieur des procédures stockées (le SET NOCOUNT n’entre pas dans cette catégorie, il ne provoque
aucune recompilation, puisqu’il ne change pas le comportement des requêtes).
2. Préfixez toujours vos objets par leur nom de schéma.
En ce qui concerne les options de session, attention aux différentes variétés de
bibliothèques client. Les anciennes méthodes de connexion, telles que ODBC, ne
placent pas par défaut les mêmes valeurs d’option que les méthodes plus modernes,
comme ADO.NET. Pour savoir quelles sont les valeurs des options de session, vous
pouvez utiliser la commande DBCC USEROPTIONS, ou les événements Session / ExistingConnection et Security Audit / Audit Login.
La différence entre le plan compilé et le plan d’exécution peut être observée via la
vue sys.dm_exec_query_stats, qui référence le plan compilé dans la colonne
sql_handle et le plan d’exécution dans la colonne plan_handle. Les handles sont des
hachages MD5 générés à partir du plan entier, ils sont donc garantis uniques par plan. Ils
peuvent être passés à la fonction sys.dm_exec_sql_text pour voir le contenu du plan.
Lorsque nous avons extrait dans les requêtes précédentes des plans d’exécution
du cache, à l’aide de la vue sys.dm_exec_cached_plans et de la colonne plan_handle,
nous avions accès à la totalité du plan compilé, et non aux Cstmt individuels. Cette
vision était celle d’un cache particulier, vous pouvez en faire l’expérience avec cette
requête d’exemple :
DBCC FREEPROCCACHE
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SELECT TOP 10 FirstName + ' ' + MiddleName + ' ' + LastName
FROM Person.Contact
GO
SET CONCAT_NULL_YIELDS_NULL OFF
GO
SELECT TOP 10 FirstName + ' ' + MiddleName + ' ' + LastName
FROM Person.Contact
GO
-- plusieurs plan_handle pour le même sql_handle
SELECT
cp.usecounts,
cp.size_in_bytes,
st.text
FROM sys.dm_exec_cached_plans cp
OUTER APPLY sys.dm_exec_sql_text (cp.plan_handle) st
JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
GO
Chapitre 9. Optimisation des procédures stockées
Deux conseils s’imposent donc :
1. Maintenez des états de session consistants en affectant les options à la connexion et éviter de les changer en cours de route, surtout à l’intérieur des procédures stockées (le SET NOCOUNT n’entre pas dans cette catégorie, il ne provoque
aucune recompilation, puisqu’il ne change pas le comportement des requêtes).
2. Préfixez toujours vos objets par leur nom de schéma.
En ce qui concerne les options de session, attention aux différentes variétés de
bibliothèques client. Les anciennes méthodes de connexion, telles que ODBC, ne
placent pas par défaut les mêmes valeurs d’option que les méthodes plus modernes,
comme ADO.NET. Pour savoir quelles sont les valeurs des options de session, vous
pouvez utiliser la commande DBCC USEROPTIONS, ou les événements Session / ExistingConnection et Security Audit / Audit Login.
La différence entre le plan compilé et le plan d’exécution peut être observée via la
vue sys.dm_exec_query_stats, qui référence le plan compilé dans la colonne
sql_handle et le plan d’exécution dans la colonne plan_handle. Les handles sont des
hachages MD5 générés à partir du plan entier, ils sont donc garantis uniques par plan. Ils
peuvent être passés à la fonction sys.dm_exec_sql_text pour voir le contenu du plan.
Lorsque nous avons extrait dans les requêtes précédentes des plans d’exécution
du cache, à l’aide de la vue sys.dm_exec_cached_plans et de la colonne plan_handle,
nous avions accès à la totalité du plan compilé, et non aux Cstmt individuels. Cette
vision était celle d’un cache particulier, vous pouvez en faire l’expérience avec cette
requête d’exemple :
DBCC FREEPROCCACHE
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SELECT TOP 10 FirstName + ' ' + MiddleName + ' ' + LastName
FROM Person.Contact
GO
SET CONCAT_NULL_YIELDS_NULL OFF
GO
SELECT TOP 10 FirstName + ' ' + MiddleName + ' ' + LastName
FROM Person.Contact
GO
-- plusieurs plan_handle pour le même sql_handle
SELECT
cp.usecounts,
cp.size_in_bytes,
st.text
FROM sys.dm_exec_cached_plans cp
OUTER APPLY sys.dm_exec_sql_text (cp.plan_handle) st
JOIN sys.dm_exec_query_stats qs ON qs.plan_handle = cp.plan_handle
GO