Backups de dados vs. Cache de dados

Quer uma formação decente (cansei de brigar com o Blogger vs Live Writer, Word 2010, etc)? Baixe o PDF. :-)



Será que o SQL Server utiliza as páginas que estão em cache para acelerar a realização do backup?

É uma pergunta que já me fizeram algumas vezes, então resolvi colocar um script bem simples para mostrar qual é a resposta à pergunta.
Por simplicidade eu vou colocando os scripts com comentários, então você pode ir acompanhando o que estou fazendo. Em primeiro lugar que tal criarmos um banco para nosso teste e tirarmos um backup para padronizar a brincadeira.

USE
Master
go

-- DROP Database BackupCache

-- Vamos criar uma estrutura simples para brincar...
CREATE
DATABASE BackupCache
go

USE BackupCache
go

CREATE
TABLE TabelaDados
(Codigo INT
IDENTITY(1,1) NOT NULL,
Texto VARCHAR(8000) NOT NULL)
GO

INSERT
INTO TabelaDados
(Texto) VALUES (REPLICATE('Sr. Nimbus', 1000))
go 1000

-- Cria um primeiro backup para deixar o exemplo padronizado
BACKUP
DATABASE BackupCache
TO DISK = 'C:\Temp\BackupCache.bkp'
WITH INIT, Format
go
Como podemos analisar se os dados estão sendo lidos do arquivo físico? Claro, sys.dm_io_virtual_file_stats!
-- Agora vamos a análise
-- Primeiro criamos uma tabela para armazenar filestats...

-- DROP TABLE FileStats

select *, SYSDATETIME() as Hora
INTO FileStats
from sys.dm_io_virtual_file_stats(null,
null) where 1=2

SELECT *
FROM FileStats

-- Qual nosso backup id?
select DB_ID()
-- = 46

-- O arquivo que me interessa mais é o 1 = MDF
SELECT *, SYSDATETIME()
from sys.dm_io_virtual_file_stats(46, null)
Se o backup estiver lendo os dados diretamente do disco vamos ver a quantidade de bytes lidos (num_of_bytes_read) aumentando, então para esse artigo eu estou me preocupando mais com o MDF. Primeiro vamos fazer um teste limpando primeiro a cache de dados...

-- Em um primeiro momento, vamos fazer um backup com a cache fria
DBCC DROPCLEANBUFFERS


INSERT INTO FileStats
SELECT *, SYSDATETIME()
from sys.dm_io_virtual_file_stats(46, null)

-- 1189 pages
BACKUP DATABASE BackupCache
TO DISK = 'C:\Temp\BackupCache.bkp'
WITH INIT, Format

/*
Processed 1184 pages for database 'BackupCache', file 'BackupCache' on file 1.
Processed 5 pages for database 'BackupCache', file 'BackupCache_log' on file 1.
BACKUP DATABASE successfully processed 1189 pages in 1.216 seconds (7.633 MB/sec).
*/

INSERT INTO FileStats
SELECT *, SYSDATETIME()
from sys.dm_io_virtual_file_stats(46, null)
go


Analisando a saída da DMV conseguimos ver que foram lidos 9953280 bytes, exatamente 1215 páginas. Um pouco a mais que o número de páginas processadas, talvez páginas lidas duas vezes ou algo que não vai para backup. O importante aqui é que as páginas foram lidas diretamente do arquivo físico.
Agora vamos testar com as páginas de dados em cache.

-- E se as páginas de dados já estiverem em cache?
-- Notem que o backup NÃO colocou as páginas em cache
select
*
from
sys.dm_os_buffer_descriptors
where database_id = 46


select
*
from TabelaDados


-- 1067 páginas em cache (o número pode variar)
select
*
from
sys.dm_os_buffer_descriptors
where database_id = 46


-- Agora vamos ao backup...
INSERT
INTO FileStats
SELECT
*,
SYSDATETIME()
from
sys.dm_io_virtual_file_stats(46,
null)


-- 1189 pages
BACKUP
DATABASE BackupCache
TO
DISK
=
'C:\Temp\BackupCache.bkp'
WITH
INIT, Format


/*
Processed 1184 pages for database 'BackupCache', file 'BackupCache' on file 1.
Processed 2 pages for database 'BackupCache', file 'BackupCache_log' on file 1.
BACKUP DATABASE successfully processed 1186 pages in 1.442 seconds (6.422 MB/sec).
*/


INSERT
INTO FileStats
SELECT
*,
SYSDATETIME()
from
sys.dm_io_virtual_file_stats(46,
null)
go

Fazendo novamente o cálculo com base nas leituras físicas que foram feitas...

-- Analisando as duas últimas entradas do arquivo de dados (BytesRead)
PRINT (38912000 - 29212672)
= PRINT 9699328 / 8192 = 1184 páginas


