sp_server_diagnostics (Transact-SQL)

Перехватывает диагностические данные и сведения о работоспособности SQL Server для обнаружения потенциальных неполадок. Процедура выполняется в режиме повтора и периодически отправляет результаты. Ее можно вызывать из обычного соединения или соединения приложения уровня данных.

Синтаксические обозначения в Transact-SQL

Синтаксис

sp_server_diagnostics [@repeat_interval =] 'repeat_interval_in_seconds'

Аргументы

• [ @repeat_interval =] 'repeat_interval_in_seconds'
  Указывает интервал времени, с которым хранимая процедура повторно выполняется для отправки сведений о работоспособности.

  Аргумент repeat_interval_in_seconds имеет тип int со значением 0 по умолчанию. Допустимыми значениями для параметра являются 0 и любые значения, которые больше или равны 5. Чтобы вернуть полные данные, хранимая процедура должна работать не менее 5 секунд. Минимальное значение для выполнения хранимой процедуры в режиме повтора равно 5 секундам.

  Если этот параметр не указан или задано значение 0, то хранимая процедура один раз вернет данные, а затем завершит работу.

  Если указано значение меньше минимального, то процедура вызывает ошибку и не возвращает данные.

  Если указано значение, большее или равное 5, то хранимая процедура будет повторно выполняться, чтобы возвращать состояние работоспособности, пока не будет отменена вручную.

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

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

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

Процедура sp_server_diagnostics возвращает следующие данные

Далее даны описания пяти компонентов.

• system: Собирает данные с точки зрения системы о спин-блокировках, серьезных затруднениях в обработке, задачах, не дающих результата, ошибках страниц и использовании ЦП. Эти сведения представляют общие рекомендации по состоянию работоспособности.

• resource: Собирает данные с точки зрения ресурсов о физической и виртуальной памяти, буферных пулах, страницах, кэше и других объектах памяти. Эти сведения представляют рекомендации по состоянию работоспособности.

• query_processing: Собирает данные с точки зрения обработки запросов о рабочих потоках, задачах, типах ожидания, сеансах, интенсивно нагружающих ЦП, и блокирующих задачах. Эти сведения представляют рекомендации по состоянию работоспособности.

• io_subsystem: Собирает данные о вводе-выводе. Помимо диагностических данных, этот компонент передает состояние удовлетворительной работоспособности или предупреждение работоспособности только для подсистемы ввода-вывода.

• events: Собирает и делает доступными через хранимую процедуру данные об ошибках и важных событиях, записанных сервером, включая сведения об исключениях кольцевого буфера, события кольцевого буфера о брокере памяти, нехватке памяти, мониторе планировщика, буферном пуле, спин-блокировках, безопасности и возможности подключения. В качестве состояния событий всегда указывается 0.

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

Замечания

Компоненты system, resource и query_processing используются для обнаружения ошибок, а компоненты io_subsystem и events используются только для диагностики.

В следующей таблице представлены компоненты и связанные с ними состояния работоспособности.

Символ (x) в каждой строке представляет допустимые состояния исправности для компонента. Например, в компоненте io_subsystem показывается удовлетворительное состояние или предупреждение, а ошибки не показываются.

Разрешения

Необходимо разрешение VIEW SERVER STATE на сервере.

Примеры

Рекомендуется использовать расширенные сеансы для записи сведения о работоспособности и записывать их в файл, расположенный вне SQL Server. Это позволит сохранить доступ к файлу в случае сбоя. В следующем примере выходные данные сеанса событий сохраняются в файл:

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

В следующем примере запроса считывается файл журнала расширенного сеанса:

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\MSSQL12.MSSQLSERVER\MSSQL\Log\*.xel', NULL, NULL, NULL)
) 
AS XEventData
ORDER BY time;

В следующем примере выходные данные процедуры sp_server_diagnostics записываются в таблице в режиме без повторения:

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;

См. также

Основные понятия

Политика отработки отказа для экземпляров отказоустойчивого кластера