Минимальное протоколирование (Bulk logged) и индексы

Автор: Alexey Knyazev

Минимальное протоколирование — это протоколирование только информации, необходимой для восстановления транзакции без поддержки восстановления на момент времени.
Эта особенность может быть очень полезна, когда необходимо сократить время на выполнение некоторых операций. Сегодня я хочу поговорить о минимальном протоколировании некоторых DDL-операции с индексом.

Минимально протоколируются следующие операции с индексами:

Операции CREATE INDEX (включая индексированные представления)
Операции ALTER INDEX REBUILD или DBCC DBREINDEX
Перестроение новой кучи DROP INDEX (если применимо)

Минимальное протоколирование выполняется более эффективно, чем полное, и снижает вероятность переполнения журнала во время выполнения операции с индексами. При этом минимальное протоколирование напрямую зависит от модели восстановления (recovery model) БД.

В следующей таблице перечислены операции с индексами и тип протоколирования, доступный для них в каждой из моделей восстановления базы данных. Эти модели восстановления поддерживаются для операций с индексами, выполняющихся как в оперативном (ONLINE), так и в автономном режиме (OFFLINE).

Операция с индексами	Полная модель (Full)	Модель с неполным протоколированием (Bulk logged)	Простая модель (Simple)
ALTER INDEX REORGANIZE	полное	полное	полное
ALTER INDEX REBUILD	полное	минимальное	минимальное
CREATE INDEX	полное	минимальное	минимальное
DBCC INDEXDEFRAG	полное	полное	полное
DBCC DBREINDEX	полное	минимальное	минимальное
DROP INDEX	Освобождение индексной страницы полностью протоколируется; создание новой кучи (при необходимости) также полностью протоколируется	Освобождение индексной страницы полностью протоколируется; создание новой кучи (при необходимости) протоколируется минимально	Освобождение индексной страницы полностью протоколируется; создание новой кучи (при необходимости) протоколируется минимально

В этой статье я покажу, как можно убедиться, что ваши операции над индексами действительно протоколируются минимально или полностью.

use master;
go

--Тестовая БД с полным режимом восстановления
if db_id( 'test_database_full' ) is not null
drop database test_database_full;
go
create database test_database_full;
go
alter database test_database_full set recovery full;
go

--Тестовая БД с неполным протоколированием
if db_id( 'test_database_bulk_logged' ) is not null
drop database test_database_bulk_logged;
go
create database test_database_bulk_logged;
go
alter database test_database_bulk_logged set recovery bulk_logged;
go

--Тестовая БД с простой моделью
if db_id( 'test_database_simple' ) is not null
drop database test_database_simple;
go
create database test_database_simple;
go
alter database test_database_simple set recovery simple;
go

use master;

--Тестовая БД с полным режимом восстановления

if db_id( 'test_database_full' ) is not null

drop database test_database_full;

create database test_database_full;

alter database test_database_full set recovery full;

--Тестовая БД с неполным протоколированием

if db_id( 'test_database_bulk_logged' ) is not null

drop database test_database_bulk_logged;

create database test_database_bulk_logged;

alter database test_database_bulk_logged set recovery bulk_logged;

--Тестовая БД с простой моделью

if db_id( 'test_database_simple' ) is not null

drop database test_database_simple;

create database test_database_simple;

alter database test_database_simple set recovery simple;

Список наших баз данных:

select name, recovery_model_desc
  from sys.databases
  where name like 'test_database_%'
  order by database_id;
go

select name, recovery_model_desc

from sys.databases

where name like 'test_database_%'

order by database_id;

Создадим в каждой БД по одной таблице.

use test_database_full;
go

if object_id( 'dbo.table_full', 'U' ) is not null
drop table dbo.table_full;
go

create table dbo.table_full ( a varchar(512) );
go

insert into dbo.table_full
select distinct replicate( rtrim(type), 512 )
from master..spt_values
where len( type ) = 1;
go

--------------------------------------------------------

use test_database_bulk_logged;
go

if object_id( 'dbo.table_bulk_logged', 'U' ) is not null
drop table dbo.table_bulk_logged;
go

