février
2011
Un article de zinzineti
Après la mise en place d’une meilleure configuration de la mémoire du serveur de base de données, à l’aide des commutateurs /3GB et /PAE il est important de suivre la bonne utilisation de la mémoire du serveur. On peut donc pour ce faire mettre en place des compteurs de performances orientés mémoire puis les analysés régulièrement. Il est également possible à partir de SQL Server 2005 d’utiliser des DMVs et DMFs pour obtenir de façon instantanée des informations sur l’utilisation de la mémoire du serveur.
Les compteurs de performances à surveiller pour qualifier le bon fonctionnement de la machine côté mémoire
|
Compteur de performance |
la Valeur doit être |
Commentaires |
|
Memory Available Mbytes |
> 100MB |
C’est la quantité de mémoire disponible pour un processus. Les performances sont mauvaises pour une valeur < 100MB |
|
Paging File %Usage |
< 70 |
C’est le pourcentage de mémoire paginée. La valeur doit être < 70 % pour un fonctionnement normal |
|
SQL Server\Buffer Manager\Buffer Cache hit ratio |
> 95% |
C’est le pourcentage des pages de procédures dans le cache. Pour un fonctionnement normal cette valeur doit être proche de 99 % |
|
SQL Server\Buffer Manager\Page Life Expectancy |
> 300 |
C’est la durée moyenne de présence dans le cache d’une page de données. |
|
SQL Server\Memory Manager\Memory Grants Pending
|
< 1 |
C’est le nombre de processus SQL en attente de mémoire. |
La liste des compteurs de performances pour surveiller la mémoire n’est pas exhaustive, mais ces points de repères ont peut déjà avoir le coeur net sur l’utilisation de la mémoire.
Les DMVs et DMFs peuvent fournir également beaucoup d’informations sur tout ce qui se passent à propos de la mémoire.
Passons en revue quelques scénarios
–> Quel est le nombre de pages mises en cache pour chaque base de données ?
SELECT COUNT(*)AS cached_pages_count
, COUNT(*) * 8 AS [Taille_Ko]
,CASE database_id
WHEN 32767 THEN 'ResourceDb'
ELSE db_name(database_id)
END AS Database_name
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name(database_id) ,database_id
ORDER BY cached_pages_count DESC;
–> Quel est le nombre de pages mises en cache pour chaque objet de la base de données active ?
SELECT COUNT(*)AS cached_pages_count
,COUNT(*) * 8 AS [Taille_Ko]
,name ,index_id
FROM sys.dm_os_buffer_descriptors AS bd
INNER JOIN
(
SELECT object_name(object_id) AS name
,index_id ,allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.hobt_id
AND (au.type = 1 OR au.type = 3)
UNION ALL
SELECT object_name(object_id) AS name
,index_id, allocation_unit_id
FROM sys.allocation_units AS au
INNER JOIN sys.partitions AS p
ON au.container_id = p.partition_id
AND au.type = 2
) AS obj
ON bd.allocation_unit_id = obj.allocation_unit_id
WHERE database_id = db_id()
GROUP BY name, index_id
ORDER BY cached_pages_count DESC;
–> Quelles sont les 50 requêtes les plus consommatrices de mémoire (logical_reads et logical_writes)?
SELECT TOP 50 creation_time,
(total_logical_reads + total_logical_writes)AS [Total IO Logique] ,
(total_logical_reads + total_logical_writes) / qs.execution_count AS [Avg IO Logique],
total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY [Avg IO Logique] DESC;
–> Quelles sont les 50 requêtes SQL dont les plans d’exécutions ne sont pas réutilisés ?
La réécriture de ces requêtes pour une réutilisation du plan d’exécution va libérer de l’espace mémoire et les performances peuvent s’améliorer.
SELECT TOP(50) [text]
, size_in_bytes/1024 AS [Taille_Ko]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype = 'Compiled Plan'
AND objtype = 'Adhoc' AND usecounts = 1
ORDER BY [Taille_Ko] DESC
–> Quel est le nombre de plans d’exécution non réutilisés dans le cache et la taille de mémoire mobilisée ?
SELECT COUNT(*) AS [Nb_Total_Plans_Non_Rutiliss]
,SUM(c.size_in_bytes)/(1024*1024) AS [Plans_Non_Rutiliss_Size_Mo]
FROM sys.dm_exec_cached_plans c
WHERE c.usecounts = 1
–> Quel est le nombre de plans dans le cache et la taille de mémoire mobilisée
SELECT COUNT(*) AS [Nb_Total_Plans]
,SUM(c.size_in_bytes)/(1024*1024) AS [Total_Cache_Size_Mo]
FROM sys.dm_exec_cached_plans c
——————————————————————-
Etienne ZINZINDOHOUE
——————————————————————-
Bonjour
je pointe sur la valeur utilisé Pour le compteur SQL Server\Buffer Manager\Page Life Expectancy
on doit étre trés précis dans la précision du valeur 300 signifie qu’en 5 minutes
il faut préciser que le 300 c’est pour une architectures CPU 32 bits
Par exemple pour un serveur avec XGo on aurait une valeur de PLE :
x * 300 / 4 = 1125