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
Columna |
Tipo de datos |
Descripción |
---|---|---|
creation_time |
Datetime |
Indica la marca de tiempo de creación de la fila. Cada fila de un conjunto de filas único tiene la misma marca de tiempo. |
component_type |
Sysname |
Indica si la fila contiene información para el componente de nivel de instancia de SQL Server o para el grupo de disponibilidad AlwaysOn:
|
component_name |
Sysname |
Indica el nombre del componente o el nombre del grupo de disponibilidad:
|
state |
Int |
Indica el estado de mantenimiento del componente:
|
state_desc |
Sysname |
Describe la columna de estado. Las descripciones que corresponden a los valores de la columna de estado son:
|
data |
Varchar (max) |
Especifica los datos que son específicos del componente. |
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.
Components |
Limpio (1) |
Advertencia (2) |
Error (3) |
Desconocido (0) |
---|---|---|---|---|
system |
x |
x |
x |
|
resource |
x |
x |
x |
|
query_processing |
x |
x |
x |
|
io_subsystem |
x |
x |
||
eventos |
x |
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