create table dbo.table_bulk_logged ( a varchar(512) );
go

insert into dbo.table_bulk_logged
select distinct replicate( rtrim(type), 512 )
from master..spt_values
where len( type ) = 1;
go

--------------------------------------------------------

use test_database_simple;
go

if object_id( 'dbo.table_simple', 'U' ) is not null
drop table dbo.table_simple;
go

create table dbo.table_simple ( a varchar(512) );
go

insert into dbo.table_simple
select distinct replicate( rtrim(type), 512 )
from master..spt_values
where len( type ) = 1;
go

use test_database_full;

if object_id( 'dbo.table_full', 'U' ) is not null

drop table dbo.table_full;

create table dbo.table_full ( a varchar(512) );

insert into dbo.table_full

select distinct replicate( rtrim(type), 512 )

from master..spt_values

where len( type ) = 1;

--------------------------------------------------------

use test_database_bulk_logged;

if object_id( 'dbo.table_bulk_logged', 'U' ) is not null

drop table dbo.table_bulk_logged;

create table dbo.table_bulk_logged ( a varchar(512) );

insert into dbo.table_bulk_logged

select distinct replicate( rtrim(type), 512 )

from master..spt_values

where len( type ) = 1;

--------------------------------------------------------

use test_database_simple;

if object_id( 'dbo.table_simple', 'U' ) is not null

drop table dbo.table_simple;

create table dbo.table_simple ( a varchar(512) );

insert into dbo.table_simple

select distinct replicate( rtrim(type), 512 )

from master..spt_values

where len( type ) = 1;

Cоздадим индекс на каждой из таблиц:

use test_database_full;
go
checkpoint;
go
create index xxx on dbo.table_full (a);
go

use test_database_bulk_logged;
go
checkpoint;
go
create index xxx on dbo.table_bulk_logged (a);
go

use test_database_simple;
go
checkpoint;
go
create index xxx on dbo.table_simple (a);
go

use test_database_full;

checkpoint;

create index xxx on dbo.table_full (a);

use test_database_bulk_logged;

checkpoint;

create index xxx on dbo.table_bulk_logged (a);

use test_database_simple;

checkpoint;

create index xxx on dbo.table_simple (a);

Перед созданием индекса, я создаю контрольную точку (checkpoint).

А теперь посмотрим с помощью недокументированной табличной функции fn_dblog, что было записано в журнал транзакций.

use test_database_full;
go
select AllocUnitName, Operation, Context, [Log Record Length]
  from fn_dblog( default, default )
  where AllocUnitName like 'dbo.table_%';
go

use test_database_bulk_logged;
go
select AllocUnitName, Operation, Context, [Log Record Length]
  from fn_dblog( default, default )
  where AllocUnitName like 'dbo.table_%';
go

use test_database_simple;
go
select AllocUnitName, Operation, Context, [Log Record Length]
  from fn_dblog( default, default )
  where AllocUnitName like 'dbo.table_%';
go

use test_database_full;

select AllocUnitName, Operation, Context, [Log Record Length]

from fn_dblog( default, default )

where AllocUnitName like 'dbo.table_%';

use test_database_bulk_logged;

select AllocUnitName, Operation, Context, [Log Record Length]

from fn_dblog( default, default )

where AllocUnitName like 'dbo.table_%';

use test_database_simple;

select AllocUnitName, Operation, Context, [Log Record Length]

from fn_dblog( default, default )

where AllocUnitName like 'dbo.table_%';

Далее вы можете самостоятельно убедиться на сколько полно логируются другие операции над индексами. Удачи!

Минимальное протоколирование (Bulk logged) и индексы

Автор: Alexey Knyazev

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

Авторизация

Контакты

Важное

Интересное

Разное

События

Рубрики

Архивы

Минимальное протоколирование (Bulk logged) и индексы

Автор: Alexey Knyazev

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

Авторизация

Контакты

Важное

Интересное

Разное

События

Мы в социальных сетях

Наши партнёры

Метки

Рубрики

Архивы