Использование Zabbix для слежения за базой данных MS SQL Server

Предисловие

Часто возникает потребность в режиме реального времени сообщать администратору о проблемах, связанных с БД (базой данных).

В данной статье будет описано, что необходимо настроить в Zabbix для слежения за базой данных MS SQL Server.

Обращаю внимание на то, что подробно как настраивать приводиться не будет, однако формулы и общие рекомендации, а также подробное описание по добавлению пользовательских элементов данных через хранимые процедуры будут приведены в данной статье.
Также здесь будет рассмотрены только основные счетчики производительности.

Решение

Вначале опишу все те счетчики производительности (через элементы данных в Zabbix), которые нам нужны:

1. Logical Disk
   1. Avg Disc sec/Read
      Показывает выраженное в секундах среднее время чтения данных с диска. Среднее значение счетчика производительности Avg. Disk sec/Read не должно превышать 10 миллисекунд. Максимальное значение счетчика производительности Avg. Disk sec/Read не должно превышать 50 миллисекунд.

      Zabbix: perf_counter[LogicalDisk(_Total)Avg. Disk sec/Read], а также важно проследить за нужным диском, например так: perf_counter[LogicalDisk(C:)Avg. Disk sec/Read]

      Примеры триггеров:
      {НАЗВАНИЕ_УЗЛА:perf_counter[LogicalDisk(_Total)Avg. Disk sec/Read].last()}>0.005, уровень-высокий
      и
      {НАЗВАНИЕ_УЗЛА:perf_counter[LogicalDisk(_Total)Avg. Disk sec/Read].last()}>0.0025, уровень-средний

   2. Avg Disc sec/Write
      Показывает выраженное в секундах среднее время записи данных на диск. Среднее значение счетчика производительности Avg. Disk sec/Write не должно превышать 10 миллисекунд. Максимальное значение счетчика производительности Avg. Disk sec/Write не должно превышать 50 миллисекунд.

      Zabbix: perf_counter[LogicalDisk(_Total)Avg. Disk sec/Write], а также важно проследить за нужным диском, например так: perf_counter[LogicalDisk(C:)Avg. Disk sec/Write]

      Примеры триггеров:
      {НАЗВАНИЕ_УЗЛА:perf_counter[LogicalDisk(_Total)Avg. Disk sec/Write].last()}>0.005, уровень-высокий
      и
      {НАЗВАНИЕ_УЗЛА:perf_counter[LogicalDisk(_Total)Avg. Disk sec/Write].last()}>0.0025, уровень-средний

   3. Avg Disk Queue Length

      Cредняя длина очереди запросов к диску. Отображает количество запросов к диску, ожидающих обработки в течении определенного интервала времени. Нормальным считается очередь не больше 2 для одиночного диска. Если в очереди больше двух запросов, то возможно диск перегружен и не успевает обрабатывать поступающие запросы. Уточнить, с какими именно операциями не справляется диск, можно с помощью счетчиков Avg. Disk Read Queue Length (очередь запросов на чтение) и Avg. Disk Wright Queue Length (очередь запросов на запись).
      Значение Avg. Disk Queue Length не измеряется, а рассчитывается по закону Литтла из математической теории очередей. Согласно этому закону, количество запросов, ожидающих обработки, в среднем равняется частоте поступления запросов, умноженной на время обработки запроса. Т.е. в нашем случае Avg. Disk Queue Length = (Disk Transfers/sec) * (Avg. Disk sec/Transfer).

      Avg. Disk Queue Length приводится как один из основных счетчиков для определения загруженности дисковой подсистемы, однако для его адекватной оценки необходимо точно представлять физическую структуру системы хранения. К примеру, для одиночного жесткого диска критическим считается значение больше 2, а если диск располагается на RAID-массиве из 4-х дисков, то волноваться стоит при значении больше 4*2=8.

      Zabbix: perf_counter[LogicalDisk(_Total)Avg. Disk Queue Length], а также важно проследить за нужным диском, например так: perf_counter[LogicalDisk(C:)Avg. Disk Queue Length]

