SHRINK ao Log antes do processamento não é uma boa ideia

Há uns dias deparei-me com um script que, no início de um processamento grande, fazia SHRINK aos ficheiros de Log das BD’s com que trabalhava.

Como é meu apanágio, quando disse que “não era uma boa ideia” também disse que “qualquer dia faço uma demo para isso”. Pensei eu que iria ser uma “entradazinha” no blog e vai-se a ver acabou nisto.


tl;dr Não é uma boa ideia porque esses ficheiros vão crescer de novo com o custo adicional desse espaço ter de ser todo colocado a zeros. E não, Grant Volume Maintenance não ajuda quando estamos a falar de ficheiros de Log.


Vamos lá começar:

create database Demo03
create database Demo04

O que temos para já?

use Demo03
go
select name, size, max_size, growth 
from sys.database_files

use Demo04
go
select name, size, max_size, growth 
from sys.database_files

E no disco, o que por lá anda?

Vamos criar duas tabelas exactamente iguais, uma em cada BD e começar a meter para lá dados. Afinal é para isso que servem as BD’s, não?

use Demo03
go
create table TruncateLogDemo
(
	id bigint primary key identity (1,1)
,	Col1 char(50) default '0123456789'
,	Col2 nvarchar(50)
)

insert into TruncateLogDemo
	( Col2 )
select '0123456789'
from sys.objects a
	cross join sys.objects b
	cross join sys.objects c


use Demo04
go
create table TruncateLogDemo
(
	id bigint primary key identity (1,1)
,	Col1 char(50) default '0123456789'
,	Col2 nvarchar(50)
)

insert into TruncateLogDemo
	( Col2 )
select N'0123456789'
from sys.objects a
	cross join sys.objects b
	cross join sys.objects c

Vamos ver o que aconteceu aos nossos ficheiros

use Demo03
go
select name, size, max_size, growth 
from sys.database_files

use Demo04
go
select name, size, max_size, growth 
from sys.database_files

Os tamanhos são iguais entre tabelas, como seria (mais ou menos) esperado.

Vamos adicionar mais algumas linhas, com um pouco mais de tamanho.

use Demo03
go
insert into TruncateLogDemo
	( Col2 )
select N'01234567890123456789'
from sys.objects a
	cross join sys.objects b
	cross join sys.objects c

use Demo04
go
insert into TruncateLogDemo
	( Col2 )
select N'01234567890123456789'
from sys.objects a
	cross join sys.objects b
	cross join sys.objects c

De novo vemos com o que ficámos

use Demo03
go
select name, size, max_size, growth 
from sys.database_files

use Demo04
go
select name, size, max_size, growth 
from sys.database_files

Tudo a crescer, tudo igual, tudo normal.

Agora vamos apenas fazer SHRINK ao Log da Demo04

use Demo04
go
dbcc shrinkfile ('Demo04_log', 0, TRUNCATEONLY)
GO

E voltamos a ver o que temos em termos de ficheiros

use Demo03
go
select name, size, max_size, growth 
from sys.database_files

use Demo04
go
select name, size, max_size, growth 
from sys.database_files

Agora que temos o Log da Demo03 do tamanho depois dos inserts e da Demo04 com os 8K originais, vamos fazer mais um batch de inserts nas duas.

use Demo03
go
insert into TruncateLogDemo
	( Col2 )
select N'01234567890123456789'
from sys.objects a
	cross join sys.objects b
	cross join sys.objects c

use Demo04
go
insert into TruncateLogDemo
	( Col2 )
select N'01234567890123456789'
from sys.objects a
	cross join sys.objects b
	cross join sys.objects c

Vamos começar a ver coisas giras…

statistics time, io on
Planos de execução em nada diferentes
Waits na Demo03
Waits na Demo04

Parece tudo igual, mas já se notam algumas diferenças. PAGEIOLATCH_EX dá lugar a PREEMPTIVE_OS_FILEOPS e a query na Demo04 demorou um pouco mais.

Vamos ver os tamanhos dos nossos ficheiros?

