SQL Server - Delayed Transaction Durability (Asynchronous Commit)

Olá,

No artigo de Abril/2015 foi abordado o uso do COMMIT Assíncrono no Oracle de forma a acelerar instruções de INSERT no banco de dados. Da mesma forma que no Oracle, outros SGBDs também possuem esse recurso:

• Oracle 10g R2 - COMMIT_WRITE

• Oracle 11g/12c - COMMIT_WAIT

• MySQL - innodb_flush_log_at_trx_commit

• SQL Server 2014 - Delayed Transaction Durability

Neste artigo irei abordar este recurso no SQL Server chamado de "Delayed Transaction Durability" introduzido no SQL Server 2014. Por padrão, no SQL Server as transações são consideradas "Full transaction durability", ou seja, elas são síncronas de forma que quando um COMMIT é executado, o SQL Server assegura que a transação é gravada no arquivo transaction log antes da mesma ser finalizada. Ao alterar para o modo "Delayed transaction durability", a mesma se torna assíncrona, ou seja, a transação é escrita em um buffer de memória e descarregada no arquivo transaction log somente quando este buffer ficar cheio ou através da execução do procedimento sp_flush_log. Essa opção reduz a latência das transações de forma que a mesma seja mais rápida para o usuário. 

Segue abaixo um exemplo prático na qual serão inseridos em uma tabela cerca de um milhão de registros. O primeiro exemplo usando o Full transaction durability e o segundo usando o "Delayed Transaction Durability".

[root ~]# sqlcmd -S localhost -U sa -Padmin

1> use bd01
2> GO
Changed database context to 'bd01'.

1>   SET NOCOUNT ON
2>   -- insert de 1.000.000 de registros
3>   DECLARE @i INT = 1, @start DATETIME = GETDATE();
4>   WHILE @i <= 1000000 BEGIN
5>        BEGIN TRANSACTION;
6>        INSERT INTO teste VALUES (GETDATE());
7>        COMMIT;
8>        SET @i = @i + 1;
9>   END
10>   -- tempo de execução em segundos
11>   SELECT DATEDIFF(ss, @start, GETDATE()) TIME;
12>   GO

TIME
-----------
       1508

Após a execução do bloco TSQL acima, a mesma finalizou após 1508 segundos (25 minutos). Analisando o o I/O realizado pode-se perceber um uso excessivo no disco nv3n1, utilizado para armazenar o arquivo transaction log (*.ldf) do banco de dados.

Device:       rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
nv0n1           0.00     1.00    0.00    3.00     0.00     0.04    29.33     0.00    0.00    0.00    0.00   0.00   0.00
nv1n1           0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
nv2n1           0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00
nv3n1           0.00   659.00    0.00 1977.00     0.00    10.30    10.67     0.92    0.47    0.00    0.47   0.47  96.30
nv4n1           0.00     0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00    0.00    0.00   0.00   0.00

Segue abaixo o exemplo com uso do recurso "Delayed transaction durability" (COMMIT Assíncrono).

1>   ALTER DATABASE bd01 SET DELAYED_DURABILITY = ALLOWED WITH NO_WAIT;
2>   GO

1>   SET NOCOUNT ON
2>   -- insert de 1.000.000 de registros
3>   DECLARE @i INT = 1, @start DATETIME = GETDATE();
4>   WHILE @i <= 1000000 BEGIN
5>        BEGIN TRANSACTION;
6>        INSERT INTO teste VALUES (GETDATE());
7>        COMMIT WITH (DELAYED_DURABILITY = ON);
8>        SET @i = @i + 1;
9>   END
10>   -- tempo de execução em segundos
11>   SELECT DATEDIFF(ss, @start, GETDATE()) TIME;
12>   GO

TIME
-----------
         11

Após a execução do bloco TSQL acima, a mesma finalizou após 11 segundos apenas.