2. Memory
   1. Pages/sec
      Показывает число страниц, которые SQL Server считал с диска или записал на диск для того, чтобы разрешить обращения к страницам памяти, которые не были загружены в оперативную память в момент обращения. Эта величина является суммой величин Pages Input/sec и Pages Output/sec, а также учитывает страничный обмен (подкачку/свопинг) системной кэш-памяти для доступа к файлам данных приложений. Кроме того, сюда включается подкачка не кэшированных файлов, непосредственно отображаемых в память. Это основной счетчик, за которым следует следить в том случае, если наблюдается большая нагрузка на использование памяти и связанный с этим избыточный страничный обмен. Этот счётчик характеризует величину свопинга и его нормальное (не пиковое) значение должно быть близко к нолю. Увеличение свопинга говорит о необходимости наращивания ОЗУ или уменьшения числа исполняемых на сервере прикладных программ.

      Zabbix: perf_counter[MemoryPages/sec]
      Пример триггера:
      {НАЗВАНИЕ_УЗЛА:perf_counter[MemoryPages/sec].min(5m)}>1000, уровень-информация

   2. Page Faults/sec

      Это значение счетчика ошибок страницы. Ошибка страницы возникает, когда процесс ссылается на страницу виртуальной памяти, которая не находится в рабочем множестве оперативной памяти. Данный счетчик учитывает как те ошибки страницы, которые требуют обращения к диску, так и те, которые вызваны нахождением страницы вне рабочего множества в оперативной памяти. Большинство процессоров могут обрабатывать ошибки страницы второго типа без особых задержек. Однако, обработка ошибок страницы первого типа, требующая доступа к диску, может привести к значительным задержкам.

      Zabbix: perf_counter[MemoryPage Faults/sec]
      Пример триггера:
      {НАЗВАНИЕ_УЗЛА:perf_counter[MemoryPage Faults/sec].min(5m)}>1000, уровень-информация

   3. Available Bytes

      Отслеживает количество доступной памяти в байтах для выполнения различных процессов. Низкие показатели означают нехватку памяти. Решение — увеличить память. Этот счётчик в большинстве случаев должен быть постоянно выше 5000 КВ.
      Есть смысл выставлять порог для Available Mbytes вручную из соображений:

      •50% свободной памяти доступно = Отлично
      •25% доступно памяти = Требует внимания
      •10% свободно = Возможны проблемы
      •Меньше 5% доступно памяти= Критично для скорости, нужно вмешиваться.
      Zabbix: perf_counter[MemoryAvailable Bytes]

3. Processor (Total): % Processor Time
   Этот счетчик показывает процентное отношение времени, которое процессор был занят выполнением операций для не простаивающих потоков (non-Idle thread). Эту величину можно рассматривать как долю времени, приходящегося на выполнение полезной работы. Каждый процессор может быть назначен простаивающему потоку, который потребляет непродуктивные циклы процессора, не используемые другими потоками. Для этого счётчика характерны непродолжительные пики, которые могут достигать 100 процентов. Однако, если наблюдаются продолжительные периоды, когда утилизация процессора выше 80 процентов, то система будет более эффективной при использовании большего числа процессоров.

   Zabbix: perf_counter[Processor(_Total)% Processor Time], здесь же может иметь место по ядрам выводить
   Пример триггера:
   {НАЗВАНИЕ_УЗЛА:perf_counter[Processor(_Total)% Processor Time].min(5m)}>80, уровень-информация

4. Network Interface (*): % Bytes Total/sec
   Общее количество переданных и полученных байт за секунду по всем интерфейсам. Это пропускная способность интерфейса (в байтах). Необходимо сравнить значение этого счётчика с максимальной пропускной способностью сетевой платы. Вообще, этот счётчик должен показать не более 50% утилизации пропускной способности сетевого адаптера.
   Zabbix: perf_counter[Network Interface(*)Bytes Sent/sec]
5. MS SQL Server: Access Methods
   Объект Access Methods (Методы доступа) в SQL Server предоставляет счетчики, помогающие следить за доступом к логическим данным в рамках базы данных. Физический доступ к страницам базы данных на диске контролируется при помощи счетчиков диспетчера буферов. Наблюдение за методами доступа к данным в базе данных помогает определить, можно ли увеличить производительность запросов путем добавления или изменения индексов, добавления или перемещения секций, добавления файлов или групп файлов, дефрагментации индексов или изменения текста запросов. Кроме того, при помощи счетчиков объекта Access Methods можно следить за размером данных, индексов и свободного пространства в базе данных, контролируя объем и фрагментацию для каждого экземпляра сервера. Чрезмерная фрагментация индексов может значительно снизить производительность.
   1. Page Splits/sec
      Количество разбиений страниц в секунду, выполненных в результате переполнения страниц индекса. Большое значение этого показателя означает, что при выполнении операций вставки и изменения данных SQL Server приходится выполнять большое количество ресурсоемких операций по разбиению страниц и переносу части существующей страницы на новое место. Таких операций по возможности следует избегать. Проблему можно попытаться решить двумя способами:
      — создать кластерный индекс для столбцов с автоприращением. В этом случае новые записи не будут помещаться внутрь страниц, уже занятых данными, а будут последовательно занимать новые страницы;
      — перестроить индексы, увеличив значение параметра Fillfactor. Этот параметр позволяет зарезервировать в страницах индексов свободное место, которое будет использоваться для размещения новых данных, без необходимости производить операции разбиения страниц.
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Access MethodsPage Splits/sec",30]
      Пример триггера: {НАЗВАНИЕ_УЗЛА:perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Access MethodsPage Splits/sec",30].last()}>{НАЗВАНИЕ_УЗЛА:perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:SQL StatisticsBatch Requests/sec",30].last()}/5, уровень-информация
   2. Full Scans/sec
      Количество неограниченных операций полного сканирования в секунду. К таким операциям относятся сканирование основной таблицы и полное сканирование индекса. Стабильное повышение этого показателя может свидетельствовать о деградации системы (нехватка нужных индексов, их сильная фрагментация, неиспользование оптимизатором существующих индексов, наличие неиспользуемых индексов). Однако, стоит отметить, что полное сканирование в небольших таблицах невсегда плохо, т к если удается всю таблицу разместить в ОЗУ, то как раз быстрее будет произвести полное сканирование. Но в большинстве случаев стабильный рост показателя этого счетчика будет говорить о деградации системы. Все это применимо только для OLTP-систем. В OLAP-системах постоянные полные сканирования-это нормально.
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Access MethodsFull Scans/sec",30]

