Минимальное протоколирование. Индексы.

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

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

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

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

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

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

Перед созданием индекса, я создаю контрольную точку (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

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