SQL Server 2014: ONLINE операции над отдельными секциями секционированных объектов

by Alexey Knyazev 10. июля 2013 00:03

SQL Server 2014

Продолжу серию заметок о новых возможностях 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

Tags: , ,

SQL Server

Комментарии (1) -

Alexey Knyazev
Alexey Knyazev Russia
10.07.2013 2:42:19 #

msdn.microsoft.com/.../ms188388(v=sql.120).aspx

Reply

Добавить комментарий

  Country flag

biuquote
  • Комментарий
  • Предпросмотр
Loading