Продолжу серию заметок о новых возможностях 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' );gocreate partition scheme ps_dtas partition pf_dt all to ( [primary] );gocreate table dbo.test_table ( dt datetime, val varchar(50) ) on ps_dt (dt);godeclare @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_tableselect dateadd( mi, row_number() over ( order by (select null) ), @start_dt ), replicate( 'A', 50 ) from cte5;goalter table dbo.test_tablerebuild 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 transelect * from dbo.test_table with (holdlock) |
А в другой запустим инструкцию:
|
1
2
3
4
|
alter table dbo.test_tablerebuild partition = 1with (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_tablerebuild partition = 1with (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
Обсудить тему можно в комментариях или вконтакте.
Не забывайте следить за рубрикой «Грядущие события и мероприятия»
