sp_server_diagnostics (Transact-SQL)

Article
04/10/2024

S’applique à :SQL Server

Capture des données de diagnostics et des informations d’intégrité sur SQL Server pour détecter des défaillances potentielles. La procédure fonctionne en mode de répétition et envoie régulièrement des résultats. Il peut être appelé à partir d’une connexion régulière ou d’une connexion d’administrateur dédiée.

Conventions de la syntaxe Transact-SQL

Syntaxe

sp_server_diagnostics [ @repeat_interval = ] 'repeat_interval'
[ ; ]

Arguments

[ @repeat_interval = ] 'repeat_interval'

Indique l’intervalle de temps auquel la procédure stockée s’exécute à plusieurs reprises pour envoyer des informations d’intégrité.

@repeat_interval est int avec la valeur par défaut .0 Les valeurs de paramètre valides sont 0, ou toute valeur égale ou supérieure 5à . La procédure stockée doit s'exécuter au moins 5 secondes pour retourner des données complètes. La valeur minimale pour que la procédure stockée s'exécute en mode de répétition est de 5 secondes.

Si ce paramètre n’est pas spécifié ou si la valeur spécifiée est 0, la procédure stockée retourne des données une fois, puis quittez.

Si la valeur spécifiée est inférieure à la valeur minimale, elle génère une erreur et ne retourne rien.

Si la valeur spécifiée est égale ou supérieure 5à , la procédure stockée s’exécute à plusieurs reprises pour retourner l’état d’intégrité jusqu’à ce qu’elle soit annulée manuellement.

Valeurs des codes de retour

0 (réussite) ou 1 (échec).

Jeu de résultats

sp_server_diagnostics retourne les informations suivantes.

Colonne	Type de données	Description
`create_time`	datetime	Indique l'horodateur de la création de ligne. Chaque ligne dans un ensemble de lignes unique a le même horodateur.
`component_type`	sysname	Indique si la ligne contient des informations pour le composant au niveau de l’instance SQL Server ou pour un groupe de disponibilité Always On : `instance` `Always On:AvailabilityGroup`
`component_name`	sysname	Indique le nom du composant ou le nom du groupe de disponibilité : `system` `resource` `query_processing` `io_subsystem` `events` `<name of the availability group>`
`state`	int	Indique l’état d’intégrité du composant. Il peut s’agir de l’une des valeurs suivantes : `0`, , `12`ou`3`
`state_desc`	sysname	Décrit la colonne d'état. Les descriptions qui correspondent aux valeurs dans la colonne d'état sont : 0: `Unknown` 1: `clean` 2: `warning` 3: `error`
`data`	varchar (max)	Spécifie des données spécifiques au composant.

Voici les descriptions des cinq composants :

système : collecte des données du point de vue du système sur les spinlocks, les conditions de traitement sévères, les tâches sans rendement, les erreurs de page et l’utilisation du processeur. Ces informations produisent une recommandation d’état d’intégrité globale.
ressource : collecte des données du point de vue de la ressource sur la mémoire physique et virtuelle, les pools de mémoires tampons, les pages, le cache et d’autres objets mémoire. Ces informations produisent une recommandation d’état d’intégrité globale.
query_processing : collecte des données du point de vue du traitement des requêtes sur les threads de travail, les tâches, les types d’attente, les sessions nécessitant beaucoup d’UC et les tâches bloquantes. Ces informations produisent une recommandation d’état d’intégrité globale.
io_subsystem : collecte des données sur les E/S. En plus des données de diagnostics, ce composant produit un état d'intégrité sain ou un état d'intégrité d'avertissement uniquement pour un sous-système d'E/S.
événements : collecte des données et des surfaces par le biais de la procédure stockée sur les erreurs et les événements d’intérêt enregistrés par le serveur, y compris les détails sur les exceptions de mémoire tampon en anneau, les événements de mémoire tampon sur le répartiteur de mémoire, la mémoire insuffisante, le moniteur du planificateur, le pool de mémoires tampons, les verrous de rotation, la sécurité et la connectivité. Les événements s’affichent 0 toujours en tant qu’état.
<nom du groupe> de disponibilité : collecte les données du groupe de disponibilité spécifié (si component_type = "Always On:AvailabilityGroup").

Notes

Du point de vue de l’échec, les systemcomposants et query_processing les resourcecomposants sont utilisés pour la détection des défaillances alors que les composants et events les io_subsystem composants sont utilisés uniquement à des fins de diagnostic.

Le tableau suivant mappe les composants à leurs états d'intégrité associés.

