Отключает ли блокировка эскалации больше нагрузки на данные mdf?

Question

У нас есть большое многоцелевое приложение с блоками OBJECT и PAGE в нескольких таблицах. Мы не можем пересмотреть проект, но нам нужно уменьшить количество блоков, которые влияют на производительность на клиентских компьютерах. Я рассматривал возможность отключения эскалаций блокировки на одной таблице, но мне нужно знать, какое давление это окажет на другие ресурсы. Дисковый ввод-вывод уже перегружен. Будут ли дополнительные индивидуальные блокировки требовать больше ввода-вывода, чем автоматические блокировки таблицы? Повлияет ли это на наши системные базы данных больше, чем на нашу базу данных? Мы не делаем полные обновления таблицы / чтения. Каждый запрос будет касаться только очень маленькой части таблицы.

Answer 1

У нас есть большое многоцелевое приложение с блоками OBJECT и PAGE в нескольких таблицах.

...

Дисковый ввод-вывод уже перегружен. Будут ли дополнительные индивидуальные блокировки требовать больше ввода-вывода, чем автоматические блокировки таблицы?

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

Блокировка эскалации идет from rows to table или же from pages to table (Я исключил раздел, так как это не ваш случай), поэтому, если теперь у вас есть блокировки страниц, это НЕ повышение уровня блокировки.

Гранулярность блокировки выбирается сервером, если вы не используете подсказки (rowlock, paglock) и если он выберет page locks Там нет эскалации. Если он удаляет все page locks и заменяет их table lock, это значит lock escalation произошло.

Во-вторых, неправильно то, что ты думаешь, что замки связаны с IO, Это неправда. Блокировки хранятся в памяти и не имеют ничего общего с чтениями. Вы можете проверить эту статью, чтобы увидеть, как CPU usage а также query duration увеличиваются, когда блокировки становятся более детализированными: почему подсказки ROWLOCK могут замедлить выполнение запросов и ухудшить блокировку в SQL Server.

Вы должны понимать, что вызывает эскалацию блокировки.

Блокировка порогов эскалации

Повышение блокировки запускается, когда повышение блокировки не отключено в таблице с помощью ALTER TABLE SET LOCK_ESCALATION вариант, и когда существует одно из следующих условий:

• Один оператор Transact-SQL получает по меньшей мере 5000 блокировок для одной неразделенной таблицы или индекса.
• Один оператор Transact-SQL получает как минимум 5000 блокировок для одного раздела многораздельной таблицы, и ALTER TABLE SET LOCK_ESCALATION опция установлена ​​на АВТО.
• Количество блокировок в экземпляре компонента Database Engine превышает пороговые значения памяти или конфигурации.

Повышение блокировки (компонент Database Engine)

Поэтому, если вы достигаете 5000 блокировок на порог оператора, вам следует разделить свои операции на меньшие партии.

А если у вас нехватка памяти, отключение эскалации блокировки сделает вашу ситуацию еще хуже.

ОБНОВИТЬ

Я нашел это описание блокировок в книге Microsoft SQL Server 2012 Internals (Справочник разработчика) Калена Делани (Автор), Боба Бошемина (Автор), Конора Каннингема (Автор), Джонатана Кехайяса (Автор), Пола С. Рэндал (Автор), Бенджамин Неварез (Автор)

Блокировки не являются структурами на диске. Вы не найдете поле блокировки непосредственно на странице данных или в заголовке таблицы, а метаданные, которые отслеживают блокировки, никогда не записываются на диск. Блокировки являются структурами внутренней памяти: они занимают часть памяти, используемой для SQL Server. Блокировка идентифицируется ресурсом блокировки, который представляет собой описание заблокированного ресурса (строки, ключа индекса, страницы или таблицы). Чтобы отслеживать базу данных, тип блокировки и информацию, описывающую заблокированный ресурс, для каждой блокировки требуется 64 байта памяти в 32-битной системе и 128 байтов памяти в 64-битной системе. Эта 64-байтовая или 128-байтовая структура называется блоком блокировки.... Менеджер блокировки поддерживает хэш-таблицу блокировки. Ресурсы блокировки, содержащиеся в блоке блокировки, хэшируются, чтобы определить целевой хэш-слот в хэш-таблице. Все блокирующие блоки, которые хэшируются в одном и том же слоте, объединяются в одну запись в хэш-таблице. Каждый блок блокировки содержит 15-байтовое поле, которое описывает заблокированный ресурс. Блок блокировки также содержит указатели на списки блоков владельца блокировки. Каждое из трех состояний имеет отдельный список владельцев замков.

Надеюсь, поможет.