sp_server_diagnostics (Transact-SQL)

Se aplica a:SQL Server

Captura datos de diagnóstico e información de estado acerca de SQL Server para detectar errores potenciales. El procedimiento se ejecuta en modo repetido y envía los resultados periódicamente. Se puede invocar desde una conexión normal o desde una conexión de administrador dedicada.

Convenciones de sintaxis de Transact-SQL

Sintaxis

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

Argumentos

[ @repeat_interval = ] 'repeat_interval'

Indica el intervalo de tiempo en el que se ejecuta el procedimiento almacenado repetidamente para enviar información de mantenimiento.

@repeat_interval es int con el valor predeterminado de 0. Los valores de parámetro válidos son 0, o cualquier valor igual o mayor que 5. El procedimiento almacenado tiene que ejecutarse al menos cinco segundos para devolver los datos completos. El tiempo mínimo que el procedimiento almacenado se ejecuta en el modo repetido es 5 segundos.

Si no se especifica este parámetro o si el valor especificado es 0, el procedimiento almacenado devuelve datos una vez y, a continuación, sale.

Si el valor especificado es menor que el valor mínimo, genera un error y no devuelve nada.

Si el valor especificado es igual o mayor que 5, el procedimiento almacenado se ejecuta repetidamente para devolver el estado de mantenimiento hasta que se cancele manualmente.

Valores de código de retorno

0 (correcto) o 1 (erróneo).

Conjunto de resultados

sp_server_diagnostics devuelve la siguiente información.

Estas son las descripciones de los cinco componentes:

• sistema: recopila datos desde una perspectiva del sistema en bloqueos por subprocesos, condiciones de procesamiento graves, tareas sin rendimiento, errores de página y uso de CPU. Esta información genera una recomendación general de estado de mantenimiento.

• recurso: recopila datos desde una perspectiva de recursos en memoria física y virtual, grupos de búferes, páginas, caché y otros objetos de memoria. Esta información genera una recomendación general de estado de mantenimiento.

• query_processing: recopila datos desde una perspectiva de procesamiento de consultas en los subprocesos de trabajo, tareas, tipos de espera, sesiones intensivas de CPU y tareas de bloqueo. Esta información genera una recomendación general de estado de mantenimiento.

• io_subsystem: recopila datos en E/S. Además de los datos de diagnóstico, este componente genera un estado de mantenimiento limpio o de advertencia solamente para un subsistema de E/S.

• eventos: recopila datos y superficies a través del procedimiento almacenado en los errores y eventos de interés registrados por el servidor, incluidos detalles sobre excepciones de búfer de anillo, eventos de búfer de anillo sobre el agente de memoria, memoria insuficiente, monitor de programador, grupo de búferes, interbloqueos, seguridad y conectividad. Los eventos siempre se muestran 0 como estado.

• <nombre del grupo> de disponibilidad: recopila datos para el grupo de disponibilidad especificado (si component_type = "Always On:AvailabilityGroup").

Comentarios

Desde una perspectiva de error, los systemcomponentes , resourcey query_processing se usan para la detección de errores, mientras que los io_subsystem componentes y events solo se usan con fines de diagnóstico.

En la tabla siguiente se asignan los componentes a sus estados de mantenimiento asociados.

El x elemento de cada fila representa estados de mantenimiento válidos para el componente. Por ejemplo, io_subsystem se muestra como clean o warning. No muestra los estados de error.

Permisos

Requiere el permiso VIEW SERVER STATE en el servidor.

Ejemplos

Se recomienda usar sesiones de eventos extendidos para capturar la información de mantenimiento y guardarla en un archivo que se encuentra fuera de SQL Server. Por lo tanto, puede acceder a él si se produce un error.

A Guardar la salida de una sesión de eventos extendidos en un archivo

En el siguiente ejemplo se guarda el resultado de una sesión de eventos en un archivo:

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. Leer el registro de sesión de eventos extendidos

La consulta siguiente lee el archivo de registro de sesión de eventos extendidos en 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. Captura de sp_server_diagnostics salida en una tabla

En el ejemplo siguiente se captura la salida de sp_server_diagnostics en una tabla en modo no repetido:

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 consulta siguiente lee la salida de resumen de la tabla de ejemplo:

SELECT create_time,
    component_name,
    state_desc
FROM SpServerDiagnosticsResult;

D. Leer la salida detallada de cada componente

Las consultas de ejemplo siguientes leen parte de la salida detallada de cada componente, en la tabla creada en el ejemplo anterior.

Sistema:

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

Monitor de recursos:

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

Esperas no preferentes:

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

Esperas preferentes:

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

Solicitudes intensivas de CPU:

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

Informe de proceso bloqueado:

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

Entrada/salida:

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

Información del evento:

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

Contenido relacionado

• Failover Policy for Failover Cluster Instances