Composants	Bon état (1)	Avertissement (2)	Erreur (3)	Inconnu (0)
`system`	x	x	x
`resource`	x	x	x
`query_processing`	x	x	x
`io_subsystem`	x	x
`events`				x

La x ligne de chaque ligne représente des états d’intégrité valides pour le composant. Par exemple, io_subsystem l’une ou l’autre affiche sous la warningforme clean . Il n’affiche pas les états d’erreur.

Remarque

La sp_server_diagnostics procédure interne est implémentée sur un thread préemptif à priorité élevée.

Autorisations

Nécessite l'autorisation VIEW SERVER STATE sur le serveur.

Exemples

Il est recommandé d’utiliser des sessions d’événements étendus pour capturer les informations d’intégrité et les enregistrer dans un fichier situé en dehors de SQL Server. Par conséquent, vous pouvez toujours y accéder en cas de défaillance.

A. Enregistrer la sortie d’une session Événements étendus dans un fichier

L'exemple suivant enregistre la sortie d'une session d'événement dans un fichier :

CREATE EVENT SESSION [diag]
ON SERVER
    ADD EVENT [sp_server_diagnostics_component_result] (set collect_data=1)
    ADD TARGET [asynchronous_file_target] (set filename='C:\temp\diag.xel');
GO
ALTER EVENT SESSION [diag]
    ON SERVER STATE = start;
GO

B. Lire le journal de session Événements étendus

La requête suivante lit le fichier journal de session Événements étendus sur SQL Server 2016 (13.x) :

SELECT xml_data.value('(/event/@name)[1]', 'varchar(max)') AS Name,
    xml_data.value('(/event/@package)[1]', 'varchar(max)') AS Package,
    xml_data.value('(/event/@timestamp)[1]', 'datetime') AS 'Time',
    xml_data.value('(/event/data[@name=''component_type'']/value)[1]', 'sysname') AS SYSNAME,
    xml_data.value('(/event/data[@name=''component_name'']/value)[1]', 'sysname') AS Component,
    xml_data.value('(/event/data[@name=''state'']/value)[1]', 'int') AS STATE,
    xml_data.value('(/event/data[@name=''state_desc'']/value)[1]', 'sysname') AS State_desc,
    xml_data.query('(/event/data[@name="data"]/value/*)') AS Data
FROM (
    SELECT object_name AS event,
        CONVERT(XML, event_data) AS xml_data
    FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\*.xel', NULL, NULL, NULL)
) AS XEventData
ORDER BY TIME;

C. Capturer la `sp_server_diagnostics` sortie dans une table

L’exemple suivant capture la sortie d’une sp_server_diagnostics table en mode non répétitif :

CREATE TABLE SpServerDiagnosticsResult (
    create_time DATETIME,
    component_type SYSNAME,
    component_name SYSNAME,
    [state] INT,
    state_desc SYSNAME,
    [data] XML
);

INSERT INTO SpServerDiagnosticsResult
EXEC sp_server_diagnostics;

La requête suivante lit la sortie récapitulative de l’exemple de tableau :

SELECT create_time,
    component_name,
    state_desc
FROM SpServerDiagnosticsResult;

D. Lire la sortie détaillée de chaque composant

L’exemple de requête suivant lit une partie de la sortie détaillée de chaque composant, dans la table créée dans l’exemple précédent.

Système :

SELECT data.value('(/system/@systemCpuUtilization)[1]', 'bigint') AS 'System_CPU',
    data.value('(/system/@sqlCpuUtilization)[1]', 'bigint') AS 'SQL_CPU',
    data.value('(/system/@nonYieldingTasksReported)[1]', 'bigint') AS 'NonYielding_Tasks',
    data.value('(/system/@pageFaults)[1]', 'bigint') AS 'Page_Faults',
    data.value('(/system/@latchWarnings)[1]', 'bigint') AS 'Latch_Warnings',
    data.value('(/system/@BadPagesDetected)[1]', 'bigint') AS 'BadPages_Detected',
    data.value('(/system/@BadPagesFixed)[1]', 'bigint') AS 'BadPages_Fixed'
FROM SpServerDiagnosticsResult
WHERE component_name LIKE 'system'
GO

Moniteur de ressources :

