Блокирующие сеансы

Microsoft.SQLServer.2016.DBEngine.BlockedSPIDsMonitor (UnitMonitor)

Наблюдает за заблокированными сеансами экземпляра SQL. Примечание: по умолчанию этот монитор отключен. Если нужно его включить, то пользуйтесь переопределениями.

Knowledge Base article:

Сводка

Монитор заблокированных сеансов определяет, существует ли ситуация блокировки.

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

Причины

Когда блокировки достигают предела, при котором они оказывают существенное влияние на ухудшение производительности системы, это обычно происходит по одной из этих причин:

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

Сеанс (определяемый идентификатором session_id или SPID) удерживает блокировку набора ресурсов в течение длительного периода времени, прежде чем освободить их. Обычно это обусловлено следующими причинами.:
- Длительные транзакции
- Отсутствие соответствующих индексов
- Неправильное использование подсказок блокировок
- Наличие других проблем, связанных с недостаточно продуманной конструкцией приложения

Решения

Прервите сеанс в начале цепочки блокировки
Уменьшите время обработки транзакций
Создавайте правильные индексы
Используйте подсказки блокировок. См. электронную документацию по SQL Server
Используйте уровни изоляции на основе управления версиями строк
Настройка параметров SQL Server (параметры памяти, периоды времени ожидания блокировки и т. д.)
Измените пороговые значения в мониторах для конкретной или для всех баз данных.

Альтернативный вариант, если блокируемые сеансы не связаны с базой данных:

Отключите этот монитор для конкретной базы данных или для всех баз данных.

Переопределяемые параметры

Название	Описание	Значение по умолчанию
Приоритет предупреждения	Определяет приоритет предупреждения.	Высокий
Серьезность предупреждения	Определяет серьезность предупреждения.	Ошибка
Включено	Включает или отключает рабочий процесс.	Нет
Создает предупреждения	Определяет, создает ли рабочий процесс предупреждения.	Да
Интервал (в секундах)	Повторяющийся интервал времени в секундах, по истечении которого запускается рабочий процесс.	300
Число заблокированных сеансов	Максимальное допустимое количество заблокированных сеансов.	1
Время синхронизации	Время синхронизации указывается в 24-часовом формате и может быть опущено.
Время ожидания (в секундах)	Определяет время, в течение которого возможно выполнение рабочего процесса перед закрытием и пометкой как сбойный.	300
Время ожидания (мин)	Минимальная продолжительность выполнения процесса перед анализом заблокированных SPID для этого процесса.	1

Element properties:

Target

Microsoft.SQLServer.2016.DBEngine

Parent Monitor

System.Health.PerformanceState

Category

PerformanceHealth

Enabled

False

Alert Generate

True

Alert Severity

Error

Alert Priority

High

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.2016.DBEngine.BlockedSPIDs

Remotable

True

Accessibility

Public

Alert Message

MSSQL 2016: заблокированный сеанс(-ы)

Сеансы {0} в экземпляре SQL Server "{1}" на компьютере "{2}" заблокированы. Дополнительные сведения см. на вкладке "Контекст предупреждения".

RunAs

Default

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2016.DBEngine.BlockedSPIDsMonitor" Accessibility="Public" Enabled="false" Target="SQL2016Core!Microsoft.SQLServer.2016.DBEngine" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2016.DBEngine.BlockedSPIDs" ConfirmDelivery="false">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2016.DBEngine.BlockedSPIDsMonitor.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>High</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Property[@Name='BlockedSPIDList']$</AlertParameter1>

      <AlertParameter2>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.ServerRole"]/InstanceName$</AlertParameter2>

      <AlertParameter3>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</AlertParameter3>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Blocked" MonitorTypeStateID="Blocked" HealthState="Error"/>

    <OperationalState ID="NotBlocked" MonitorTypeStateID="NotBlocked" HealthState="Success"/>

  </OperationalStates>

  <Configuration>

    <ConnectionString>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBEngine"]/ConnectionString$</ConnectionString>

    <IntervalSeconds>300</IntervalSeconds>

    <SyncTime/>

    <WaitMinutes>1</WaitMinutes>

    <NumberBlockedSPIDs>1</NumberBlockedSPIDs>

    <TimeoutSeconds>300</TimeoutSeconds>

    <InstanceName>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.ServerRole"]/InstanceName$</InstanceName>

    <ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>

  </Configuration>

</UnitMonitor>