sp_server_diagnostics (Transact-SQL)sp_server_diagnostics (Transact-SQL)

Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions) Применимо к:Applies to: даSQL ServerSQL Server (все поддерживаемые версии) yesSQL ServerSQL Server (all supported versions)

Записывает диагностические данные и сведения о работоспособности SQL ServerSQL Server для выявления потенциальных неполадок.Captures diagnostic data and health information about SQL ServerSQL Server to detect potential failures. Процедура работает в повторяющемся режиме и периодически отправляет результаты.The procedure runs in repeat mode and sends results periodically. Ее можно вызывать из обычного соединения или соединения приложения уровня данных.It can be invoked from either a regular or a DAC connection.

Применимо к: SQL ServerSQL Server (SQL Server 2012 (11.x)SQL Server 2012 (11.x) и выше).Applies to: SQL ServerSQL Server ( SQL Server 2012 (11.x)SQL Server 2012 (11.x) and later).

Значок ссылки на раздел Синтаксические обозначения в Transact-SQLTopic link icon Transact-SQL Syntax Conventions

СинтаксисSyntax

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'   

АргументыArguments

[ @repeat_interval = ] 'repeat_interval_in_seconds' Указывает интервал времени, в течение которого хранимая процедура будет запускаться повторно для отправки сведений о работоспособности.[ @repeat_interval = ] 'repeat_interval_in_seconds' Indicates the time interval at which the stored procedure will run repeatedly to send health information.

repeat_interval_in_seconds имеет тип int и значение по умолчанию 0.repeat_interval_in_seconds is int with the default of 0. Допустимыми значениями для параметра являются 0 и любые значения, которые больше или равны 5.The valid parameter values are 0, or any value equal to or more than 5. Чтобы вернуть полные данные, хранимая процедура должна работать не менее 5 секунд.The stored procedure has to run at least 5 seconds to return complete data. Минимальное значение для выполнения хранимой процедуры в режиме повтора равно 5 секундам.The minimum value for the stored procedure to run in the repeat mode is 5 seconds.

Если этот параметр не указан или задано значение 0, то хранимая процедура один раз вернет данные, а затем завершит работу.If this parameter is not specified, or if the specified value is 0, the stored procedure will return data one time and then exit.

Если указано значение меньше минимального, то процедура вызывает ошибку и не возвращает данные.If the specified value is less than the minimum value, it will raise an error and return nothing.

Если указано значение, большее или равное 5, то хранимая процедура будет повторно выполняться, чтобы возвращать состояние работоспособности, пока не будет отменена вручную.If the specified value is equal to or more than 5, the stored procedure runs repeatedly to return the health state until it is manually canceled.

Значения кода возвратаReturn Code Values

0 (успешное завершение) или 1 (неуспешное завершение)0 (success) or 1 (failure)

Результирующие наборыResult Sets

sp_server_diagnostics возвращает следующие сведения.sp_server_diagnostics returns the following information

СтолбецColumn Тип данныхData type ОписаниеDescription
create_timecreate_time datetimedatetime Указывает отметку времени создания строки.Indicates the time stamp of row creation. Все строки в одном наборе данных имеют одинаковые отметки времени.Each row in a single rowset has the same time stamp.
component_typecomponent_type sysnamesysname Указывает, содержит ли строка сведения для SQL ServerSQL Server компонента уровня экземпляра или для Always on группы доступности:Indicates whether the row contains information for the SQL ServerSQL Server instance level component or for an Always On availability group:

экземплярinstance

Always On: AvailabilityGroupAlways On:AvailabilityGroup
component_namecomponent_name sysnamesysname Указывает имя компонента или имя группы доступности:Indicates the name of component or the name of the availability group:

системаsystem

ресурсresource

query_processingquery_processing

io_subsystemio_subsystem

событияevents

<name of the availability group>
statestate intint Указывает состояние работоспособности компонента:Indicates the health status of the component:

00

11

22

33
state_descstate_desc sysnamesysname Описывает столбец state.Describes the state column. Далее представлены описания, соответствующие значениям в столбце state:Descriptions that correspond to the values in the state column are:

0. Неизвестно0: Unknown

1: чистая очистка1: clean

2: предупреждение2: warning

3: ошибка3: error
datadata varchar (max)varchar (max) Указывает данные, свойственные данному компоненту.Specifies data that is specific to the component.

