Продолжу серию заметок о новых возможностях SQL Server 2014. И сегодня поговорим об ещё одной приятной конструкции - ONLINE перестроение отдельных секций секционированных объектов (таблиц или индексов).
Напомню, что некоторые редакции SQL Server (Enterprise, Evaluation, Developer) позволяют выполнять ряд операций в режиме ONLINE. Основное преимущество этого режима - это то, что в момент создания/изменения индекса мы можем продолжать работать с данными, в том числе вносить изменения в эти данные. Происходит это за счёт того, что блокировка схемы (Sch-M) не удерживается на всём этапе создания/изменения. С более полным списком возможностей, поддерживаемых различными выпусками, можно ознакомиться по ссылке: http://msdn.microsoft.com/ru-ru/library/cc645993.aspx
Действительно, возможность очень полезная, особенно, если у вас доступность данных - это одно из ключевых требований к системе. Но до версии SQL Server 2014 у нас возникает ряд ограничений, если мы работаем с секционированными объектами. Основное ограничение в том, что мы не можем проводить ONLINE операции над отдельно взятой секцией, а только над всем объектом целиком.
При попытке выполнить инструкцию:
alter table [MyTable] rebuild partition = 1 with (online = on)
мы получим исключение:
'online' is not a recognized ALTER INDEX REBUILD PARTITION option
Без указания режима ONLINE для секции мы получаем блокировку Sch-M на всю таблицу, что, согласитесь не приятно. При этом мы можем изменить режим эскалации (LOCK_ESCALATION) для нашей таблицы в надежде избавиться от укрупнения блокировки до уровня таблицы, т.к. в BOL сказано, что по умолчанию укрупнение стоит = TABLE, а для секционированной таблицы мы можем задать режим AUTO:
В секционированных таблицах допускается укрупнение блокировки до секций. После укрупнения блокировки до уровня секции дальнейшее укрупнение до гранулярности TABLE выполняться не будет.
Но для операций REBUILD это правило не действует и мы все-равно получаем блокировку уровня таблицы. И в том же BOL есть об этом упоминание:
Перестроение секционированного индекса нельзя выполнять в режиме в сети(ONLINE). Во время этой операции вся таблица блокируется.
А теперь представьте, что нам необходимо провести сжатие данных (data_compression) лишь в одной из секций, а при этой операции мы заблокируем всю таблицу. На эту тему даже есть фидбэк на официальном сайте: http://connect.microsoft.com/SQLServer/feedback/details/709976/table-lock-during-partition-compression.
И выдержка из ответа:
We are currently working on supporting Online Index Rebuild for a single partition and hopefully this will be available in our next release.
И это случилось! Поддержка ONLINE операций на уровне одной секции появилась в SQL Server 2014.
Ниже скрипт для демонстрации:
create partition function pf_dt ( datetime )
as range right for values ( '20130701' );
go
create partition scheme ps_dt
as partition pf_dt all to ( [primary] );
go
create table dbo.test_table ( dt datetime, val varchar(50) ) on ps_dt (dt);
go
declare @start_dt datetime = '20130614';
with
cte1 as ( select t1.* from ( values(1),(1) ) t1(i) )
, cte2 as ( select t2.* from cte1 t1 cross join cte1 t2 )
, cte3 as ( select t3.* from cte2 t2 cross join cte2 t3 )
, cte4 as ( select t4.* from cte3 t3 cross join cte3 t4 )
, cte5 as ( select t5.* from cte4 t4 cross join cte4 t5 )
insert into dbo.test_table
select dateadd( mi, row_number() over ( order by (select null) ), @start_dt ), replicate( 'A', 50 ) from cte5;
go
alter table dbo.test_table
rebuild partition = 2 with ( online = on );
go
Но разработчики пошли дальше и добавили ещё несколько полезных параметров для ONLINE операций, а именно параметр low_priority_lock_wait, который позволяет задать интервал ожидания, в случаи блокировки ресурса над которым мы выполняем операцию:
MAX_DURATION = time [MINUTES]
и какое решение необходимо принять по истечению этого интервала:
ABOUT_AFTER_WAIT = [NONE | SELF | BLOCKERS]
- NONE - никаких действий (равносильно текущему поведению в SQL Server 2012)
- SELF - мы прерываем нашу операцию
- BLOCKERS - мы прерываем работу всех процессов, которые нам "мешают"
Для демонстрации в одной сессии выполним инструкцию:
begin tran
select * from dbo.test_table with (holdlock)
А в другой запустим инструкцию:
alter table dbo.test_table
rebuild partition = 1
with (online = on ( wait_at_low_priority ( max_duration = 1 minutes, abort_after_wait = self ) ) );
go
По истечению одной минуты мы увидим исключение:
Lock request time out period exceeded.
А теперь не останавливая первую транзакцию выполним:
alter table dbo.test_table
rebuild partition = 1
with (online = on ( wait_at_low_priority ( max_duration = 1 minutes, abort_after_wait = blockers ) ) );
go
По истечению всё тойже минуты мы увидем, что операция выполнена успешно. При этом перейдём в окно первой транзакции и попробуем выполнить любой оператор. Мы получим исключение:
Ошибка на транспортном уровне при отправке запроса серверу. (provider: Shared Memory Provider, error: 0 - С обоих концов канала отсутствуют процессы.)
Т.е. наша транзакция была успешно "прибита".
Но в заключении я хочу сказать, что мы можем выбрать поведение в случаи блокировок не только для ONLINE операций, но и для операций переключения секций (SWITCH). Ниже скрипт для самостоятельных тестов:
--Таблица для переключения данных из одной из секций
create table dbo.test_table_demo ( dt datetime, val varchar(50) );
go
--Само переключение с использованием wait_at_low_priority
alter table dbo.test_table
switch partition 1 to dbo.test_table_demo
with ( wait_at_low_priority ( max_duration = 1 minutes, abort_after_wait = self ) );
go
Подписаться