Минимальное протоколирование (MIN_LOGGED) и полная (FULL) модель восстановления

by Alexey Knyazev 2. апреля 2016 19:14


SQL Server позволяет повысить производительность ряда операций за счёт минимального протоколирование изменений в журнал транзакций.
Минимальное протоколирование — это протоколирование только информации, необходимой для восстановления транзакции без поддержки восстановления на момент времени.
Список таких операций впечатляет (https://msdn.microsoft.com/ru-ru/library/ms190925.aspx):

  • Операции массового импорта (bcp, BULK INSERT и INSERT... SELECT)
  • Операции SELECT INTO
  • Инструкции WRITETEXT и UPDATETEXT при вставке или добавлении новых данных в столбцы с типом данных text, ntext и image
  • Частичные обновления типов данных с большими значениями с помощью предложений. WRITE инструкции UPDATE при вставке или добавлении новых данных
  • Операции с индексами (CREATE INDEX [включая индексированные представления], ALTER INDEX REBUILD или DBCC DBREINDEX, DROP INDEX), тесты над индексами я проводил ранее - http://t-sql.ru/post/index_minimally_logged.aspx

За счёт минимального протоколирования можно в разы сократить время выполнения привычных запросов, но это возможно, только если у вас используется модель восстановления – простая (SIMPLE) или модель восстановления с неполным протоколированием (BULK LOGGED).
Как же быть, если мы используем полную модель восстановления (FULL)? В BOL по этому поводу написано:
В модели полного восстановления все массовые операции полностью протоколируются. Однако для набора массовых операций можно использовать минимальное протоколирование, временно переключив базу данных на модель восстановления с неполным протоколированием во время массовых операций. Минимальное протоколирование более эффективно, чем полное, и снижает вероятность того, что во время массовой операции большого объема будет заполнено все доступное пространство журнала транзакций. Однако, если при включенном минимальном протоколировании база данных будет повреждена или потеряна, ее нельзя будет восстановить до точки сбоя.
Но всё ли так однозначно? Давайте проведём небольшое тестирование одной из операций с минимальным протоколированием (операции массового импорта) в полной модели восстановления.

use master;
go

--Удалим БД, если она уже существует
if db_id( 'MIN_LOGGED' ) is not null
begin
  alter database MIN_LOGGED set restricted_user with rollback immediate;
  drop database MIN_LOGGED;
end

--Создадим БД для тестов
create database MIN_LOGGED;
go
--Переключим модель в режим полного протоколирования
alter database MIN_LOGGED set recovery full;
go
--Переключение на модель полного восстановления или модель восстановления с неполным протоколированием 
--вступает в силу только после создания первой резервной копии данных
backup database MIN_LOGGED to disk = 'c:\temp\MIN_LOGGED_01.bak';
go

use MIN_LOGGED;
go

--Создадим 2 таблицы для тестов
create table dbo.test_table_01 (id int identity, val varchar(255));
go
create table dbo.test_table_02 (id int identity, val varchar(255));
go
set statistics time on;
go
--Вставка с хинтом TABLOCK 
--(позволяет использовать минимальное протоколирование для массовой загрузки)
--https://msdn.microsoft.com/ru-ru/library/ms190422.aspx
insert into dbo.test_table_01 with (tablock)
select replicate('A', 255) from sys.all_objects t1, sys.all_objects t2;
go
insert into dbo.test_table_02
select replicate('A', 255) from sys.all_objects t1, sys.all_objects t2;
go
set statistics time off;
go
/*
SQL Server Execution Times:
  CPU time = 5445 ms,  elapsed time = 211902 ms.
SQL Server Execution Times:
  CPU time = 16333 ms,  elapsed time = 280117 ms.
*/
Разница во времени выполнения запросов существенна 211 vs 280 секунд (или 25%). Обратимся к журналу транзакций:
--Посмотрим сколько строк у нас записалось в журнал транзакций
select count(*) cnt 
  from fn_dblog( default, default )
  where AllocUnitName like 'dbo.test_table_01%';
go

select count(*) cnt 
  from fn_dblog( default, default )
  where AllocUnitName like 'dbo.test_table_02%';
go

Кроме того, в ряде случаев страница BCM - Bulk Change Map (Схема массовых изменений), она отслеживает экстенты, измененные операциями с неполным протоколированием, показывает нам что операция выполнялась с минимальным протоколированием.

Всё это наталкивает на мысль, что, несмотря на всю документацию, при полной модели восстановления мы получили неполное протоколирование, а это, как мы помним, чревато потерей данных, т.к. мы не сможем восстановить БД на определённый момент времени.
Давайте попробуем удалить часть данных в транзакции и восстановиться на время начала этой транзакции.

--Запомним, сколько записей в dbo.test_table_01
exec sp_spaceused 'dbo.test_table_01'; --4 647 336                          
go 
begin transaction flag with mark 'flag';
delete top (1000) from dbo.test_table_01;
commit transaction
go
--Убедимся, что удаление произошло
exec sp_spaceused 'dbo.test_table_01'; --4 646 336                                       
go 
--Очистим таблицу
truncate table dbo.test_table_01;
go
--Сделаем резервную копию журнала транзакций
backup log MIN_LOGGED to disk = 'c:\temp\MIN_LOGGED_ldf_01.bak';
go

--Восстановим БД на момент начала транзакции "flag"
use master;
go
restore database MIN_LOGGED from disk = 'c:\temp\MIN_LOGGED_01.bak' with norecovery, replace;
go
restore database MIN_LOGGED from disk = 'c:\temp\MIN_LOGGED_ldf_01.bak' with stopatmark = 'flag', recovery;
go

use MIN_LOGGED;
go
exec sp_spaceused 'dbo.test_table_01'; --4 646 336                                       
go 
Вуаля!!!, мы смогли восстановиться на нужную нам «точку». Но почему же у нас операция была помечена, как операция с минимальным протоколированием и почему операция выполнилась быстрее?
Во-первых, страница Bulk Change Map актуальна только для модели восстановления с неполным протоколированием (BULK LOGGED).
Во-вторых, ряд операций минимально протоколируется и при полной модели восстановления, например, TRUNCATE (в журнал транзакций записывает только данные об освобождении страниц).
В-третьих, при анализе журнала транзакций, необходимо смотреть не только кол-во операций, которые попали в лог, но и их размер [Log Record Length], тогда разница будет уже не такая существенная.

Основное отличие в том, что SQL Server для операций массовой вставки выделяет сразу целые страницы и экстенты, а не по строкам, тем самым количество записей существенно меньше, но их размер больше.
Но основное преимущество от минимального протоколирования мы сможем получить только при режиме восстановления BULK LOGGED, либо SIMPLE. И если проделать все операции, описанные выше, уже при одной из этих моделей, то мы получим следующие цифры (что уже на порядок лучше):
SQL Server Execution Times:
  CPU time = 15476 ms,  elapsed time = 82727 ms.

SQL Server Execution Times:
  CPU time = 16801 ms,  elapsed time = 202738 ms.


А при попытке восстановить БД на определённый момент времени мы получим ошибку:



Помните об этой особенности, когда пишите свой код. Даже при полной модели восстановления можно получить более эффективные запросы.

Ссылка по теме: http://www.enabledbusinesssolutions.com/blog/?p=1222

Tags: ,

SQL Server

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

  Country flag

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