sp_server_diagnostics (Transact-SQL)

Article
05/19/2015

Capture des données de diagnostics et des informations d'intégrité relatives à SQL Server afin de détecter des échecs potentiels. La procédure fonctionne en mode de répétition et envoie des résultats régulièrement. Elle peut être appelée depuis une connexion DAC ou ordinaire.

Icône Lien de rubrique Conventions de la syntaxe Transact-SQL

Syntaxe

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'

Arguments

[ @repeat_interval =] 'repeat_interval_in_seconds'
Indique l'intervalle de temps auquel la procédure stockée s'exécutera de façon répétée pour envoyer les informations d'intégrité.

repeat_interval_in_seconds est de type int avec 0 comme valeur par défaut. 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 retournera des données une fois puis s'arrêtera.

Si la valeur spécifiée est inférieure à la valeur minimale, une erreur sera générée et rien ne sera retourné.

Si la valeur spécifiée est supérieure ou égale à 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 (succès) ou 1 (échec)

Jeux de résultats

sp_server_diagnostics retourne les informations suivantes

Colonne	Type de données	Description
creation_time	Datetime	Indique l'horodatage de la création de ligne. Chaque ligne dans un ensemble de lignes unique a le même horodatage.
component_type	Sysname	Indique si la ligne contient des informations sur le composant au niveau de l'instance SQL Server ou sur un groupe de disponibilité AlwaysOn : instance alwaysOn:AvailabilityGroup
component_name	Sysname	Indique le nom du composant ou le nom du groupe de disponibilité : system resource query_processing io_subsystem événements <name of the availability group>
state	Int	Indique l'état d'intégrité du composant : 0 1 2 3
state_desc	Sysname	Décrit la colonne d'état. Les descriptions qui correspondent aux valeurs dans la colonne d'état sont : 0 : inconnu 1 : bon état 2 : avertissement 3 : erreur
data	Varchar (max)	Spécifie des données spécifiques au composant.

Voici les descriptions des cinq composants :

system : recueille des données du point de vue du système sur les verrouillages spinlock, conditions de traitement sévères, tâches improductives, défauts de page et utilisation de l'UC. Ces informations permettent d'obtenir une recommandation de l'état d'intégrité global.
resource : recueille des données du point de vue des ressources 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 permettent d'obtenir une recommandation de l'état d'intégrité global.
query_processing : recueille des données du point de vue du traitement des requête sur les threads de travail, les tâches, les types d'attente, les sessions sollicitant beaucoup l'UC et les tâches de blocage. Ces informations permettent d'obtenir une recommandation de l'état d'intégrité global.
io_subsystem : recueille 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.
events : recueille des données et des surfaces via la procédure stockée sur les erreurs et les événements d'intérêt enregistrés par le serveur, notamment des détails à propos des exceptions de la mémoire tampon de l'anneau, des événements de mémoire tampon en anneau sur le gestionnaire d'allocation mémoire, une mémoire insuffisante, un moniteur de planificateur, un pool de mémoires tampons, des verrouillages spinlock, la sécurité et la connectivité. Les événements afficheront toujours 0 comme état.
<name of the availability group> : collecte des données pour le groupe de disponibilité spécifié (si component_type = « alwaysOn:AvailabilityGroup »).

Notes

Du point de vue d'un échec, les composant system, resource et query_processing seront exploités pour la détection de pannes, tandis que les composants io_subsystem et events le seront uniquement à des fins de diagnostics.

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

Composants	Bon état (1)	Avertissement (2)	Erreur (3)	Inconnu (0)
système	x	x	x
ressource	x	x	x
query_processing	x	x	x
io_subsystem	x	x
événements				x

Le (x) dans chaque ligne représente des états d'intégrité valides pour le composant. Par exemple, io_subsystem indiquera un bon état ou un avertissement. Il n'affichera pas les états d'erreur.

Autorisations

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

Exemples

Il est recommandé d'utiliser les sessions étendues pour capturer les informations d'intégrité et de les enregistrer dans un fichier situé en dehors de SQL Server. Par conséquent, vous pouvez toujours y accéder en cas d'échec. 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

La requête d'exemple ci-dessous lit le fichier journal de session étendue :

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\MSSQL11.MSSQLSERVER\MSSQL\Log\*.xel', NULL, NULL, NULL)
) 
AS XEventData
ORDER BY time

L'exemple suivant capture la sortie de sp_server_diagnostics dans une table dans un mode de non-répétition :

CREATE TABLE SpServerDiagnosticsResult
(
      create_time DateTime,
      component_type sysname,
      component_name sysname,
      state int,
      state_desc sysname,
      data nvarchar(max)
)
INSERT INTO SpServerDiagnosticsResult 
EXEC sp_server_diagnostics

Voir aussi

Concepts

Stratégie de basculement pour les instances de cluster de basculement

Share via