use Demo03
go
select name, size, max_size, growth 
from sys.database_files

use Demo04
go
select name, size, max_size, growth 
from sys.database_files	

Tudo como esperaríamos, e no caso do 4 o espaço está mais bem “ocupado”

E que tal nos despacharmos para uma “à bruta”, mais parecida com o que temos no nosso dia-a-dia?

Vamos fazer SHRINK aos dois Logs, antes de um insert gigantesco.

use Demo03
go
dbcc shrinkfile ('Demo03_log', 0, TRUNCATEONLY)
GO
use Demo04
go
dbcc shrinkfile ('Demo04_log', 0, TRUNCATEONLY)
GO

Relembrando o nosso tamanho inicial

use Demo03
go
select name, size, max_size, growth 
from sys.database_files

use Demo04
go
select name, size, max_size, growth 
from sys.database_files
use Demo03
go
insert into TruncateLogDemo
	( Col2 )
select N'01234567890123456789'
from sys.objects a
	cross join sys.objects b
	cross join sys.objects c
	cross join sys.objects d

use Demo04
go
insert into TruncateLogDemo
	( Col2 )
select N'01234567890123456789'
from sys.objects a
	cross join sys.objects b
	cross join sys.objects c
	cross join sys.objects d

Até aqui basicamente o mesmo, como se esperaria. Apenas um pouco mais de tempo, já que são agora 100 milhões de linhas.

E nos Waits?

Demo03 Time
Demo03 Waits
Demo04 Time
Demo04 Waits

Quase que os poderia ter trocado e não iria notar diferença. Basicamente temos o mesmo em cada insert.

Mas se resolvermos truncar apenas o Log da Demo04?

use Demo04
go
dbcc shrinkfile ('Demo04_log', 0, TRUNCATEONLY)
go


use Demo03
go
insert into TruncateLogDemo
	( Col2 )
select N'01234567890123456789'
from sys.objects a
	cross join sys.objects b
	cross join sys.objects c
	cross join sys.objects d

use Demo04
go
insert into TruncateLogDemo
	( Col2 )
select N'01234567890123456789'
from sys.objects a
	cross join sys.objects b
	cross join sys.objects c
	cross join sys.objects d

Demo03 Time
Demo03 Waits
Demo04 Time
Demo04 Waits

O que vemos de “mais diferente”?

Podemos começar com o tempo? Nestas duas iterações os tempos foram um pouco diferentes. “Apenas” 63% superiores no caso da Demo04 onde tínhamos truncado o Log…

Saliento também mais algumas:

Com o log truncado não temos LATCH_SH, provavelmente o tempo de espera por mais espaço de log não permite mais rapidez.

Quando temos o log truncado aparecem PREEMPTIVE_OS_GETDISKFREESPACE, o sql server tem de perguntar ao sistema operativo se há espaço livre para poder crescer.

Temos muitos mais PREEMPTIVE_OS_FILEOPS quando o log está truncado, afinal precisamos que o sistema operativo nos dê espaço livre.

LOGBUFFER é maior quando não truncamos o log, estamos mais vezes à espera que os log records sejam gravados porque o espaço já lá está, mas o meu SSD mesmo assim não consegue aguentar tanto trabalho.

Temos mais PAGEIOLATCH_EX e WRITELOG, esperamos mais vezes e mais tempo pela escrita no log, porque mais vezes precisamos de aumentar o log.

Além disto tudo, relembro que crescer o log não é apenas “apanhar” um pouco mais de espaço livre, mas o sistema operativo tem também de colocar esse espaço com zeros. Instant File Initialization não funciona com os ficheiros de log.

Resumindo, os tempos foram:

461122 e 478217 da primeira iteração, com os dois logs truncados
336827 e 529971 na segunda iteração, com apenas o log da Demo04 truncado.

Concluindo…

“É uma boa ideia fazer SHRINK aos ficheiros de Log antes de um processamento que vai inserir uns milhões de linhas?” Não.

Share

You may also like...

Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios marcados com *