sp_server_diagnostics (Transact-SQL)

Статья
05/19/2015

Перехватывает диагностические данные и сведения о работоспособности 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

См. также

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

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

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