SELECT data.value('(./Record/ResourceMonitor/Notification)[1]', 'VARCHAR(max)') AS [Notification],
    data.value('(/resource/memoryReport/entry[@description=''Working Set'']/@value)[1]', 'bigint') / 1024 AS [SQL_Mem_in_use_MB],
    data.value('(/resource/memoryReport/entry[@description=''Available Paging File'']/@value)[1]', 'bigint') / 1024 AS [Avail_Pagefile_MB],
    data.value('(/resource/memoryReport/entry[@description=''Available Physical Memory'']/@value)[1]', 'bigint') / 1024 AS [Avail_Physical_Mem_MB],
    data.value('(/resource/memoryReport/entry[@description=''Available Virtual Memory'']/@value)[1]', 'bigint') / 1024 AS [Avail_VAS_MB],
    data.value('(/resource/@lastNotification)[1]', 'varchar(100)') AS 'LastNotification',
    data.value('(/resource/@outOfMemoryExceptions)[1]', 'bigint') AS 'OOM_Exceptions'
FROM SpServerDiagnosticsResult
WHERE component_name LIKE 'resource'
GO

Attentes non déconseillées :

SELECT waits.evt.value('(@waitType)', 'varchar(100)') AS 'Wait_Type',
    waits.evt.value('(@waits)', 'bigint') AS 'Waits',
    waits.evt.value('(@averageWaitTime)', 'bigint') AS 'Avg_Wait_Time',
    waits.evt.value('(@maxWaitTime)', 'bigint') AS 'Max_Wait_Time'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/topWaits/nonPreemptive/byDuration/wait') AS waits(evt)
WHERE component_name LIKE 'query_processing'
GO

Attentes préemptives :

SELECT waits.evt.value('(@waitType)', 'varchar(100)') AS 'Wait_Type',
    waits.evt.value('(@waits)', 'bigint') AS 'Waits',
    waits.evt.value('(@averageWaitTime)', 'bigint') AS 'Avg_Wait_Time',
    waits.evt.value('(@maxWaitTime)', 'bigint') AS 'Max_Wait_Time'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/topWaits/preemptive/byDuration/wait') AS waits(evt)
WHERE component_name LIKE 'query_processing'
GO

Demandes nécessitant beaucoup d’UC :

SELECT cpureq.evt.value('(@sessionId)', 'bigint') AS 'SessionID',
    cpureq.evt.value('(@command)', 'varchar(100)') AS 'Command',
    cpureq.evt.value('(@cpuUtilization)', 'bigint') AS 'CPU_Utilization',
    cpureq.evt.value('(@cpuTimeMs)', 'bigint') AS 'CPU_Time_ms'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/cpuIntensiveRequests/request') AS cpureq(evt)
WHERE component_name LIKE 'query_processing'
GO

Rapport de processus bloqué :

SELECT blk.evt.query('.') AS 'Blocked_Process_Report_XML'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/queryProcessing/blockingTasks/blocked-process-report') AS blk(evt)
WHERE component_name LIKE 'query_processing'
GO

Entrée/sortie :

SELECT data.value('(/ioSubsystem/@ioLatchTimeouts)[1]', 'bigint') AS 'Latch_Timeouts',
    data.value('(/ioSubsystem/@totalLongIos)[1]', 'bigint') AS 'Total_Long_IOs'
FROM SpServerDiagnosticsResult
WHERE component_name LIKE 'io_subsystem'
GO

Informations sur l’événement :

SELECT xevts.evt.value('(@name)', 'varchar(100)') AS 'xEvent_Name',
    xevts.evt.value('(@package)', 'varchar(100)') AS 'Package',
    xevts.evt.value('(@timestamp)', 'datetime') AS 'xEvent_Time',
    xevts.evt.query('.') AS 'Event Data'
FROM SpServerDiagnosticsResult
CROSS APPLY data.nodes('/events/session/RingBufferTarget/event') AS xevts(evt)
WHERE component_name LIKE 'events'
GO

Failover Policy for Failover Cluster Instances

sp_server_diagnostics (Transact-SQL)

Syntaxe

Arguments

[ @repeat_interval = ] 'repeat_interval'

Valeurs des codes de retour

Jeu de résultats

Notes

Autorisations

Exemples

A. Enregistrer la sortie d’une session Événements étendus dans un fichier

B. Lire le journal de session Événements étendus

C. Capturer la `sp_server_diagnostics` sortie dans une table

D. Lire la sortie détaillée de chaque composant

Commentaires

Commentaires

Ressources supplémentaires

sp_server_diagnostics (Transact-SQL)

Syntaxe

Arguments

[ @repeat_interval = ] 'repeat_interval'

Valeurs des codes de retour

Jeu de résultats

Notes

Autorisations

Exemples

A. Enregistrer la sortie d’une session Événements étendus dans un fichier

B. Lire le journal de session Événements étendus

C. Capturer la sp_server_diagnostics sortie dans une table

D. Lire la sortie détaillée de chaque composant

Contenu connexe

Commentaires

Commentaires

Ressources supplémentaires

C. Capturer la `sp_server_diagnostics` sortie dans une table