Автор: Alexey Knyazev
С выходом каждой новой версии SQL Server появляется всё больше операций, которые могут быть проведены ON-Line, т.е. без длительных блокировок и ожиданий. В SQL Server 2014, например, мы увидели ON-Line операции над отдельными секциями секционированных объектов.
Новая версия SQL Server 2016 так же добавит несколько операций ON-Line.
Во первых появится возможность совершать операцию TRUNCATE над отдельными секциями, а не только над всей таблицей. О преимуществах операции TRUNCATE над операцией DELETE я не буду останавливать в этой статье, просто скажу, что за счёт минимального количества операций протоколирования, операция TRUCATE выполняется в разы быстрее, чем операция DELETE. При этом мы можем указывать не только отдельную секцию, но и целый диапазон.
|
1 2 |
truncate table dbo.test_table with ( partitions ( 1, 3 to 4 ) ); go |
А вторая операция — это возможность выполнять изменение колонки в таблице в режиме ON-Line
|
1 2 3 4 |
alter table dbo.test_table alter column val varchar(50) not null with (online = on ); go |
Для демонстрации TRUNCATE над отдельными секция создадим секционированную таблицу и наполним её данными:
|
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 |
create partition function pf_dt ( datetime ) as range right for values ( '20150801', '20150802', '20150803', '20150804', '20150805' ); 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 insert into dbo.test_table select '20150801', replicate( 'A', 50 ); go 10 insert into dbo.test_table select '20150802', replicate( 'A', 50 ); go 10 insert into dbo.test_table select '20150803', replicate( 'A', 50 ); go 10 insert into dbo.test_table select '20150804', replicate( 'A', 50 ); go 10 insert into dbo.test_table select '20150805', replicate( 'A', 50 ); go 10 |
Убедимся, что в секциях появились данные:
|
1 2 3 4 |
select partition_number, rows from sys.partitions where object_id = object_id ( 'dbo.test_table' ); go |
Теперь выполним TRUNCATE над секциями 2 и 4-5, и убедимся, что данные удалились:
|
1 2 3 4 5 6 |
truncate table dbo.test_table with ( partitions ( 2, 4 to 5 ) ); go select partition_number, rows from sys.partitions where object_id = object_id ( 'dbo.test_table' ); go |
А теперь протестируем, как работает On-Line операция изменения колонки. Создадим таблицу с большой колонкой, чтобы каждая запись занимала отдельную страницу и наполним её данными:
|
1 2 3 4 5 |
create table dbo.test_table ( id int identity primary key, val varchar(8000) ); go insert into dbo.test_table select replicate( 'A', 8000 ) from sys.all_columns; go 100 |
Теперь попробуем изменить колонку val на NOT NULL, а в другом окне будем просматривать данные в таблице. Можно убедиться, что, когда мы делаем ALTER COLUMN в режиме ON-Line, то данные остаются доступны на чтение, а блокировка схемы Sch-M накладывается лишь в самом конце операции ALTER, когда происходит переключение на новые страницы с данными. При этом доступны не только сами данные, но и статистики, которые были созданы по этому полю и лишь после того, как данные колонки переключаются на новые страницы, статистика удаляется и её необходимо создать по новой. Для того, чтобы убедиться, что после ON-Line операции ALTER COLUMN изменились страницы выполним следующий скрипт:
|
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 28 29 30 31 32 33 34 35 |
drop table dbo.test_table; go create table dbo.test_table ( id int identity primary key, val varchar(8000) ); go insert into dbo.test_table select replicate( 'A', 8000 ); go select l.page_id from dbo.test_table as t outer apply sys.fn_PhysLocCracker(%%PhysLoc%%) as l; go alter table dbo.test_table alter column val varchar(8000) not null --with (online = on ); go select l.page_id from dbo.test_table as t outer apply sys.fn_PhysLocCracker(%%PhysLoc%%) as l; go ------------------------------------- drop table dbo.test_table; go create table dbo.test_table ( id int identity primary key, val varchar(8000) ); go insert into dbo.test_table select replicate( 'A', 8000 ); go select l.page_id from dbo.test_table as t outer apply sys.fn_PhysLocCracker(%%PhysLoc%%) as l; go alter table dbo.test_table alter column val varchar(8000) not null with (online = on ); go select l.page_id from dbo.test_table as t outer apply sys.fn_PhysLocCracker(%%PhysLoc%%) as l; go |
Результат ниже:
Подробнее про On-Line операцию ALTER COLUMN
*** *** *** *** *** *** *** *** *** *** *** ***
Ссылка на наш канал YouTube
Обсудить тему можно в комментариях или вконтакте.
Не забывайте смотреть рубрику «Грядущие события и мероприятия»