-- Foram lidas 1184 páginas do arquivo físico.
-- Alguma página que estava em memória foi utilizada? NÃO!


Então aqui já temos nossa resposta, mas só para deixar a coisa mais divertida, e se tivermos páginas sujas em cache?! Primeiro deixamos as páginas sujas (notem que já foi feito o flush de muitas algumas páginas alteradas, o que já forçou escritas no arquivo e isso pode ser confirmado pelo file stats) e depois realizamos o backup.

-- E com dirty pages? Veja coluna is_modified
select
*
from
sys.dm_os_buffer_descriptors
where database_id = 46
    AND is_modified = 1


UPDATE TabelaDados
    SET Texto =
REPLICATE('Sr! Nimbus', 1000)


-- Flush de dirty pages foi feito e sobraram N páginas sujas... (313 no meu caso)
select
*
from
sys.dm_os_buffer_descriptors
where database_id = 46
    AND is_modified = 1
go


-- Agora vamos ao backup...
INSERT
INTO FileStats
SELECT
*,
SYSDATETIME()
from
sys.dm_io_virtual_file_stats(46,
null)


BACKUP
DATABASE BackupCache
TO
DISK
=
'C:\Temp\BackupCache.bkp'
WITH
INIT, Format


/*
Processed 1184 pages for database 'BackupCache', file 'BackupCache' on file 1.
Processed 2 pages for database 'BackupCache', file 'BackupCache_log' on file 1.
BACKUP DATABASE successfully processed 1186 pages in 1.322 seconds (7.005 MB/sec).
*/


INSERT
INTO FileStats
SELECT
*,
SYSDATETIME()
from
sys.dm_io_virtual_file_stats(46,
null)
go

select
*
from FileStats
-- Analisando as duas últimas entradas do arquivo de dados (BytesRead)
PRINT (48611328 - 38912000)
= PRINT 9699328 / 8192 = 1184 páginas


Analisando os file stats, vemos novamente que o SQL Server leu as páginas do disco! Hhuumm, e as páginas sujas que estavam em memória? Sumiram! Of course my horse, o backup disparou um checkpoint que fez um flush das páginas sujas! Duvida? Veja o log de transação.

-- Aonde foram parar mas minhas páginas sujas??? Somente 4 páginas?
-- Sim, lembra que o backup executa um checkpoint? Flush foi feito...
select
*
from
sys.dm_os_buffer_descriptors
where database_id = 46
    AND is_modified = 1
    
/*
72057594039894016    DATA_PAGE
281474978283520    DATA_PAGE
6488064    FILEHEADER_PAGE
6488064    DIFF_MAP_PAGE
*/
-- O que, não acredita?
SELECT
*
FROM
fn_dblog(null,
null)


/*
Viu o BEGIN_CKPT e END_CKPT?


00000084:00000131:003a    LOP_BEGIN_CKPT    LCX_NULL
00000084:00000148:0001    LOP_END_CKPT    LCX_NULL
00000084:00000149:0001    LOP_BEGIN_XACT    LCX_NULL
00000084:00000149:0002    LOP_MODIFY_COLUMNS    LCX_CLUSTERED
00000084:00000149:0003    LOP_PREP_XACT    LCX_NULL
00000084:0000014a:0001    LOP_COMMIT_XACT    LCX_NULL
00000084:0000014b:0001    LOP_FILE_HDR_MODIFY    LCX_FILE_HEADER
00000084:0000014b:0002    LOP_MODIFY_ROW    LCX_BOOT_PAGE_CKPT
00000084:0000014b:0003    LOP_MODIFY_ROW    LCX_BOOT_PAGE_CKPT
00000084:00000150:0001    LOP_BEGIN_XACT    LCX_NULL
00000084:00000150:0002    LOP_SET_BITS    LCX_DIFF_MAP
00000084:00000150:0003    LOP_INSERT_ROWS    LCX_HEAP
00000084:00000150:0004    LOP_INSERT_ROWS    LCX_HEAP
00000084:00000150:0005    LOP_COMMIT_XACT    LCX_NULL

Ô curioso, se você olhar no log as páginas que sofreram alterações, vai ver 4 páginas.
Nossa, que coincidência com o buffer descriptors! HAHAHAHA. Lindo.
*/

Resumindo a brincadeira, os backups do SQL Server vão ler diretamente as páginas que estão em disco e NÃO vão usar as páginas que estão em cache, independentemente de você tiver 64GB de dados já em memória. Então você deve tomar cuidado com os backups de dados durante o dia, pois eles podem sim impactar diretamente seu subsistema de discos.
Espero que você tenha se divertido.
[]s
Luciano Caixeta Moreira - {Luti}
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
http://www.srnimbus.com.br/