sp_server_diagnostics (Transact-SQL)

Aplica-se: SQL Server

Captura dados de diagnóstico e informações de integridade sobre o SQL Server para detectar possíveis falhas. O procedimento é executado no modo de repetição e envia resultados periodicamente. Ele pode ser invocado de uma conexão regular ou de uma conexão de administrador dedicada.

Convenções de sintaxe de Transact-SQL

Sintaxe

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

Argumentos

@repeat_interval [ = ] 'repeat_interval'

Indica o intervalo de tempo no qual o procedimento armazenado é executado repetidamente para enviar informações de integridade.

@repeat_interval é int com o padrão de 0. Os valores de parâmetro válidos são 0, ou qualquer valor igual ou maior que 5. O procedimento armazenado deve ser executado por pelo menos 5 segundos para retornar dados completos. O valor mínimo de execução do procedimento armazenado no modo de repetição é de 5 segundos.

Se esse parâmetro não for especificado ou se o valor especificado for 0, o procedimento armazenado retornará dados uma vez e, em seguida, sairá.

Se o valor especificado for menor que o valor mínimo, ele gerará um erro e não retornará nada.

Se o valor especificado for igual ou maior que 5, o procedimento armazenado será executado repetidamente para retornar o estado de integridade até que ele seja cancelado manualmente.

Valores do código de retorno

0 (sucesso) ou 1 (falha).

Conjunto de resultados

sp_server_diagnostics retorna as informações a seguir.

Aqui estão as descrições dos cinco componentes:

• system: coleta dados de uma perspectiva do sistema sobre spinlocks, condições severas de processamento, tarefas não produtivas, falhas de página e uso da CPU. Essas informações produzem uma recomendação geral do estado de integridade.

• resource: coleta dados de uma perspectiva de recurso na memória física e virtual, buffer pools, páginas, cache e outros objetos de memória. Essas informações produzem uma recomendação geral do estado de integridade.

• query_processing: coleta dados de uma perspectiva de processamento de consulta nos threads de trabalho, tarefas, tipos de espera, sessões intensivas de CPU e tarefas de bloqueio. Essas informações produzem uma recomendação geral do estado de integridade.

• io_subsystem: Coleta dados sobre E/S. Além dos dados de diagnóstico, esse componente produz um estado de integridade limpo ou de integridade de aviso somente para um subsistema de IO.

• events: coleta dados e superfícies por meio do procedimento armazenado nos erros e eventos de interesse registrados pelo servidor, incluindo detalhes sobre exceções de buffer de anel, eventos de buffer de anel sobre o agente de memória, memória insuficiente, monitor do agendador, pool de buffers, spinlocks, segurança e conectividade. Os eventos sempre são exibidos 0 como o estado.

• <nome do grupo> de disponibilidade: coleta dados para o grupo de disponibilidade especificado (se component_type = "Always On:AvailabilityGroup").

Comentários

De uma perspectiva de falha, os systemcomponentes , resourcee query_processing são usados para detecção de falhas, enquanto os io_subsystem componentes e events são usados apenas para fins de diagnóstico.

A tabela a seguir mapeia os componentes para seus estados de integridade associados.

O x em cada linha representa estados de integridade válidos para o componente. Por exemplo, io_subsystem mostra como clean ou warning. Ele não mostra os estados de erro.

Permissões

Requer a permissão VIEW SERVER STATE no servidor.

Exemplos

É uma prática recomendada usar sessões de Eventos Estendidos para capturar as informações de integridade e salvá-las em um arquivo localizado fora do SQL Server. Portanto, você ainda pode acessá-lo se houver uma falha.

R. Salvar a saída de uma sessão de Eventos Estendidos em um arquivo

O exemplo a seguir salva a saída de uma sessão de evento em um arquivo:

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. Ler o log da sessão de Eventos Estendidos

A consulta a seguir lê o arquivo de log de sessão de Eventos Estendidos no 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. Capturar sp_server_diagnostics saída para uma tabela

O exemplo a seguir captura a saída de sp_server_diagnostics para uma tabela em um modo sem repetição:

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;

A consulta a seguir lê a saída resumida da tabela de exemplo:

SELECT create_time,
    component_name,
    state_desc
FROM SpServerDiagnosticsResult;

D. Leia a saída detalhada de cada componente

As consultas de exemplo a seguir leem algumas das saídas detalhadas de cada componente, na tabela criada no exemplo 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 não preemptivas:

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 preemptivas:

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

Solicitações com uso intensivo 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

Relatório de processo 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/saída:

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

Informações do 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

Conteúdo relacionado

• Failover Policy for Failover Cluster Instances