305
9.3 Cache des requêtes ad hoc
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.sql_handle
exécutée après les deux exemples précédents.
Figure 9.4 — Plusieurs plan_handle pour le même sql_handle
Vous voyez que pour le même sql_handle, vous avez chaque fois deux
plan_handle. Vous pouvez aussi le constater en traçant avec le profiler l’événement
sp:CacheInsert. Afin de vérifier pour quelle raison (quel attribut du plan) des plans
différents ont été créés, vous pouvez vous baser sur la vue
sys.dm_exec_plan_attributes, comme ceci par exemple (en passant un sql_hande
trouvé) :
SELECT st.text, qs.sql_handle, qs.plan_handle, pa.attribute, pa.value
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE qs.sql_handle = 0x020000002F5CC820E0CC946DD76094543CF7AA299904C81A
and pa.is_cache_key = 1
ORDER BY pa.attribute
Ce qu’il faut comprendre ici, c’est que SQL Server cherche à réutiliser autant que
possible les plans d’exécution en cache, car le recalcul de plans peut être très pénalisant. Nous venons de voir que certaines conditions invalident pourtant le plan en
cache et obligent SQL Server à compiler à nouveau le batch ou la procédure. Il faut
éviter autant que possible de provoquer de telles situations. Les deux cas que nous
avons testés précédemment sont les plus courants :
• un changement d’options de session en cours de travail modifie le contexte
d’exécution.
Des
options
comme
ANSI_NULLS,
ANSI_DEFAULTS,
CONCAT_NULL_YIELDS_NULL, ARITHABORT, DATEFIRST, DATEFORMAT, LANGUAGE,
QUOTED_IDENTIFIER, etc. peuvent empêcher la réutilisation d’un plan, parce
qu’elles influent sur les comparaisons, et qu’elles modifient potentiellement la
valeur des littéraux exprimés dans la requête. SQL Server évalue très tôt dans
la phase de compilation la valeur de ces littéraux (une fonctionnalité nommée
constant folding). Si une option est modifiée, qui peut changer cette valeur déjà
évaluée, le code doit être compilé à nouveau ;
• lorsqu’un objet n’est pas complètement identifié par son schéma, SQL Server
ne peut pas garantir que l’appel par des utilisateurs dont le schéma par défaut
est différent, va référencer le même objet, il doit donc recompiler.
9.3 Cache des requêtes ad hoc
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.sql_handle
exécutée après les deux exemples précédents.
Figure 9.4 — Plusieurs plan_handle pour le même sql_handle
Vous voyez que pour le même sql_handle, vous avez chaque fois deux
plan_handle. Vous pouvez aussi le constater en traçant avec le profiler l’événement
sp:CacheInsert. Afin de vérifier pour quelle raison (quel attribut du plan) des plans
différents ont été créés, vous pouvez vous baser sur la vue
sys.dm_exec_plan_attributes, comme ceci par exemple (en passant un sql_hande
trouvé) :
SELECT st.text, qs.sql_handle, qs.plan_handle, pa.attribute, pa.value
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
OUTER APPLY sys.dm_exec_plan_attributes(qs.plan_handle) pa
WHERE qs.sql_handle = 0x020000002F5CC820E0CC946DD76094543CF7AA299904C81A
and pa.is_cache_key = 1
ORDER BY pa.attribute
Ce qu’il faut comprendre ici, c’est que SQL Server cherche à réutiliser autant que
possible les plans d’exécution en cache, car le recalcul de plans peut être très pénalisant. Nous venons de voir que certaines conditions invalident pourtant le plan en
cache et obligent SQL Server à compiler à nouveau le batch ou la procédure. Il faut
éviter autant que possible de provoquer de telles situations. Les deux cas que nous
avons testés précédemment sont les plus courants :
• un changement d’options de session en cours de travail modifie le contexte
d’exécution.
Des
options
comme
ANSI_NULLS,
ANSI_DEFAULTS,
CONCAT_NULL_YIELDS_NULL, ARITHABORT, DATEFIRST, DATEFORMAT, LANGUAGE,
QUOTED_IDENTIFIER, etc. peuvent empêcher la réutilisation d’un plan, parce
qu’elles influent sur les comparaisons, et qu’elles modifient potentiellement la
valeur des littéraux exprimés dans la requête. SQL Server évalue très tôt dans
la phase de compilation la valeur de ces littéraux (une fonctionnalité nommée
constant folding). Si une option est modifiée, qui peut changer cette valeur déjà
évaluée, le code doit être compilé à nouveau ;
• lorsqu’un objet n’est pas complètement identifié par son schéma, SQL Server
ne peut pas garantir que l’appel par des utilisateurs dont le schéma par défaut
est différent, va référencer le même objet, il doit donc recompiler.