290
Chapitre 9. Optimisation des procédures stockées
sur la mémoire, le cache se nettoie. Il ne contient au maximum que deux versions
d’un plan pour une procédure : la version « sérielle » (monoprocesseur) et éventuellement la version parallélisée. De plus, ce plan est stocké sans information d’utilisateur, nous verrons dans la section 9.1.2, ce que cela implique.
Le cache de procédure peut être examiné à l’aide d’une vue de gestion
dynamique :
sys.dm_exec_cached_plans.
Alliée
aux
fonctions
sys.dm_exec_sql_text() et sys.dm_exec_query_plan(), elle vous permet d’observer
en détail ce qui réside dans le cache :
SELECT cp.usecounts, cp.size_in_bytes, st.text,
DB_NAME(st.dbid) as db,
OBJECT_SCHEMA_NAME(st.objectid, st.dbid) + '.'
+ OBJECT_NAME(st.objectid, st.dbid) as object,
qp.query_plan, cp.cacheobjtype, cp.objtype
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
Cette requête ne fonctionne qu’à partir du Service Pack 2 de SQL Server 2005,
qui introduit la fonction OBJECT_SCHEMA_NAME(), et améliore la fonction
OBJECT_NAME(), lui permettant de passer l’identifiant de base de données.
En exécutant cette requête, vous constaterez que la colonne cp.objtype contient
différents types de requêtes, et pas seulement des procédures stockées. Nous reviendrons sur la capacité de SQL Server à cacher d’autres plans d’exécution plus loin.
Vous pouvez constater en observant cette requête que la vue
sys.dm_exec_cached_plans retourne les colonnes usecounts et size_in_bytes, fort
utiles pour juger de la taille et de l’utilité du cache. Bien entendu usercounts indique
le nombre de réutilisation du plan d’exécution, et size_in_bytes sa taille en
mémoire. Le cache d’une procédure complexe peut prendre plusieurs mégaoctets. La
taille indiquée ne dépend d’ailleurs pas seulement de la complexité du plan
d’exécution : elle varie aussi selon le nombre d’exécutions simultanées de la procédure ou du batch, car, comme nous le verrons, des plans en rapport avec le contexte
d’exécution sont générés à l’exécution à partir de ce plan compilé, et eux-mêmes
cachés. On aura compris une chose : un serveur SQL fortement sollicité, et qui exécute des requêtes complexes, gagnera fortement à avoir le plus de mémoire de travail
possible. L’architecture 64 bits apporte un réel plus dans ce cas de figure.
Dès que le plan d’exécution de la procédure est en cache, il sera réutilisé à chaque
appel ultérieur, économisant ainsi le calcul coûteux du plan d’exécution. Vous pouvez facilement observer les différences de temps d’exécution entre le premier appel
d’une procédure et les suivantes avec le profiler, et notamment sur la valeur en temps
CPU, qui inclut le temps de compilation.
Normalement, la procédure va rester dans le cache. Par contre, comme sur les
systèmes 32 bits, la mémoire virtuelle est limitée, et comme le cache de procédure
Chapitre 9. Optimisation des procédures stockées
sur la mémoire, le cache se nettoie. Il ne contient au maximum que deux versions
d’un plan pour une procédure : la version « sérielle » (monoprocesseur) et éventuellement la version parallélisée. De plus, ce plan est stocké sans information d’utilisateur, nous verrons dans la section 9.1.2, ce que cela implique.
Le cache de procédure peut être examiné à l’aide d’une vue de gestion
dynamique :
sys.dm_exec_cached_plans.
Alliée
aux
fonctions
sys.dm_exec_sql_text() et sys.dm_exec_query_plan(), elle vous permet d’observer
en détail ce qui réside dans le cache :
SELECT cp.usecounts, cp.size_in_bytes, st.text,
DB_NAME(st.dbid) as db,
OBJECT_SCHEMA_NAME(st.objectid, st.dbid) + '.'
+ OBJECT_NAME(st.objectid, st.dbid) as object,
qp.query_plan, cp.cacheobjtype, cp.objtype
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
Cette requête ne fonctionne qu’à partir du Service Pack 2 de SQL Server 2005,
qui introduit la fonction OBJECT_SCHEMA_NAME(), et améliore la fonction
OBJECT_NAME(), lui permettant de passer l’identifiant de base de données.
En exécutant cette requête, vous constaterez que la colonne cp.objtype contient
différents types de requêtes, et pas seulement des procédures stockées. Nous reviendrons sur la capacité de SQL Server à cacher d’autres plans d’exécution plus loin.
Vous pouvez constater en observant cette requête que la vue
sys.dm_exec_cached_plans retourne les colonnes usecounts et size_in_bytes, fort
utiles pour juger de la taille et de l’utilité du cache. Bien entendu usercounts indique
le nombre de réutilisation du plan d’exécution, et size_in_bytes sa taille en
mémoire. Le cache d’une procédure complexe peut prendre plusieurs mégaoctets. La
taille indiquée ne dépend d’ailleurs pas seulement de la complexité du plan
d’exécution : elle varie aussi selon le nombre d’exécutions simultanées de la procédure ou du batch, car, comme nous le verrons, des plans en rapport avec le contexte
d’exécution sont générés à l’exécution à partir de ce plan compilé, et eux-mêmes
cachés. On aura compris une chose : un serveur SQL fortement sollicité, et qui exécute des requêtes complexes, gagnera fortement à avoir le plus de mémoire de travail
possible. L’architecture 64 bits apporte un réel plus dans ce cas de figure.
Dès que le plan d’exécution de la procédure est en cache, il sera réutilisé à chaque
appel ultérieur, économisant ainsi le calcul coûteux du plan d’exécution. Vous pouvez facilement observer les différences de temps d’exécution entre le premier appel
d’une procédure et les suivantes avec le profiler, et notamment sur la valeur en temps
CPU, qui inclut le temps de compilation.
Normalement, la procédure va rester dans le cache. Par contre, comme sur les
systèmes 32 bits, la mémoire virtuelle est limitée, et comme le cache de procédure