Далее даны описания пяти компонентов.Here are the descriptions of the five components:

  • система: собирает данные с точки зрения системы на спин-блокировки, серьезные условия обработки, нестандартные задачи, ошибки страниц и загрузку ЦП.system: Collects data from a system perspective on spinlocks, severe processing conditions, non-yielding tasks, page faults, and CPU usage. Эти сведения представляют общие рекомендации по состоянию работоспособности.This information is produces an overall health state recommendation.

  • ресурс: собирает данные с точки зрения ресурса для физической и виртуальной памяти, буферных пулов, страниц, кэша и других объектов памяти.resource: Collects data from a resource perspective on physical and virtual memory, buffer pools, pages, cache and other memory objects. Эти сведения представляют рекомендации по состоянию работоспособности.This information produces an overall health state recommendation.

  • query_processing: собирает данные из перспективы обработки запросов в рабочих потоках, задачах, типах ожидания, сеансах с ИНТЕНСИВНЫМ использованием ЦП и блокирующих задачах.query_processing: Collects data from a query-processing perspective on the worker threads, tasks, wait types, CPU intensive sessions, and blocking tasks. Эти сведения представляют рекомендации по состоянию работоспособности.This information produces an overall health state recommendation.

  • io_subsystem: собирает данные по операциям ввода-вывода.io_subsystem: Collects data on IO. Помимо диагностических данных, этот компонент передает состояние удовлетворительной работоспособности или предупреждение работоспособности только для подсистемы ввода-вывода.In addition to diagnostic data, this component produces a clean healthy or warning health state only for an IO subsystem.

  • события: собирает данные и поверхности с помощью хранимой процедуры в отношении ошибок и событий, которые записываются сервером, включая сведения об исключениях кольцевого буфера, событиях кольцевого буфера о брокере памяти, нехватке памяти, мониторе планировщиков, буферном пуле, спин-блокировки, безопасности и подключении.events: Collects data and surfaces through the stored procedure on the errors and events of interest recorded by the server, including details about ring buffer exceptions, ring buffer events about memory broker, out of memory, scheduler monitor, buffer pool, spinlocks, security, and connectivity . В качестве состояния событий всегда указывается 0.Events will always show 0 as the state.

  • <name of the availability group>: Собирает данные для указанной группы доступности (если component_type = "Always On: AvailabilityGroup").<name of the availability group>: Collects data for the specified availability group (if component_type = "Always On:AvailabilityGroup").

ПримечанияRemarks

Компоненты system, resource и query_processing используются для обнаружения ошибок, а компоненты io_subsystem и events используются только для диагностики.From a failure perspective, the system, resource, and query_processing components will be leveraged for failure detection while the io_subsystem and events components will be leveraged for diagnostic purposes only.

В следующей таблице представлены компоненты и связанные с ними состояния работоспособности.The following table maps the components to their associated health states.

ComponentsComponents Удовлетворительно (1)Clean (1) Предупреждение (2)Warning (2) Ошибка (3)Error (3) Неизвестно (0)Unknowns (0)
системаsystem xx xx xx
ресурсresource xx xx xx
query_processingquery_processing xx xx xx
io_subsystemio_subsystem xx xx
событияevents xx

Символ (x) в каждой строке представляет допустимые состояния исправности для компонента.The (x) in each row represents valid health states for the component. Например, в компоненте io_subsystem показывается удовлетворительное состояние или предупреждение,For example, io_subsystem will either show as clean or warning. а ошибки не показываются.It will not show the error states.

Примечание

Выполнение внутренней процедуры sp_server_diagnostics реализуется в потоке с вытеснением с высоким приоритетом.Execution of sp_server_diagnostics internal procedure is implemented on a preemptive thread at high priority.

РазрешенияPermissions

необходимо разрешение VIEW SERVER STATE на сервере.Requires VIEW SERVER STATE permission on the server.

ПримерыExamples

Рекомендуется использовать расширенные сеансы для записи сведения о работоспособности и записывать их в файл, расположенный вне SQL Server.It is best practice to use the extended sessions to capture the health information and save it to a file that is located outside of SQL Server. Это позволит сохранить доступ к файлу в случае сбоя.Therefore, you can still access it if there is a failure. В следующем примере выходные данные сеанса событий сохраняются в файл:The following example saves the output from an event session to a file:

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  

В следующем примере запроса считывается файл журнала расширенного сеанса:The example query below reads the extended session log file:

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;  

В следующем примере выходные данные процедуры sp_server_diagnostics записываются в таблице в режиме без повторения:The following example captures the output of sp_server_diagnostics to a table in a non-repeat mode:

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; 

В примере запроса ниже считываются сводные выходные значения из таблицы:The example query below reads the summary output from the table:

SELECT create_time,
       component_name,
       state_desc 
FROM SpServerDiagnosticsResult;  

В примере запроса ниже считываются некоторые подробные выходные сведения из каждого компонента в таблице:The example query below reads some of the detailed output from the each component in the table:

-- system
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

-- Resource Monitor
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

-- Nonpreemptive waits
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

-- Preemptive waits
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

-- CPU intensive requests
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

-- Blocked Process Report
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

-- IO
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

-- Event information
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  

См. также:See Also

Failover Policy for Failover Cluster InstancesFailover Policy for Failover Cluster Instances