С выходом каждой новой версии SQL Server появляется всё больше операций, которые могут быть проведены ON-Line, т.е. без длительных блокировок и ожиданий. В SQL Server 2014, например, мы увидели ON-Line операции над отдельными секциями секционированных объектов.
Новая версия SQL Server 2016 так же добавит несколько операций ON-Line.
Во первых появится возможность совершать операцию TRUNCATE над отдельными секциями, а не только над всей таблицей. О преимуществах операции TRUNCATE над операцией DELETE я не буду останавливать в этой статье, просто скажу, что за счёт минимального количества операций протоколирования, операция TRUCATE выполняется в разы быстрее, чем операция DELETE. При этом мы можем указывать не только отдельную секцию, но и целый диапазон.
truncate table dbo.test_table with ( partitions ( 1, 3 to 4 ) );
go
А вторая операция - это возможность выполнять изменение колонки в таблице в режиме ON-Line
alter table dbo.test_table
alter column val varchar(50) not null
with (online = on );
go
Для демонстрации TRUNCATE над отдельными секция создадим секционированную таблицу и наполним её данными:
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
Убедимся, что в секциях появились данные:
select partition_number, rows
from sys.partitions
where object_id = object_id ( 'dbo.test_table' );
go
Теперь выполним TRUNCATE над секциями 2 и 4-5, и убедимся, что данные удалились:
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 операция изменения колонки. Создадим таблицу с большой колонкой, чтобы каждая запись занимала отдельную страницу и наполним её данными:
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 изменились страницы выполним следующий скрипт:
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
Подписаться