Автор: 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
Список наших баз данных:
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
Далее вы можете самостоятельно убедиться на сколько полно логируются другие операции над индексами. Удачи!
