289
9.2 Maîtrise de la compilation
9.2 MAÎTRISE DE LA COMPILATION
Un avantage important de la procédure stockée, est la réutilisation de son plan
d’exécution. Nous allons détailler ce mécanisme. Ci-après, nous ne parlerons que de
procédures, mais gardez à l’esprit que le mécanisme est le même pour les fonctions
utilisateur (UDF) et pour les déclencheurs, qui sont précompilés de la même
manière.
Lorsque la procédure est créée via une instruction CREATE PROCEDURE, son code
source est stocké dans une table système de métadonnées de la base courante. Vous
pouvez retrouver ce code grâce à la vue système sys.sql_modules :
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspGetBillOfMaterials')
Cette requête retourne la définition de la procédure nommée dbo.uspGetBillOfMaterials. La vue sys.sql_modules interroge des tables systèmes qui ne peuvent plus
être requêtées directement depuis SQL Server 2005. Si vous êtes curieux de savoir
comment ces tables systèmes s’appellent, vous pouvez retrouver la définition de cette
vue (comme de tous les objets système) à l’aide d’un de ces commandes :
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.sql_modules'))
ou
SELECT definition FROM sys.system_sql_modules
WHERE Object_Id = OBJECT_ID('sys.sql_modules')
Ce stockage n’implique en rien une compilation, ou une optimisation. Il faut distinguer en SQL Server la phase de compilation, à proprement parler la vérification
des privilèges de l’utilisateur et de l’existence des objets, de l’optimisation. L’optimisation est la génération d’un plan d’exécution pour le code SQL. Lorsque la procédure est créée, rien de tout cela n’est fait, même pas la vérification de l’existence des
objets : on peut créer une procédure qui référence des tables qui n’existent pas, par
les vertus de la fonctionnalité de « résolution de nom différée » (Deferred Name
Resolution). Cette résolution différé ne s’applique qu’aux tables (et aux vues, qui sont
syntaxiquement et conceptuellement identiques aux tables), tout autre objet référencé, comme une procédure stockée appelée par un EXECUTE, ou une colonne d’une
table, doivent exister.
Ainsi, à la création de la procédure, on peut dire que seule une vérification syntaxique du code SQL est effectuée. La vérification de l’existence des tables, comme
l’optimisation, ne seront réalisées que lors de la première exécution de la procédure
stockée. Par « première exécution », nous entendons première exécution depuis le
démarrage de l’instance SQL. Le plan d’exécution généré pour la procédure lors de
sa première exécution sera stocké en mémoire vive, dans ce qu’on appelle le cache
de plans, ou cache de procédures. Lors des exécutions ultérieures de la procédure, ce
plan sera utilisé, ce qui économise l’étape d’optimisation. Le plan reste dans le cache
jusqu’au redémarrage du service. Il est également possible que, à cause d’une pression
9.2 Maîtrise de la compilation
9.2 MAÎTRISE DE LA COMPILATION
Un avantage important de la procédure stockée, est la réutilisation de son plan
d’exécution. Nous allons détailler ce mécanisme. Ci-après, nous ne parlerons que de
procédures, mais gardez à l’esprit que le mécanisme est le même pour les fonctions
utilisateur (UDF) et pour les déclencheurs, qui sont précompilés de la même
manière.
Lorsque la procédure est créée via une instruction CREATE PROCEDURE, son code
source est stocké dans une table système de métadonnées de la base courante. Vous
pouvez retrouver ce code grâce à la vue système sys.sql_modules :
SELECT definition
FROM sys.sql_modules
WHERE object_id = OBJECT_ID('dbo.uspGetBillOfMaterials')
Cette requête retourne la définition de la procédure nommée dbo.uspGetBillOfMaterials. La vue sys.sql_modules interroge des tables systèmes qui ne peuvent plus
être requêtées directement depuis SQL Server 2005. Si vous êtes curieux de savoir
comment ces tables systèmes s’appellent, vous pouvez retrouver la définition de cette
vue (comme de tous les objets système) à l’aide d’un de ces commandes :
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.sql_modules'))
ou
SELECT definition FROM sys.system_sql_modules
WHERE Object_Id = OBJECT_ID('sys.sql_modules')
Ce stockage n’implique en rien une compilation, ou une optimisation. Il faut distinguer en SQL Server la phase de compilation, à proprement parler la vérification
des privilèges de l’utilisateur et de l’existence des objets, de l’optimisation. L’optimisation est la génération d’un plan d’exécution pour le code SQL. Lorsque la procédure est créée, rien de tout cela n’est fait, même pas la vérification de l’existence des
objets : on peut créer une procédure qui référence des tables qui n’existent pas, par
les vertus de la fonctionnalité de « résolution de nom différée » (Deferred Name
Resolution). Cette résolution différé ne s’applique qu’aux tables (et aux vues, qui sont
syntaxiquement et conceptuellement identiques aux tables), tout autre objet référencé, comme une procédure stockée appelée par un EXECUTE, ou une colonne d’une
table, doivent exister.
Ainsi, à la création de la procédure, on peut dire que seule une vérification syntaxique du code SQL est effectuée. La vérification de l’existence des tables, comme
l’optimisation, ne seront réalisées que lors de la première exécution de la procédure
stockée. Par « première exécution », nous entendons première exécution depuis le
démarrage de l’instance SQL. Le plan d’exécution généré pour la procédure lors de
sa première exécution sera stocké en mémoire vive, dans ce qu’on appelle le cache
de plans, ou cache de procédures. Lors des exécutions ultérieures de la procédure, ce
plan sera utilisé, ce qui économise l’étape d’optimisation. Le plan reste dans le cache
jusqu’au redémarrage du service. Il est également possible que, à cause d’une pression