292
Chapitre 9. Optimisation des procédures stockées
DBCC FLUSHPROCINDB (5)
Pour vider plus généralement les caches de SQL Server : DBCC FREESYSTEMCACHE('ALL').
Plus d'informations sur cette entrée de blog : http://sqlblog.com/blogs/
kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-fromcache.aspx
Il est également à noter que le cache se vide intégralement lors de quelques opérations, qu’il faut donc éviter d’exécuter inutilement sur un serveur de production :
• détachement d’une base de données (sp_detach_db) ;
• utilisation de la commande RECONFIGURE pour appliquer les changements
d’une option de serveur ;
• lorsqu’une vue est créée avec CHECK OPTION, toutes les entrées du cache qui
référencent la base de données dans laquelle se trouve la vue, sont vidées ;
• avant SQL Server 2005 Service Pack 2, DBCC CHECKDB vidait le cache. Ce n’est
plus le cas.
Si vous êtes confrontés à des nettoyages de cache intempestifs, consultez le journal d’erreur (ERRORLOG) de SQL Server. Depuis le Service Pack 2 de SQL Server 2005, un message d’information y est enregistré lorsque le cache se vide (aussi
à l’issue d’un DBCC FREEPROCACHE). Vous pouvez également tracer l’événement
SQL Trace Errors and Warnings : ErrorLog. L’événement Security Audit :
Audit DBCC Event se déclenche également à l’exécution de toute commande
DBCC.
9.2.1 Paramètres typiques
Sur quelle base le plan d’une procédure est-il calculé ? Lorsque nous passons des
paramètres, leurs valeurs peuvent différer et générer des plans d’exécution différents.
Qu’en est-il alors du comportement de la procédure stockée ? Malheureusement, il
n’y a en ce domaine pas de miracle : toutes les instructions de la procédure sont optimisées en tenant compte des valeurs de paramètres envoyées. Le premier appel
impose donc la qualité du plan d’exécution. Si la valeur des paramètres est
« typique », le plan sera de bonne qualité. Par contre, si des paramètres « extrêmes »
sont passés, cela peut produire de mauvaises performances lors des appels ultérieurs.
Prenons l’exemple de ces deux requêtes :
--créons un index pour aider la recherche
CREATE NONCLUSTERED INDEX [nix$Person_Contact$LastName]
ON [Person].[Contact] (LastName)
GO
-- 2 lignes à retourner
Chapitre 9. Optimisation des procédures stockées
DBCC FLUSHPROCINDB (5)
Pour vider plus généralement les caches de SQL Server : DBCC FREESYSTEMCACHE('ALL').
Plus d'informations sur cette entrée de blog : http://sqlblog.com/blogs/
kalen_delaney/archive/2007/09/29/geek-city-clearing-a-single-plan-fromcache.aspx
Il est également à noter que le cache se vide intégralement lors de quelques opérations, qu’il faut donc éviter d’exécuter inutilement sur un serveur de production :
• détachement d’une base de données (sp_detach_db) ;
• utilisation de la commande RECONFIGURE pour appliquer les changements
d’une option de serveur ;
• lorsqu’une vue est créée avec CHECK OPTION, toutes les entrées du cache qui
référencent la base de données dans laquelle se trouve la vue, sont vidées ;
• avant SQL Server 2005 Service Pack 2, DBCC CHECKDB vidait le cache. Ce n’est
plus le cas.
Si vous êtes confrontés à des nettoyages de cache intempestifs, consultez le journal d’erreur (ERRORLOG) de SQL Server. Depuis le Service Pack 2 de SQL Server 2005, un message d’information y est enregistré lorsque le cache se vide (aussi
à l’issue d’un DBCC FREEPROCACHE). Vous pouvez également tracer l’événement
SQL Trace Errors and Warnings : ErrorLog. L’événement Security Audit :
Audit DBCC Event se déclenche également à l’exécution de toute commande
DBCC.
9.2.1 Paramètres typiques
Sur quelle base le plan d’une procédure est-il calculé ? Lorsque nous passons des
paramètres, leurs valeurs peuvent différer et générer des plans d’exécution différents.
Qu’en est-il alors du comportement de la procédure stockée ? Malheureusement, il
n’y a en ce domaine pas de miracle : toutes les instructions de la procédure sont optimisées en tenant compte des valeurs de paramètres envoyées. Le premier appel
impose donc la qualité du plan d’exécution. Si la valeur des paramètres est
« typique », le plan sera de bonne qualité. Par contre, si des paramètres « extrêmes »
sont passés, cela peut produire de mauvaises performances lors des appels ultérieurs.
Prenons l’exemple de ces deux requêtes :
--créons un index pour aider la recherche
CREATE NONCLUSTERED INDEX [nix$Person_Contact$LastName]
ON [Person].[Contact] (LastName)
GO
-- 2 lignes à retourner