Avez-vous déjà rencontré un message d’information dans le journal des erreurs de votre serveur SQL Server indiquant un changement dans les paramètres de configuration ? Si c’est le cas, il est important de prendre conscience de l’impact potentiel de ces changements sur les performances, notamment pendant les périodes de traitement intensif.
SQL Server offre une large gamme d’options de configuration qui peuvent être modifiées dynamiquement à l’aide de la commande sp_configure, sans nécessiter de redémarrage du service SQL Server. Bien que cette flexibilité soit pratique, il est crucial de comprendre que certains changements dynamiques peuvent avoir un impact négatif sur les performances, comme l’indique le message “d’information” dans le journal des erreurs.
Jetons un coup d’œil à un exemple pour mieux comprendre ce concept. Tout d’abord, examinons l’état actuel des cachestores :
SELECT COUNT(*) AS 'Total des plans mis en cache'
FROM sys.dm_exec_cached_plans;
SELECT name AS 'Nom du cache', single_pages_kb, multi_pages_kb, entries_count
FROM sys.dm_os_memory_cache_counters
WHERE name IN ('Object Plans', 'SQL Plans', 'Bound Trees');
Dans cet exemple, nous pouvons voir qu’il y a 160 plans en cache, occupant une certaine quantité d’allocations de pages uniques et multiples pour chaque entrée.
Maintenant, imaginez que votre administrateur système a récemment ajouté de la mémoire RAM supplémentaire à la machine SQL Server en réponse à vos plaintes concernant les pénuries de mémoire. Enthousiasmé par les ressources supplémentaires, vous décidez d’ajuster l’allocation maximale de mémoire de SQL Server à 4 Go en utilisant la requête suivante :
EXEC sys.sp_configure N'max server memory (MB)', N'4000';
GO
RECONFIGURE;
GO
Après avoir effectué cette modification, vous vérifiez avec impatience le journal des erreurs de SQL Server pour valider la mise à jour de la configuration. Vous êtes ravi de voir le message suivant :
L'option de configuration 'max server memory (MB)' a été modifiée de 2000 à 4000. Exécutez l'instruction RECONFIGURE pour l'installer.
Cependant, vous remarquez également les messages moins excitants suivants :
SQL Server a rencontré 1 occurrence(s) de vidage du cachestore pour le cachestore 'Object Plans' (partie du cache des plans) en raison de certaines opérations de maintenance de la base de données ou de reconfiguration.
SQL Server a rencontré 1 occurrence(s) de vidage du cachestore pour le cachestore 'SQL Plans' (partie du cache des plans) en raison de certaines opérations de maintenance de la base de données ou de reconfiguration.
SQL Server a rencontré 1 occurrence(s) de vidage du cachestore pour le cachestore 'Bound Trees' (partie du cache des plans) en raison de certaines opérations de maintenance de la base de données ou de reconfiguration.
Ces messages indiquent que les cachestores ont été vidés, entraînant la perte de tous les plans compilés pour les procédures stockées, les fonctions, les déclencheurs, les vues, les plans ad hoc et les plans préparés. Par conséquent, toute exécution de requête ultérieure nécessitera une compilation du plan, ce qui entraînera une utilisation accrue du processeur et potentiellement une dégradation des performances.
Il est important de noter que certains paramètres de configuration, lorsqu’ils sont modifiés à l’aide de l’instruction RECONFIGURE, peuvent déclencher une vidange du cache de procédures similaire à la commande DBCC FREEPROCCACHE. L’ensemble du cache de procédures est effacé si l’une des options serveur suivantes est modifiée :
- cross db ownership chaining
- index create memory (KB)
- remote query timeout (s)
- user options
- max text repl size (B)
- cost threshold for parallelism
- max degree of parallelism
- min memory per query (KB)
- query wait (s)
- min server memory (MB)
- max server memory (MB)
- query governor cost limit
Cet exemple rappelle pourquoi il est crucial d’éviter de modifier les paramètres de configuration pendant les périodes de traitement intensif. Tout comme vous ne lanceriez pas la commande DBCC FREEPROCCACHE en plein traitement intensif, il est préférable de s’abstenir de modifier les paramètres de configuration pendant ces périodes critiques.
Pour plus de lecture et d’informations détaillées, vous pouvez consulter l’article de la base de connaissances Microsoft suivant : http://support.microsoft.com/kb/917828