288
Chapitre 9. Optimisation des procédures stockées
Enfin, la procédure stockée est précompilée et conservée dans un cache de procédures, en mémoire, ce qui évite des recalculs de plans d’exécution. Nous détaillerons
cet élément.
9.1 DONE_IN_PROC
Par défaut, SQL Server retourne un message au client après chaque exécution
d’instruction SQL, indiquant le nombre de lignes affectées. Vous le voyez dans
l’onglet « messages » de SSMS, lorsque vous exécutez la moindre commande. Ce
message prend la forme : « (14 row(s) affected) » (pour une langue de session
us_english). Ce message est indépendant des jeux de résultats eux-mêmes, il est
envoyé dans un paquet TDS à travers le réseau à chaque exécution d’une instruction, qu’elle soit dans un batch ou qu’elle fasse partie d’une procédure stockée. Cela
provoque des allers-retours réseau inutiles. Dans la plupart des cas ce message
done_in_proc n’est pas utilisé par le client (la fonction ODBC SQLRowCount()
l’utilise, mais pas la propriété RecordCount de l’objet ADODB.Recordset). Vous pouvez
donc obtenir un gain de performance réel en désactivant l’envoi des tokens
done_in_proc, surtout pendant l’exécution de procédures stockées complexes.
Pour cela, vous avez plusieurs solutions. La plus simple est de placer, en début de
vos procédures stockées, l’instruction SET NOCOUNT ON, comme ceci :
CREATE PROCEDURE dbo.DoSomethingUseful
AS BEGIN
SET NOCOUNT ON
...
Ce qui désactive le renvoi des messages done_in_proc pour le reste de la session.
Vous pouvez également désactiver globalement ces messages pour toutes les sessions,
en modifiant le paramètre de serveur 'user options' :
EXEC sp_configure 'user options', 512
RECONFIGURE
ou à l’aide du drapeau de trace 3640 à ajouter à la ligne de commande de démarrage du serveur SQL, dans les paramètres du service (propriété « Startup Parameters »
du service Dans SQL Server Configuration Manager (voir section 7.5.2).
Malgré cela, c’est une bonne idée d’écrire systématiquement la commande SET
NOCOUNT ON au début de toutes vos procédures stockées, car l’option a pu être remise
à OFF dans la session (vous pouvez notamment configurer SSMS pour initialiser cette
option à l’ouverture de session, voyez la fenêtre de propriétés de la requête, dans la
page « advanced »).
Enfin, vous pouvez tester quel est l’état de cette option de la façon suivante
(exemple d’utilisation) :
IF @@OPTIONS & 512 = 512
PRINT 'SET NOCOUNT est à ON';
Chapitre 9. Optimisation des procédures stockées
Enfin, la procédure stockée est précompilée et conservée dans un cache de procédures, en mémoire, ce qui évite des recalculs de plans d’exécution. Nous détaillerons
cet élément.
9.1 DONE_IN_PROC
Par défaut, SQL Server retourne un message au client après chaque exécution
d’instruction SQL, indiquant le nombre de lignes affectées. Vous le voyez dans
l’onglet « messages » de SSMS, lorsque vous exécutez la moindre commande. Ce
message prend la forme : « (14 row(s) affected) » (pour une langue de session
us_english). Ce message est indépendant des jeux de résultats eux-mêmes, il est
envoyé dans un paquet TDS à travers le réseau à chaque exécution d’une instruction, qu’elle soit dans un batch ou qu’elle fasse partie d’une procédure stockée. Cela
provoque des allers-retours réseau inutiles. Dans la plupart des cas ce message
done_in_proc n’est pas utilisé par le client (la fonction ODBC SQLRowCount()
l’utilise, mais pas la propriété RecordCount de l’objet ADODB.Recordset). Vous pouvez
donc obtenir un gain de performance réel en désactivant l’envoi des tokens
done_in_proc, surtout pendant l’exécution de procédures stockées complexes.
Pour cela, vous avez plusieurs solutions. La plus simple est de placer, en début de
vos procédures stockées, l’instruction SET NOCOUNT ON, comme ceci :
CREATE PROCEDURE dbo.DoSomethingUseful
AS BEGIN
SET NOCOUNT ON
...
Ce qui désactive le renvoi des messages done_in_proc pour le reste de la session.
Vous pouvez également désactiver globalement ces messages pour toutes les sessions,
en modifiant le paramètre de serveur 'user options' :
EXEC sp_configure 'user options', 512
RECONFIGURE
ou à l’aide du drapeau de trace 3640 à ajouter à la ligne de commande de démarrage du serveur SQL, dans les paramètres du service (propriété « Startup Parameters »
du service Dans SQL Server Configuration Manager (voir section 7.5.2).
Malgré cela, c’est une bonne idée d’écrire systématiquement la commande SET
NOCOUNT ON au début de toutes vos procédures stockées, car l’option a pu être remise
à OFF dans la session (vous pouvez notamment configurer SSMS pour initialiser cette
option à l’ouverture de session, voyez la fenêtre de propriétés de la requête, dans la
page « advanced »).
Enfin, vous pouvez tester quel est l’état de cette option de la façon suivante
(exemple d’utilisation) :
IF @@OPTIONS & 512 = 512
PRINT 'SET NOCOUNT est à ON';