6. MS SQL Server: Buffer Manager
   Объект Buffer Manager (диспетчера буферов) предоставляет счетчики, позволяющие наблюдать за тем, как SQL Server использует следующие ресурсы:
   — память для хранения страниц данных;
   — счетчики, служащие для мониторинга физического ввода-вывода, когда SQL Server считывает и записывает страницы баз данных;
   — расширение буферного пула для расширения буферного кэша с использованием быстрой энергонезависимой памяти, например твердотельных накопителей (SSD);
   — мониторинг памяти и счетчиков, используемых SQL Server, помогает получить следующие сведения;
   — существуют ли «узкие места», вызванные недостатком физической памяти. Если часто используемые данные не могут быть сохранены в кэше, SQL Server вынужден считывать их с диска;
   — можно ли повысить эффективность выполнения запросов, увеличив объем памяти или выделив дополнительную память для кэширования данных или хранения внутренних структур SQL Server;
   — насколько часто SQL Server считывает данные с диска. В сравнении с другими операциями, такими как доступ к памяти, физический ввод-вывод выполняется дольше. Уменьшение объема ввода-вывода может повысить производительность выполнения запросов.
   1. Buffer Cache hit radio
      Показывает, насколько полно SQL Server может разместить данные в буфере кэша. Чем выше это значение, тем лучше, т.к. для эффективного обращения SQL сервера к страницам данных, они должны находиться в буфере кэша, и операции физического ввода-вывода (I/O) должны отсутствовать. Если наблюдается устойчивое снижение среднего значения этого счётчика, необходимо рассмотреть возможность добавления ОЗУ. Данный показатель всегда должен быть выше 90% для OLTP-систем и выше 50% для OLAP-систем.
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Buffer ManagerBuffer cache hit ratio",30]
      Примеры триггеров: {НАЗВАНИЕ_УЗЛА:perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Buffer ManagerBuffer cache hit ratio",30].last()}<70, уровень-высокий
      и
      {НАЗВАНИЕ_УЗЛА:perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Buffer ManagerBuffer cache hit ratio",30].last()}<80, уровень-средний
   2. Page life expectancy
      Показывает, как долго страница будет постоянно находиться в памяти в нынешнем состоянии. Если значение постоянно падает, то это означает, что система злоупотребляет буферным пулом. Таким образом, потенциально работа памяти может вызывать проблемы, приводящие к снижению производительности. Стоит отметить, что не существует универсального показателя, ниже которого можно однозначно судить о том, что система злоупотребляет буферным пулом (показатель в 300 секунд устарел с MS SQL Server 2012).
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Buffer ManagerPage life expectancy",30]
      Пример триггера: {НАЗВАНИЕ_УЗЛА:perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Buffer ManagerPage life expectancy",30].last()}<5, уровень-информация

7. MS SQL Server: General Statistics
   Объект General Statistics (Общая статистика) в SQL Server предоставляет счетчики, позволяющие наблюдать общую активность сервера, например количество одновременных соединений и количество пользователей в секунду, подключающихся или отключающихся от компьютера, где запущен экземпляр SQL Server. Эти показатели полезно использовать в больших системах оперативной обработки транзакций (OLTP), где большое количество клиентов постоянно подключаются и отключаются от экземпляра SQL Server.
   1. Process blocked
      Количество блокированных в данный момент процессов.
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:General StatisticsProcesses blocked",30]
      Пример триггера: ({НАЗВАНИЕ_УЗЛА:perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:General StatisticsProcesses blocked",30].min(2m,0)}>=0)
      and ({НАЗВАНИЕ_УЗЛА:perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:General StatisticsProcesses blocked",30].time(0)}>=50000)
      and ({НАЗВАНИЕ_УЗЛА:perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:General StatisticsProcesses blocked",30].time(0)}<=230000), уровень-информация (здесь идет ограничение по сигнализации с 05:00 до 23:00)
   2. User Connections
      Количество пользователей, подключенных в данный момент к серверу SQL Server.
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:General StatisticsUser Connections",30]

8. MS SQL Server: Locks
   Объект Locks (Блокировки) в Microsoft SQL Server предоставляет сведения о блокировках SQL Server, полученных для отдельных типов ресурсов. Блокировки выдаются на такие ресурсы SQL Server, как прочитанные или измененные транзакцией строки, для предотвращения одновременного использования ресурсов несколькими транзакциями. Например, если исключительная (X) блокировка получена транзакцией на строку в таблице, никакая другая транзакция не сможет изменить эту строку, пока блокировка не будет освобождена. Минимизация использования блокировок повышает параллелизм, что может улучшить общую производительность. Одновременно может отслеживаться несколько экземпляров объекта Locks, каждый из которых будет представлять собой блокировку отдельного вида ресурсов.
   1. Average Wait Time (ms)
      Средняя длительность ожидания (в миллисекундах) для всех запросов блокировки, при которых потребовалось ожидание. Этот счетчик показывает, сколько в среднем процессам пользователей приходится проводить в очереди, чтобы наложить на ресурс блокировку. Максимально допустимое значение этого счетчика полностью зависит от вашей задачи, какое-то среднее значение для всех приложений здесь определить сложно. Слишком высокое значение этого счетчика может означать проблемы с блокировками в вашей базе данных.
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Locks(_Total)Average Wait Time (ms)",30]
      Пример триггера: {НАЗВАНИЕ_УЗЛА:perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Locks(_Total)Average Wait Time (ms)",30].last()}>=500, уровень-информация
   2. Lock Wait Time (ms)
      Суммарное время ожидания блокировок (в миллисекундах) за последнюю секунду.
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Locks(_Total)Lock Wait Time (ms)",30]
   3. Lock Waits/sec
      Количество случаев за последнюю секунду, когда потоку приходилось ожидать в связи с запросом блокировки.
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Locks(_Total)Lock Wait Time (ms)",30]
   4. Lock Timeouts/sec
      Количество повторений, когда не удается получить блокировку путем циклического обращения. Значение параметра конфигурирования SQL Server spin counter определяет количество «оборотов» потока (spins), прежде чем истечет время тайм-аута и поток перейдет в неактивное состояние.
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Lock Timeouts/sec",30]
      Пример триггера: {НАЗВАНИЕ_УЗЛА:perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Locks(_Total)Lock Timeouts/sec",30].last()}>1000, уровень-информация
   5. Lock Requests/sec
      Количество запросов в секунду указанного типа блокировки.
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Lock Requests/sec",30]
      Пример триггера: {НАЗВАНИЕ_УЗЛА:perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Locks(_Total)Lock Requests/sec",30].last()}>500000, уровень-информация
   6. Lock Number of Deadlocks/sec
      Количество запросов блокировки в секунду, приводящих к взаимоблокировке. Наличие взаимоблокировок свидетельствует о неправильно построенных запросах, которые блокируют совместные ресурсы.
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Number of Deadlocks/sec",30]
      Пример триггера: {НАЗВАНИЕ_УЗЛА:perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Locks(_Total)Number of Deadlocks/sec",30].last()}>1, уровень-высокий

9. MS SQL Server: Memory Manager
   Объект Memory Manager (Диспетчер памяти) в Microsoft SQL Server обеспечивает счетчики для контроля использования памяти всего сервера. Контроль над использованием памяти всего сервера для оценки действий пользователя и использования ресурсов может помочь идентифицировать нехватку производительности. Контроль над памятью, используемый экземпляром SQL Server, может помочь определить:
   — существуют ли нехватки в недостаточной физической памяти для хранения в кэше часто используемых данных. Если памяти недостаточно, SQL Server должен получить данные с диска;
   — может ли производительность запроса улучшиться, если будет добавлена память или увеличится объем доступной памяти для кэширования данных или внутренних структур SQL Server.
   1. Memory Grants Outstending
      Указывает общее число процессов, успешно получивших память рабочей области. При стабильном падении показателя, необходимо увеличить ОЗУ.
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Memory ManagerMemory Grants Outstanding",30]
   2. Memory Grants Pending
      Указывает общее число процессов, ожидающих предоставления памяти рабочей памяти. При стабильном росте показателя, необходимо увеличить ОЗУ.
      Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:Memory ManagerMemory Grants Pending",30]

10. MS SQL Server: Statistics
    Объект Statistics (Статистика) в Microsoft SQL Server обеспечивает работу счетчиков для наблюдения компиляции и типов запросов, отправляемых экземпляру SQL Server. Наблюдение за числом компиляций и повторных компиляций запросов и числа пакетов, полученных экземпляром SQL Server, дает представление о том, как быстро SQL Server выполняет запросы пользователей и насколько эффективно их обрабатывает оптимизатор запросов.
    1. Butch Requests/sec
       Число пакетов команд Transact-SQL, полученных за секунду. На эту статистику влияют любые ограничения (ввод-вывод, число пользователей, размер кэша, сложность запросов и т. д.). Высокое число запросов пакетов свидетельствует о высокой пропускной способности.
       Zabbix: perf_counter["MSSQL$НАЗВАНИЕ_ЭКЗЕМПЛЯРА:SQL StatisticsBatch Requests/sec",30]

Помимо всего выше перечисленного также можно настроить и другие элементы данных (а также и создать триггеры по ним с последующим оповещением).Например:
1) размер свободного места на диске
2) размеры БД-файлов данных и журнала лога
и т. д.
Однако, все эти показатели не показывают проблему именно запросов в реальном времени.
Для этого необходимо создавать свои специальные счетчики.
Из-за соображений конфиденциальности, не буду приводить примеры таких счетчиков. Тем более, что они настраиваются уникально для каждой системы. Но отмечу, что для таких систем как 1С, NAV и CRM специализированные счетчики создать можно совместно с соответствующими разработчиками.
Приведу пример создания обобщенного показателя, который показывает сколько запросов выполняется и сколько запросов ожидают выполнения (приостановлены или заблокированы) на каждый момент времени.
Для этого необходимо создать хранимую процедуру:

USE [ИМЯ_БАЗЫ_ДАННЫХ]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [nav].[ZabbixGetCountRequestStatus]
	@Status nvarchar(255)
AS
BEGIN
	/*
		возвращает кол-во запросов с заданным статусом
	*/
	SET NOCOUNT ON;

	select count(*) as [Count]
	from sys.dm_exec_requests ER with(readuncommitted)
	where [status]=@Status
END

Далее необходимо зайти в папку, где находится Zabbix (zabbixconfuserparams.d) и создать 2 файла с расширением ps1 (PowerShell) и написать в каждом из них следующие коды:

$SQLServer = "НАЗВАНИЕ_ЭКЗЕМПЛЯРА";
$uid = "ЛОГИН"; 
$pwd = "ПАРОЛЬ";
$Status="running";

$connectionString = "Server = $SQLServer; Database=НАЗВАНИЕ_БД; Integrated Security = False; User ID = $uid; Password = $pwd;";

$connection = New-Object System.Data.SqlClient.SqlConnection;
$connection.ConnectionString = $connectionString;

#Создаем запрос непосредственно к MSSQL / Create a request directly to MSSQL
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure;  
$SqlCmd.CommandText = "nav.ZabbixGetCountRequestStatus";
$SqlCmd.Connection = $Connection;

$paramStatus=$SqlCmd.Parameters.Add("@Status" , [System.Data.SqlDbType]::VarChar);
$paramStatus.Value = $Status;

$connection.Open();
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet) > $null;
$connection.Close();

$result = $DataSet.Tables[0].Rows[0]["Count"];

write-host $result;

$SQLServer = "НАЗВАНИЕ_ЭКЗЕМПЛЯРА";
$uid = "ЛОГИН"; 
$pwd = "ПАРОЛЬ";
$Status="suspended";

$connectionString = "Server = $SQLServer; Database=НАЗВАНИЕ_БД; Integrated Security = False; User ID = $uid; Password = $pwd;";

$connection = New-Object System.Data.SqlClient.SqlConnection;
$connection.ConnectionString = $connectionString;

#Создаем запрос непосредственно к MSSQL / Create a request directly to MSSQL
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand;
$SqlCmd.CommandType = [System.Data.CommandType]::StoredProcedure;  
$SqlCmd.CommandText = "nav.ZabbixGetCountRequestStatus";
$SqlCmd.Connection = $Connection;

$paramStatus=$SqlCmd.Parameters.Add("@Status" , [System.Data.SqlDbType]::VarChar);
$paramStatus.Value = $Status;

$connection.Open();
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter;
$SqlAdapter.SelectCommand = $SqlCmd;
$DataSet = New-Object System.Data.DataSet;
$SqlAdapter.Fill($DataSet) > $null;
$connection.Close();

$result = $DataSet.Tables[0].Rows[0]["Count"];

write-host $result;

Теперь необходимо создать файл с пользовательскими параметрами и расширением .conf (или добавить строчки в существующий такой пользовательский файл, если он был создан ранее) и вставить следующие строки:
UserParameter=НАЗВАНИЕ_ПАРАМЕТРА_КОЛИЧЕСТВО_ВЫПОЛНЯЕМЫХ_ЗАПРОСОВ,powershell -NoProfile -ExecutionPolicy Bypass -File ПОЛНЫЙ_ПУТЬzabbixconfuserparams.dНАЗВАНИЕ_ФАЙЛА_ДЛЯ_ВЫПОЛНЯЕМЫХ_ЗАПРОСОВ.ps1
UserParameter=НАЗВАНИЕ_ПАРАМЕТРА_КОЛИЧЕСТВО_ОЖИДАЮЩИХ_ЗАПРОСОВ,powershell -NoProfile -ExecutionPolicy Bypass -File ПОЛНЫЙ_ПУТЬzabbixconfuserparams.dНАЗВАНИЕ_ФАЙЛА_ДЛЯ_ОЖИДАЮЩИХ_ЗАПРОСОВ.ps1
После этого сохраняем файл .conf и перезапускаем агент Zabbix.
После этого добавляем в Zabbix новых два элемента (в данном случае названия и ключ совпадают):
НАЗВАНИЕ_ПАРАМЕТРА_КОЛИЧЕСТВО_ВЫПОЛНЯЕМЫХ_ЗАПРОСОВ
НАЗВАНИЕ_ПАРАМЕТРА_КОЛИЧЕСТВО_ОЖИДАЮЩИХ_ЗАПРОСОВ
Теперь можно создавать графики и триггеры на созданные пользовательские элементы данных.

Если кол-во ожидающих запросов резко возрастает, то следующим запросом можно вывести все выполняющиеся и ожидающие запросы в данный момент времени с детализацией от куда и под каким логином выполняется запрос, текст и план запроса, а также прочие детали:

select ER.[session_id]
      ,ER.[request_id]
      ,ER.[start_time]
      ,ER.[status]
      ,ER.[command]
      ,ER.[sql_handle]
      ,ER.[statement_start_offset]
      ,ER.[statement_end_offset]
      ,ER.[plan_handle]
      ,coalesce(ER.[database_id], ES.[database_id]) as [database_id]
	  ,DB_Name(coalesce(ER.[database_id], ES.[database_id])) as [DBName]
      ,(select top(1) text from sys.dm_exec_sql_text(ER.[sql_handle])) as [TSQL]
	  ,(select top(1) [query_plan] from sys.dm_exec_query_plan(ER.[plan_handle])) as [QueryPlan]
      ,ER.[user_id]
      ,ER.[connection_id]
      ,ER.[blocking_session_id]
      ,ER.[wait_type]
      ,ER.[wait_time]
      ,ER.[last_wait_type]
      ,ER.[wait_resource]
      ,ER.[open_transaction_count]
      ,ER.[open_resultset_count]
      ,ER.[transaction_id]
      ,ER.[context_info]
      ,ER.[percent_complete]
      ,ER.[estimated_completion_time]
      ,ER.[cpu_time]
      ,ER.[total_elapsed_time]
      ,ER.[scheduler_id]
      ,ER.[task_address]
      ,ER.[reads]
      ,ER.[writes]
      ,ER.[logical_reads]
      ,ER.[text_size]
      ,ER.[language]
      ,ER.[date_format]
      ,ER.[date_first]
      ,ER.[quoted_identifier]
      ,ER.[arithabort]
      ,ER.[ansi_null_dflt_on]
      ,ER.[ansi_defaults]
      ,ER.[ansi_warnings]
      ,ER.[ansi_padding]
      ,ER.[ansi_nulls]
      ,ER.[concat_null_yields_null]
      ,ER.[transaction_isolation_level]
      ,ER.[lock_timeout]
      ,ER.[deadlock_priority]
      ,ER.[row_count]
      ,ER.[prev_error]
      ,ER.[nest_level]
      ,ER.[granted_query_memory]
      ,ER.[executing_managed_code]
      ,ER.[group_id]
      ,ER.[query_hash]
      ,ER.[query_plan_hash]
	  ,EC.[most_recent_session_id]
      ,EC.[connect_time]
      ,EC.[net_transport]
      ,EC.[protocol_type]
      ,EC.[protocol_version]
      ,EC.[endpoint_id]
      ,EC.[encrypt_option]
      ,EC.[auth_scheme]
      ,EC.[node_affinity]
      ,EC.[num_reads]
      ,EC.[num_writes]
      ,EC.[last_read]
      ,EC.[last_write]
      ,EC.[net_packet_size]
      ,EC.[client_net_address]
      ,EC.[client_tcp_port]
      ,EC.[local_net_address]
      ,EC.[local_tcp_port]
      ,EC.[parent_connection_id]
      ,EC.[most_recent_sql_handle]
	  ,ES.[login_time]
	  ,ES.[host_name]
	  ,ES.[program_name]
	  ,ES.[host_process_id]
	  ,ES.[client_version]
	  ,ES.[client_interface_name]
	  ,ES.[security_id]
	  ,ES.[login_name]
	  ,ES.[nt_domain]
	  ,ES.[nt_user_name]
	  ,ES.[memory_usage]
	  ,ES.[total_scheduled_time]
	  ,ES.[last_request_start_time]
	  ,ES.[last_request_end_time]
	  ,ES.[is_user_process]
	  ,ES.[original_security_id]
	  ,ES.[original_login_name]
	  ,ES.[last_successful_logon]
	  ,ES.[last_unsuccessful_logon]
	  ,ES.[unsuccessful_logons]
	  ,ES.[authenticating_database_id]
from sys.dm_exec_requests ER with(readuncommitted)
left join sys.dm_exec_sessions ES with(readuncommitted)
on ES.session_id = ER.session_id 
left join sys.dm_exec_connections EC  with(readuncommitted)
on EC.session_id = ER.session_id
where ER.status in ('suspended', 'running')

Также напомню, что по собранным статистикам можно получить самые тяжелые запросы:

/*
creation_time - Время, когда запрос был скомпилирован. Поскольку при старте сервера кэш пустой, данное время всегда больше либо равно моменту запуска сервиса. Если время, указанное в этом столбце позже, чем предполагаемое (первое использование процедуры), это говорит о том, что запрос по тем или иным причинам был рекомпилирован.
last_execution_time - Момент фактического последнего выполнения запроса.
execution_count - Сколько раз запрос был выполнен с момента компиляции
Количество выполнений позволяет найти ошибки в алгоритмах - часто в наиболее выполняемых запросах оказываются те, которые находятся внутри каких-либо циклов однако могут быть выполнены перед самим циклом один раз. Например, получение каких-либо параметров из базы данных, не меняющихся внутри цикла.
CPU - Суммарное время использования процессора в миллисекундах. Если запрос обрабатывается параллельно, то это время может превысить общее время выполнения запроса, поскольку суммируется время использования запроса каждым ядром. Во время использования процессора включается только фактическая нагрузка на ядра, в нее не входят ожидания каких-либо ресурсов.
Очевидно, что данный показатель позволяет выявлять запросы, наиболее сильно загружающие процессор.
AvgCPUTime - Средняя загрузка процессора на один запрос. 
TotDuration - Общее время выполнения запроса, в миллисекундах.
Данный параметр может быть использован для поиска тех запросов, которые, независимо от причины выполняются "наиболее долго". Если общее время выполнения запроса существенно ниже времени CPU (с поправкой на параллелизм) - это говорит о том, что при выполнения запроса были ожидания каких-либо ресурсов. В большинстве случаев это связано с дисковой активностью или блокировками, но также это может быть сетевой интерфейс или другой ресурс. 
Полный список типов ожиданий можно посмотреть в описании представления sys.dm_os_wait_stats.
AvgDur - Среднее время выполнения запроса в миллисекундах.
Reads - Общее количество чтений.
Это пожалуй лучший агрегатный показатель, позволяющий выявить наиболее нагружающие сервер запросы.
Логическое чтение - это разовое обращение к странице данных, физические чтения не учитываются.
В рамках выполнения одного запроса, могут происходить неоднократные обращения к одной и той же странице.
Чем больше обращений к страницам, тем больше требуется дисковых чтений, памяти и, если речь идет о повторных обращениях, большее время требуется удерживать страницы в памяти.
Writes - Общее количество изменений страниц данных.
Характеризует то, как запрос "нагружает" дисковую систему операциями записи.
Следует помнить, что этот показатель может быть больше 0 не только у тех запросов, которые явно меняют данные, но также и у тех, которые сохраняют промежуточные данные в tempdb.
AggIO - Общее количество логических операций ввода-вывода (суммарно)
Как правило, количество логических чтений на порядки превышает количество операций записи, поэтому этот показатель сам по себе для анализа применим в редких случаях.
AvgIO - Среднее количество логических дисковых операций на одно выполнение запроса.
Значение данного показателя можно анализировать из следующих соображений:
Одна страница данных - это 8192 байта. Можно получить среднее количество байт данных, "обрабатываемых" данным запросом. Если этот объем превышает реальное количество данных, которые обрабатывает запрос (суммарный объем данных в используемых в запросе таблицах), это говорит о том, что был выбран заведомо плохой план выполнения и требуется заняться оптимизацией данного запроса.
Я встречал случай, когда один запрос делал количество обращений, эквивалентных объему в 5Тб, при этом общий объем данных в это БД был 300Гб, а объем данных в таблицах, задействованных в запросе не превышал 10Гб.
В общем можно описать одну причину такого поведения сервера - вместо использования индекса сервер предпочитает сканировать таблицу или наоборот.
Если объем логических чтений в разы превосходит общие объем данных, то это вызвано повторным обращениям к одним и тем же страницам данных. Помимо того, что в одном запросе таблица может быть использована несколько раз, к одним и тем же страницам сервер обращается например в случаях, когда используется индекс и по результатам поиска по нему, найденные некоторые строки данных лежат на одной и той же странице. Конечно, в таком случае предпочтительным могло бы быть сканирование таблицы - в этом случае сервер обращался бы к каждой странице данных только один раз. Однако этому часто мешают... попытки оптимизации запросов, когда разработчик явно указывает, какой индекс или тип соединения должен быть использован.
Обратный случай - вместо использования индекса было выбрано сканирование таблицы. Как правило, это связано с тем, что статистика устарела и требуется её обновление. Однако и в этом случае причиной неудачно выбранного плана вполне могут оказаться подсказки оптимизатору запросов.
query_text - Текст самого запроса
database_name - Имя базы данных, в находится объект, содержащий запрос. NULL для системных процедур
object_name - Имя объекта (процедуры или функции), содержащего запрос.
*/
with s as (
	select  creation_time,
			last_execution_time,
			execution_count,
			total_worker_time/1000 as CPU,
			convert(money, (total_worker_time))/(execution_count*1000)as [AvgCPUTime],
			qs.total_elapsed_time/1000 as TotDuration,
			convert(money, (qs.total_elapsed_time))/(execution_count*1000)as [AvgDur],
			total_logical_reads as [Reads],
			total_logical_writes as [Writes],
			total_logical_reads+total_logical_writes as [AggIO],
			convert(money, (total_logical_reads+total_logical_writes)/(execution_count + 0.0))as [AvgIO],
			[sql_handle],
			plan_handle,
			statement_start_offset,
			statement_end_offset
	from sys.dm_exec_query_stats as qs with(readuncommitted)
	where convert(money, (qs.total_elapsed_time))/(execution_count*1000)>=100 --выполнялся запрос не менее 100 мс
)
select
	s.creation_time,
	s.last_execution_time,
	s.execution_count,
	s.CPU,
	s.[AvgCPUTime],
	s.TotDuration,
	s.[AvgDur],
	s.[Reads],
	s.[Writes],
	s.[AggIO],
	s.[AvgIO],
	--st.text as query_text,
	case 
		when sql_handle IS NULL then ' '
		else(substring(st.text,(s.statement_start_offset+2)/2,(
			case
				when s.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2      
				else s.statement_end_offset    
			end - s.statement_start_offset)/2  ))
	end as query_text,
	db_name(st.dbid) as database_name,
	object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as [object_name],
	sp.[query_plan],
	s.[sql_handle],
	s.plan_handle
from s
cross apply sys.dm_exec_sql_text(s.[sql_handle]) as st
cross apply sys.dm_exec_query_plan(s.[plan_handle]) as sp

Результат

В данной статье был рассмотрен пример счетчиков производительности (элементы данных) в Zabbix. Данный подход позволяет уведомлять администраторов о разных проблемах в реальном времени или через какое-то определенное время. Таким образом, данный подход позволяет минимизировать в будущем наступления критической проблемы и остановки работы СУБД и сервера, что в свою очередь защищает производство от остановки рабочих процессов.
Предыдущая статья: Регламентные работы с базой данных информационной системы 24x7 в MS SQL Server

Источники:

» Zabbix 3.4
» Счетчики производительности
» Центр производительности для базы данных Azure SQL и SQL Server Database Engine
» Стиль жизни SQL
» SQLSkills
» TechNet Microsoft
» Анализируем использование памяти
» Анализ производительности
» Документация по SQL
» Заметки о Windows

Автор: jobgemws

Источник