291
9.2 Maîtrise de la compilation
réside dans cette portion de la mémoire (rappelons que la mémoire au-delà des
4 Go ne peut être attribuée qu’au cache de données), il peut arriver qu’une pression
sur cette mémoire oblige SQL Server à nettoyer le cache 1 , pour faire de la place
pour la mémoire de travail. Dans ce cas, SQL Server va tout de même s’efforcer de
conserver les plans les plus coûteux à recréer. Vous pouvez vous faire une idée du
coût d’un plan à l’aide des colonnes original_cost et current_cost de
sys.dm_os_memory_cache_entries. Voici un exemple de requête :
SELECT
DB_NAME(st.dbid) as db,
OBJECT_SCHEMA_NAME(st.objectid, st.dbid) + '.'
+ OBJECT_NAME(st.objectid, st.dbid) as object,
cp.objtype, cp.usecounts, cp.size_in_bytes,
ce.disk_ios_count, ce.context_switches_count,
ce.pages_allocated_count, ce.original_cost, ce.current_cost
FROM sys.dm_exec_cached_plans cp
JOIN sys.dm_os_memory_cache_entries ce
on cp.memory_object_address = ce.memory_object_address
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
Pression sur le cache de procédures
N’oubliez pas qu’un verrou de compilation est posé sur une procédure lors de sa compilation.
Il ne peut y avoir qu’une seule compilation de la même procédure à la fois, ce qui signifie que
si la compilation est longue, tous les utilisateurs devront attendre la fin de la compilation. Pour
éviter ce type de problèmes, n’écrivez pas de procédures trop longues ou complexes, modularisez au besoin, et assurez-vous de disposer de suffisamment de mémoire de travail (en
dessous de la limite des 4 Go en 32 bits).
Si vous souhaitez vider le cache, par exemple pour réaliser des tests consistants de
performances, vous disposez de la commande DBCC FREEPROCCACHE, qui vide entièrement le cache de procédures. Elle est en général utilisée conjointement avec DBCC
DROPCLEANBUFFERS, pour obtenir un état de la mémoire proche d’un démarrage de
l’instance SQL.
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Il vaut mieux bien entendu éviter de lancer ces commandes sur un serveur de
production, qui verrait immédiatement ses performances se dégrader jusqu’à ce que
le cache soit de nouveau rempli.
Deux autres commandes DBCC, moins connues permettent de vider le cache :
pour les plans qui s’appliquent à une base de données spécifique : DBCC FLUSHPROCINDB (DBID) :
SELECT DB_ID('AdventureWorks')
1. À travers un memory sweep, lancé par un algorithme de planification, dont on peut voir l’état
dans la vue sys.dm_os_memory_cache_clock_hands.
9.2 Maîtrise de la compilation
réside dans cette portion de la mémoire (rappelons que la mémoire au-delà des
4 Go ne peut être attribuée qu’au cache de données), il peut arriver qu’une pression
sur cette mémoire oblige SQL Server à nettoyer le cache 1 , pour faire de la place
pour la mémoire de travail. Dans ce cas, SQL Server va tout de même s’efforcer de
conserver les plans les plus coûteux à recréer. Vous pouvez vous faire une idée du
coût d’un plan à l’aide des colonnes original_cost et current_cost de
sys.dm_os_memory_cache_entries. Voici un exemple de requête :
SELECT
DB_NAME(st.dbid) as db,
OBJECT_SCHEMA_NAME(st.objectid, st.dbid) + '.'
+ OBJECT_NAME(st.objectid, st.dbid) as object,
cp.objtype, cp.usecounts, cp.size_in_bytes,
ce.disk_ios_count, ce.context_switches_count,
ce.pages_allocated_count, ce.original_cost, ce.current_cost
FROM sys.dm_exec_cached_plans cp
JOIN sys.dm_os_memory_cache_entries ce
on cp.memory_object_address = ce.memory_object_address
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
Pression sur le cache de procédures
N’oubliez pas qu’un verrou de compilation est posé sur une procédure lors de sa compilation.
Il ne peut y avoir qu’une seule compilation de la même procédure à la fois, ce qui signifie que
si la compilation est longue, tous les utilisateurs devront attendre la fin de la compilation. Pour
éviter ce type de problèmes, n’écrivez pas de procédures trop longues ou complexes, modularisez au besoin, et assurez-vous de disposer de suffisamment de mémoire de travail (en
dessous de la limite des 4 Go en 32 bits).
Si vous souhaitez vider le cache, par exemple pour réaliser des tests consistants de
performances, vous disposez de la commande DBCC FREEPROCCACHE, qui vide entièrement le cache de procédures. Elle est en général utilisée conjointement avec DBCC
DROPCLEANBUFFERS, pour obtenir un état de la mémoire proche d’un démarrage de
l’instance SQL.
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Il vaut mieux bien entendu éviter de lancer ces commandes sur un serveur de
production, qui verrait immédiatement ses performances se dégrader jusqu’à ce que
le cache soit de nouveau rempli.
Deux autres commandes DBCC, moins connues permettent de vider le cache :
pour les plans qui s’appliquent à une base de données spécifique : DBCC FLUSHPROCINDB (DBID) :
SELECT DB_ID('AdventureWorks')
1. À travers un memory sweep, lancé par un algorithme de planification, dont on peut voir l’état
dans la vue sys.dm_os_memory_cache_clock_hands.