298
Chapitre 9. Optimisation des procédures stockées
notamment invalidant automatiquement les procédures d’un objet qui vient d’être
modifié.
9.2.2 Recompilations automatiques
Les recompilations ne sont pas toutes déclenchées volontairement. Pour plusieurs
raisons, il ne serait pas raisonnable de conserver un plan d’exécution intact en cache
tout le long de la vie d’une instance (qui peut rester active des années sans être redémarrée). Le serveur SQL vit : non seulement les structures sont susceptibles de
changer, ce qui invalide le plan d’exécution, mais le contenu des tables évolue,
entraînant des recalculs de statistiques, le contexte d’exécution des utilisateurs peut
lui aussi changer, etc. Tous ces événements entraînent un changement potentiel de
plan d’exécution. SQL Server est attentif à ces modifications, et peut, lorsque le
besoin s’en fait sentir, déclencher des recompilations automatiques, lors de l’exécution du code. Depuis SQL Server 2005, ces recompilations s’effectuent par instruction (statement-level recompilation) et n’affectent donc pas la procédure ou le batch
tout entier.
Elles se produisent pour deux types de raisons :
• exactitude des données – la modification des structures sous-jacentes, comme
l’ajout ou la suppression de colonnes de table, de contraintes, la suppression
d’un index utilisé dans le plan, etc. ainsi que la modification d’options de
session (notamment à l’intérieur de la procédure stockée). les options qui
peuvent modifier le résultat des requêtes ou la valeur des constantes, comme
par exemple SET ANSI_NULLS, SET CONCAT_NULL_YIELDS_NULL, SET DATEFORMAT,
etc., peuvent provoquer des recompilations systématiques. Elles peuvent
changer le contexte d’exécution, donc forcer le plan d’exécution à s’adapter à
ce nouvel environnement ;
• optimisation du plan – principalement lors d’un recalcul de statistiques. Le
plan compilé comporte une valeur de seuil de recompilation, et teste à chaque
exécution si le nombre de modifications dans la table dépasse ce seuil. Si c’est
le cas, la requête est recompilée.
Les recompilations peuvent s’avérer coûteuses. Elles sont un mal (ou un bien)
nécessaire, mais ralentissent parfois inutilement l’exécution de procédures stockées.
Cela est généralement dû à de mauvaises pratiques de programmation, qui déclenchent des recompilations répétitives à chaque exécution de la procédure stockée,
voire plusieurs fois par exécution. Nous avons parlé des options de sessions. D’autres
éléments sont à considérer :
L’interpolation de code DML et de code DDL est susceptible de provoquer des
recompilations : la modification de structure d’objets doit être répercutée dans le
plan d’exécution des requêtes qui les référencent ;
La mauvaise utilisation de tables temporaires peut entraîner des recompilations.
La situation depuis SQL Server 2005 est meilleure qu’en SQL Server 2000 : comme
Chapitre 9. Optimisation des procédures stockées
notamment invalidant automatiquement les procédures d’un objet qui vient d’être
modifié.
9.2.2 Recompilations automatiques
Les recompilations ne sont pas toutes déclenchées volontairement. Pour plusieurs
raisons, il ne serait pas raisonnable de conserver un plan d’exécution intact en cache
tout le long de la vie d’une instance (qui peut rester active des années sans être redémarrée). Le serveur SQL vit : non seulement les structures sont susceptibles de
changer, ce qui invalide le plan d’exécution, mais le contenu des tables évolue,
entraînant des recalculs de statistiques, le contexte d’exécution des utilisateurs peut
lui aussi changer, etc. Tous ces événements entraînent un changement potentiel de
plan d’exécution. SQL Server est attentif à ces modifications, et peut, lorsque le
besoin s’en fait sentir, déclencher des recompilations automatiques, lors de l’exécution du code. Depuis SQL Server 2005, ces recompilations s’effectuent par instruction (statement-level recompilation) et n’affectent donc pas la procédure ou le batch
tout entier.
Elles se produisent pour deux types de raisons :
• exactitude des données – la modification des structures sous-jacentes, comme
l’ajout ou la suppression de colonnes de table, de contraintes, la suppression
d’un index utilisé dans le plan, etc. ainsi que la modification d’options de
session (notamment à l’intérieur de la procédure stockée). les options qui
peuvent modifier le résultat des requêtes ou la valeur des constantes, comme
par exemple SET ANSI_NULLS, SET CONCAT_NULL_YIELDS_NULL, SET DATEFORMAT,
etc., peuvent provoquer des recompilations systématiques. Elles peuvent
changer le contexte d’exécution, donc forcer le plan d’exécution à s’adapter à
ce nouvel environnement ;
• optimisation du plan – principalement lors d’un recalcul de statistiques. Le
plan compilé comporte une valeur de seuil de recompilation, et teste à chaque
exécution si le nombre de modifications dans la table dépasse ce seuil. Si c’est
le cas, la requête est recompilée.
Les recompilations peuvent s’avérer coûteuses. Elles sont un mal (ou un bien)
nécessaire, mais ralentissent parfois inutilement l’exécution de procédures stockées.
Cela est généralement dû à de mauvaises pratiques de programmation, qui déclenchent des recompilations répétitives à chaque exécution de la procédure stockée,
voire plusieurs fois par exécution. Nous avons parlé des options de sessions. D’autres
éléments sont à considérer :
L’interpolation de code DML et de code DDL est susceptible de provoquer des
recompilations : la modification de structure d’objets doit être répercutée dans le
plan d’exécution des requêtes qui les référencent ;
La mauvaise utilisation de tables temporaires peut entraîner des recompilations.
La situation depuis SQL Server 2005 est meilleure qu’en SQL Server 2000 : comme