Блокировки

Если в системе установлен бесконечный период ожидания завершения транзакции (а это задано по умолчанию), то при возникновении deadlock для двух транзакций вполне возможно, что, ожидая освобождения заблокированных ресурсов, в тупике окажутся и новые транзакции.

Чтобы избежать подобных проблем, в среде MS SQL Server реализован специальный механизм разрешения конфликтов тупикового блокирования. Для этих целей сервер снимает одну из блокировок, вызвавших конфликт, и откатывает инициализировавшую ее транзакцию. При выборе блокировки, которой необходимо пожертвовать, сервер исходит из соображений минимальной стоимости.

Паттерны

• Deadlock
• Lock Escalation
  • через Extended Events
• Online
  • Моментальный снимок ожидающих задач
  • wait by CPU
• Исторически
  • SQL Wait Statistics скрипт
  • IO waits by query, database
• Локализация причин
  • System view DMV sql

Виды ожиданий Wait statistics

• Виды ожиданий
  • PAGEIOLATCH_SH и PAGEIOLATCH_EX
    • см нагружающие запросы по вводу/выводу
  • CXPACKET - Означает параллелизм, но не обязательно в нем проблема
    • Advanced
    • Если у нас транзакционная система: имеет смысл установить Max Degree Parallelism = 1
    • Хранилища и витрины данных: установить Max Degree Parallelism = 0 или явное количество CPU
    • Смешанные: установить Max Degree Parallelism = 1 , a y запросов требующих параллелизма установить hint MAXDOP=0
    • Параллелизм Maxdop и cost Threshold
  • LATCH_XX
  • LCK_M_XX - блокировка
  • LCK_M_IX - блокировка
  • PREEMPTIVE_XE_DISPATCHER - связано с фоновыми потоками расширенных событий Extended Events
  • ASYNC_IO_COMPLETION - Возникает, когда задача ожидает завершения ввода-вывода
  • ASYNC_NETWORK_IO - SQL Server ждет, пока клиент закончит получать данные