SQL Server 2016: Новые ON-Line операции

by Alexey Knyazev 2. августа 2015 03:10

С выходом каждой новой версии 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

Tags: , ,

SQL Server

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

  Country flag

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