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

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

Ниже скрипт для демонстрации:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
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 — мы прерываем работу всех процессов, которые нам «мешают»

Для демонстрации в одной сессии выполним инструкцию:

1
2
begin tran
select * from dbo.test_table with (holdlock)

А в другой запустим инструкцию:

1
2
3
4
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.

А теперь не останавливая первую транзакцию выполним:

1
2
3
4
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). Ниже скрипт для самостоятельных тестов:

1
2
3
4
5
6
7
8
--Таблица для переключения данных из одной из секций
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

Автор: Алексей Князев

***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***
Полезные Скрипты

Ссылка на наш канал YouTube

Обсудить тему можно в комментариях или вконтакте.

Не забывайте следить за рубрикой «Грядущие события и мероприятия»

Запись опубликована в рубрике Полезно и интересно с метками , , . Добавьте в закладки постоянную ссылку.

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

Войти с помощью: