Поделиться через


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 возвращает следующие данные

Столбец

Тип данных

Описание

creation_time

Datetime

Указывает отметку времени создания строки. Все строки в одном наборе данных имеют одинаковые отметки времени.

component_type

Sysname

Указывает, содержит ли строка сведения о компоненте уровня экземпляра SQL Server или о группе доступности AlwaysOn:

  • экземпляр

  • alwaysOn:AvailabilityGroup

component_name

Sysname

Указывает имя компонента или имя группы доступности:

  • система

  • ресурс

  • query_processing

  • io_subsystem

  • события

  • <name of the availability group>

состояние

Int

Указывает состояние исправности компонента:

  • 0

  • 1

  • 2

  • 3

state_desc

Sysname

Описывает столбец state. Далее представлены описания, соответствующие значениям в столбце state:

  • 0: неизвестно

  • 1: удовлетворительно

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

  • 3: ошибка

data

Varchar (max)

Указывает данные, свойственные данному компоненту.

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

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

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

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

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

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

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

Замечания

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

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

Components

Удовлетворительно (1)

Предупреждение (2)

Ошибка (3)

Неизвестно (0)

система

x

x

x

ресурс

x

x

x

query_processing

x

x

x

io_subsystem

x

x

события

x

Символ (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\MSSQL11.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

См. также

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

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