sp_server_diagnostics (Transact-SQL)

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

Convenciones de sintaxis de Transact-SQL

Sintaxis

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'

Argumentos

• [ @repeat_interval =] 'repeat_interval_in_seconds'
  Indica el intervalo de tiempo en que el procedimiento almacenado se ejecutará varias veces para enviar información del estado.

  repeat_interval_in_seconds es de tipo int y su valor predeterminado es 0. Los valores válidos de los parámetros son 0 o cualquier valor mayor o igual 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 devolverá los datos una vez y, a continuación, saldrá.

  Si el valor especificado es menor que el valor mínimo, generará un error y no devolverá nada.

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

Valores del código de retorno

0 (correcto) o 1 (error)

Conjuntos de resultados

sp_server_diagnostics devuelve la siguiente información

Estas son las descripciones de los cinco componentes:

• system: recopila los datos desde una perspectiva del sistema en los bloqueos por subproceso, condiciones de procesamiento graves, tareas improductivas, errores de página y uso de CPU. Esta información genera una recomendación del estado de mantenimiento total.

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

• query_processing: recopila datos desde la perspectiva del procesamiento de consultas en subprocesos de trabajo, tareas, tipos de espera, sesiones que usan mucho la CPU y tareas de bloqueo. Esta información genera una recomendación del estado de mantenimiento total.

• io_subsystem: recopila datos de la 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.

• events: recopila datos y superficies a través del procedimiento almacenado sobre los errores y eventos de interés registrados por el servidor, incluidos los detalles de excepciones de búfer de anillo, los eventos de búfer de anillo acerca del agente de memoria, la falta de memoria, el supervisor del programador, el grupo de búferes, los bloqueos de subprocesos, la seguridad y la conectividad. Los eventos mostrarán siempre 0 como estado.

• <name of the availability group>: recopila los datos del grupo de disponibilidad especificado (si component_type = “alwaysOn:AvailabilityGroup”).

Comentarios

Desde la perspectiva de los errores, los componentes del sistema, recursos y procesamiento de consultas se aprovecharán para la detección de errores mientras que los componentes de eventos e io_subsystem se aprovecharán solo con fines de diagnóstico.

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

La (x) de cada fila representa los estados de mantenimiento válidos para el componente. Por ejemplo, io_subsystem se mostrará como limpio o como advertencia. No mostrará los estados de error.

Permisos

Se requiere el permiso VIEW SERVER STATE en el servidor.

Ejemplos

Se recomienda utilizar sesiones extendidas para capturar la información de estado y guardarla en un archivo que se encuentre fuera de SQL Server. Por consiguiente, todavía puede acceder a ella si hay un error. 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

La consulta de ejemplo siguiente lee el archivo de registro de sesión extendido:

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

En el siguiente ejemplo se captura el resultado de sp_server_diagnostics en una tabla en un modo no repetido:

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

Vea también

Conceptos

Directiva de conmutación por error para instancias de clústeres de conmutación por error