Вопрос или проблема
Я использую sp_rename как конечный шаг в процессе своей ETL хранимой процедуры, чтобы сменить имена промежуточных и финальных таблиц.
Поскольку эта операция требует блокировок SCH M, я столкнулся со следующим сценарием.
Предположим, что выполняется длительный запрос SELECT на FINALTABLE, и в этот момент вызывается хранимая процедура, которая достигла оператора sp_rename, тогда любые другие запросы SELECT на FINALTABLE блокируются.
Я понимаю, что это происходит потому, что запросы select (SCH S) становятся в очередь за sp_rename (SCH M).
Я читаю о способах решения этой проблемы, и решение, похоже, заключается в использовании переключения партиций с синтаксисом ожидания с низким приоритетом, что помогает завершитьongoing SELECT или запрос ALTER TABLE после определенного времени. Однако первым шагом к переключению партиций является усечение финальной таблицы, которое также требует блокировку SCH M на финальной таблице, и нет возможности использовать ожидание с низким приоритетом для оператора truncate. Таким образом, во время выполнения длительного запроса select, и когда вызывается SP, что приводит к выполнению оператора truncate, в этот момент truncat блокируется, так как нужны блокировки SCH M, и, следовательно, блокирует все последующие запросы select от других сеансов.
Таким образом, я пытаюсь понять, как именно переключение партиций лучше по сравнению с использованием sp_rename в этом аспекте, чтобы предотвратить описанный выше сценарий.
Ответ или решение
Вопрос о том, как правильно справляться с блокировками, возникающими при использовании команд sp_rename и переключения по разделам в ETL-процессах, является актуальным для многих специалистов в области информационных технологий. Оба подхода имеют свои плюсы и минусы, и их выбор во многом зависит от специфики вашей работы с базами данных. Давайте более детально рассмотрим каждую из этих стратегий.
Проблема блокировки с sp_rename
При использовании sp_rename для окончательной переименования временных и финальных таблиц мы сталкиваемся с тем, что это действие требует блокировки типа Schema Modification (SCH M). Как следствие, любые запросы на чтение (SCH S), выполняющиеся одновременно, будут блокироваться до тех пор, пока sp_rename не завершится. Это может быть катастрофическим для производительности, особенно если SELECT-запросы занимают много времени для выполнения. В результате, это приводит к снижению доступности данных для других пользователей и может вызвать задержки в бизнес-процессах.
Альтернативный подход: переключение разделов
Переключение по разделам, в свою очередь, предлагает несколько методов, позволяющих уменьшить влияние блокировок. Этот подход позволяет переключать данные между таблицами, которые могут находиться в разных состояниях (например, временная таблица и финальная таблица), без необходимости блокировать другие операции на чтение. Основные шаги, связанные с переключением по разделам, включают:
- Добавление нового раздела: Создайте новый раздел для финальной таблицы с новыми данными.
- Переключение по разделам: Используйте команду
ALTER TABLE ... SWITCHдля быстрого переключения новых и старых данных.
Однако, как вы правильно заметили, первые шаги этого процесса также могут содержать операции, такие как обрезка (TRUNCATE), которая требует блокировки типа SCH M. Это означает, что если запрос долго выполняется на финальной таблице и во время этого запускается процесс переключения разделов, операция обрезки также может быть заблокирована. Такой сценарий затрудняет недопущение блокировок.
Почему переключение разделов все же предпочтительнее
-
Управление приоритетами блокировок: В отличие от
sp_rename, операции переключения по разделам могут быть более гибкими в управлении приоритетами блокировок. Вы можете использовать синтаксисWAIT_AT_LOW_PRIORITY, который позволит настроить ожидание блокировки и при необходимости прервать её. Это означает, что если возникают долгие запросы на чтение, вы можете настроить переключение таким образом, чтобы оно ожидало освобождения ресурсов или было автоматически отменено. -
Изоляция операций: Переключение по разделам позволяет изолировать операции записи и чтения. Это может означать меньшее влияние на производительность во время ETL-процессов, поскольку вы уменьшаете вероятность блокировок между записями и чтением данных.
-
Гибкость управления партиями: При использовании разделов можно настроить их так, чтобы итоговые данные отображались на более высоком уровне производительности, например, путем использования методов, при которых старые данные удаляются по частям, вместо обрезки всей таблицы сразу.
Заключение
Выбор между sp_rename и переключением по разделам зависит от ваших требований и контекста использования. Если ваш ETL-процесс предполагает частое изменение структуры таблиц и вы хотите минимизировать блокировки, переключение по разделам, скорее всего, будет более подходящим решением. Однако вам необходимо внимательно управлять процессом обрезки, чтобы избежать блокировок.
Правильное планирование и реализация управления блокировками станут залогом повысить производительность вашей базы данных и улучшат доступность данных для пользователей.