Annexe A : Surveillance de l'état de santé de SQL Server

Vous pouvez exécuter la requête suivante pour obtenir tous les noms DMV et DMF :

SELECT * FROM sys.system_objects
WHERE name LIKE 'dm_%'
ORDER BY name

L'engorgement du processeur est souvent dû à un plan de requête non optimisé, à une mauvaise configuration, à de mauvais facteurs de conception ou à une insuffisance des ressources matérielles. Voici des requêtes couramment utilisées pour identifier la cause d'un engorgement du processeur.

La requête suivante offre une vue générale des lots ou procédures actuellement en mémoire cache qui utilisent le plus de ressources processeur.

SELECT TOP 50 
      SUM(qs.total_worker_time) AS total_cpu_time, 
      SUM(qs.execution_count) AS total_execution_count,
      COUNT(*) AS  number_of_statements, 
      qs.sql_handle 
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.sql_handle
ORDER BY SUM(qs.total_worker_time) DESC

La requête suivante affiche l'utilisation totale de l'UC par des plans mis en cache avec du texte SQL.

SELECT 
      total_cpu_time, 
      total_execution_count,
      number_of_statements,
      s2.text
      --(SELECT SUBSTRING(s2.text, statement_start_offset / 2, ((CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(NVARCHAR(MAX), s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2) ) AS query_text
FROM 
      (SELECT TOP 50 
            SUM(qs.total_worker_time) AS total_cpu_time, 
            SUM(qs.execution_count) AS total_execution_count,
            COUNT(*) AS  number_of_statements, 
            qs.sql_handle --,
            --MIN(statement_start_offset) AS statement_start_offset, 
            --MAX(statement_end_offset) AS statement_end_offset
      FROM 
            sys.dm_exec_query_stats AS qs
      GROUP BY qs.sql_handle
      ORDER BY SUM(qs.total_worker_time) DESC) AS stats
      CROSS APPLY sys.dm_exec_sql_text(stats.sql_handle) AS s2 

La requête suivante affiche les 50 instructions SQL qui utilisent le plus de ressources processeur.

SELECT TOP 50
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text, *
FROM sys.dm_exec_query_stats 
ORDER BY [Avg CPU Time] DESC

Voici des requêtes DMV détectant les compilations/recompilations excessives.

select * from sys.dm_exec_query_optimizer_info
where 
      counter = 'optimizations'
      or counter = 'elapsed time'

L'exemple de requête suivant vous donne les 25 procédures stockées qui ont été le plus recompilées. La valeur plan_generation_num indique le nombre recompilations de cette requête.

select top 25
      sql_text.text,
      sql_handle,
      plan_generation_num,
      execution_count,
      dbid,
      objectid 
from sys.dm_exec_query_stats a
      cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc

Un plan de requête inefficace peut entraîner une augmentation de consommation du processeur.

La requête ci-dessous affiche la requête qui utilise le plus de ressources processeur cumulées.

SELECT 
    highest_cpu_queries.plan_handle, 
    highest_cpu_queries.total_worker_time,
    q.dbid,
    q.objectid,
    q.number,
    q.encrypted,
    q.[text]
from 
    (select top 50 
        qs.plan_handle, 
        qs.total_worker_time
    from 
        sys.dm_exec_query_stats qs
    order by qs.total_worker_time desc) as highest_cpu_queries
    cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

La requête suivante affiche certains opérateurs trop gourmands en processeur, tels que «%Hash Match% », « %Sort% », pour la recherche des suspects.

select *
from 
      sys.dm_exec_cached_plans
      cross apply sys.dm_exec_query_plan(plan_handle)
where 
      cast(query_plan as nvarchar(max)) like '%Sort%'
      or cast(query_plan as nvarchar(max)) like '%Hash Match%'

Si vous avez détecté des plans de requête inefficaces provoquant une forte consommation du processeur, exécutez UPDATE STATISTICS sur les tables impliquées dans la requête et vérifiez si le problème persiste. Puis recueillez les données et signalez le problème au service de support de Planification PerformancePoint.

L'exécution de compilations et recompilations trop nombreuses peut entraîner sur le système un problème de performances lié au processeur.

Exécutez les requêtes DMV suivantes pour détecter les compilations/recompilations excessives.

select * from sys.dm_exec_query_optimizer_info
where 
counter = 'optimizations'
or counter = 'elapsed time'

L'exemple de requête suivant vous donne les 25 procédures stockées qui ont été le plus recompilées. La valeur plan_generation_num indique le nombre recompilations de cette requête.

select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid 
from sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where plan_generation_num > 1
order by plan_generation_num desc

Si vous avez détecté des compilations ou des recompilations excessives, collectez le maximum de données et signalez le problème au support de Planification.

Avant d'entreprendre la détection de pressions sur la mémoire et l'enquête associée, activez les options avancées dans SQL Server. Exécutez tout d'abord la requête suivante sur la base de données maître pour activer cette option.

sp_configure 'show advanced options'
go
sp_configure 'show advanced options', 1
go
reconfigure
go

Exécutez la requête suivante pour vérifier d'abord les options de configuration liées à la mémoire.

sp_configure 'awe_enabled'
go
sp_configure 'min server memory'
go
sp_configure 'max server memory'
go
sp_configure 'min memory per query'
go
sp_configure 'query wait'
go

Exécutez la requête DMV suivante pour afficher les informations relatives au processeur, à la mémoire du planificateur et au pool de mémoire tampon.

select 
cpu_count,
hyperthread_ratio,
scheduler_count,
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
bpool_committed * 8 / 1024 as bpool_committed_mb,
bpool_commit_target * 8 / 1024 as bpool_target_mb,
bpool_visible * 8 / 1024 as bpool_visible_mb
from sys.dm_os_sys_info

Pour identifier les goulets d'étranglement au niveau des E/S, examinez les attentes de verrous internes. Exécutez la requête DMV suivante pour rechercher les statistiques d'attente de verrous d'E/S.

select wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms, wait_time_ms / waiting_tasks_count
from sys.dm_os_wait_stats  
where wait_type like 'PAGEIOLATCH%'  and waiting_tasks_count > 0
order by wait_type

Vous pouvez identifier un problème d'E/S si les valeurs waiting_task_counts et wait_time_ms diffèrent de manière significative de ce que vous voyez normalement. Il est important de disposer de valeurs de référence des compteurs de performances et des principaux résultats des requêtes DMV lorsque SQL Server s'exécute sans heurts.

Ces wait_types peuvent indiquer si votre sous-système d'E/S présente un goulet d'étranglement.

Utilisez la requête DMV suivante pour détecter les demandes d'E/S en attente. Exécutez cette requête régulièrement pour vérifier l'état du sous-système d'E/S et pour isoler le ou les disques physiques qui sont impliqués dans les goulets d'étranglement d'E/S.

select 
    database_id, 
    file_id, 
    io_stall,
    io_pending_ms_ticks,
    scheduler_address 
from  sys.dm_io_virtual_file_stats(NULL, NULL)t1,
        sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle

Cette requête ne renvoie généralement rien en situation normale. Si elle renvoie des lignes, vous devrez faire des recherches plus poussées.

Vous pouvez également trouver des requêtes liées aux E/S en exécutant la requête DMV suivante.

select top 5 (total_logical_reads/execution_count) as avg_logical_reads,
                   (total_logical_writes/execution_count) as avg_logical_writes,
           (total_physical_reads/execution_count) as avg_physical_reads,
           Execution_count, statement_start_offset, p.query_plan, q.text
from sys.dm_exec_query_stats
      cross apply sys.dm_exec_query_plan(plan_handle) p
      cross apply sys.dm_exec_sql_text(plan_handle) as q
order by (total_logical_reads + total_logical_writes)/execution_count Desc

Cette requête DMV peut servir à détecter quels lots/demandes génèrent le plus d'E/S. La requête DMV qui suit recherche les cinq demandes qui génèrent le plus d'E/S. L'affinement de ces requêtes améliorera les performances du système.

select top 5 
    (total_logical_reads/execution_count) as avg_logical_reads,
    (total_logical_writes/execution_count) as avg_logical_writes,
    (total_physical_reads/execution_count) as avg_phys_reads,
     Execution_count, 
    statement_start_offset as stmt_start_offset, 
    sql_handle, 
    plan_handle
from sys.dm_exec_query_stats  
order by  (total_logical_reads + total_logical_writes) Desc

Exécutez la requête suivante pour déterminer les sessions qui bloquent.

select blocking_session_id, wait_duration_ms, session_id from 
sys.dm_os_waiting_tasks
where blocking_session_id is not null

Utilisez cet appel pour savoir quelles lignes SQL sont renvoyées par la session blocking_session_id. Par exemple, si la session blocking_session_id est 87, exécutez cette requête pour obtenir le code SQL.

dbcc INPUTBUFFER(87)

La requête suivante affiche l'analyse des attentes SQL et les 10 ressources les plus attendues.

select top 10 *
from sys.dm_os_wait_stats
--where wait_type not in ('CLR_SEMAPHORE','LAZYWRITER_SLEEP','RESOURCE_QUEUE','SLEEP_TASK','SLEEP_SYSTEMTASK','WAITFOR')
order by wait_time_ms desc

Pour savoir quel SPID bloque un autre SPID, créez la procédure stockée suivante dans votre base de données puis exécutez-la. Cette procédure stockée signale la situation de blocage. Tapez sp_who pour trouver @SPID ; @SPID est un paramètre facultatif.

create proc dbo.sp_block (@spid bigint=NULL)
as
select 
    t1.resource_type,
    'database'=db_name(resource_database_id),
    'blk object' = t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id    
from 
    sys.dm_tran_locks as t1, 
    sys.dm_os_waiting_tasks as t2
where 
    t1.lock_owner_address = t2.resource_address and
    t1.request_session_id = isnull(@spid,t1.request_session_id)

Voici des exemples d'utilisation de cette procédure stockée.

exec sp_block
exec sp_block @spid = 7

Tâches

Récupération de Planning Server avec SQL Server

Concepts

Altération d'une base de données SQL Server Analysis Services