Entenda o Database Snapshot!

Posted on julho 29, 2011

6


O database snapshot é um feature que surgiu a partir do SQL Server 2005 Enterprise, e que possibilita a criação de uma “Foto”do banco de dados em read-only. Durante a criação dessa “foto” do banco de dados, todas as transações que não foram commitadas são desconsideradas, isso ocorrre porque o SQL Server não poderá garantir a consistência e integridade dos dados. Se por acaso a base de dados que conter uma  “Foto” tiver problemas e por algum motivo ficar offline, o banco de dados de origem também ficará, isso ocorre porque o database snapshot faz referência aos arquivos físicos do banco de dados de origem. O database snapshot opera em nível de data-page level, ou seja, antes da página ser modificada na fonte, a mesma é copiada para a “Foto”, essa operação é chamada de copy-on-write ou seja na criação do Database Snapshot temos somente ponteiros direcionando para as página no banco de dados fonte, quando essa página é alterada, a página é transferida para a “Foto”. Para o armazenamento das páginas originais é utilizado sparse files. Inicialmente o arquivo é vazio, porém de acordo com que as páginas são copiadas para o arquivo o mesmo começa a crescer.

 

Algumas informações de criação e visualização dos arquivos gerados.

 

–Criação e Validação Database Snasphot                                                

CREATE DATABASE WorkSpaceDB

GO

 

USE WorkSpaceDB

GO

 

–Nome do Arquivo de Dados

SELECT  *

FROM sys.database_files

 

CREATE DATABASE snap_WorkSpaceDB ON

( NAME = WorkSpaceDB, FILENAME =

‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\WorkSpaceDB.ss’ )

AS SNAPSHOT OF WorkSpaceDB;

GO

 

USE snap_WorkSpaceDB

go

 

–Visualização dos Arquivos                                           

 

SELECT f.type_desc, f.name, f.physical_name, f.state_desc, f.is_sparse

FROM sys.master_files AS F

WHERE F.database_id = DB_ID()

 

image

 

Sparse File

Os sparse_files é um recurso do NTFS, inicialmente não contem dados. Quando o arquivo é criado, o NTFS gradativamente aloca espaço, sendo assim o arquivo poderá crescer gradualmente dependendo das requisições que forem feitas para o mesmo. Se por acaso o banco de dados estiver sem espaço em disco, e assim for marcado como suspect, então possivelmente excluindo o database snapshot o mesmo possa voltar ao normal. O crescimento do arquivo se da de 64 KB á 64 KB, o último crescimento do arquivo poderá ter de 1 a 8 KB de espaço disponível, dependendo de quantas páginas terão que ser copiadas para o arquivo. O tamanho do arquivo não poderá ultrapassar o tamanho do arquivo .mdf do banco de dados.

 

Funcionamento

image

Quando há uma inserção no banco de dados fonte, a página que foi modificada é copiada pelo sistema de copy-on-write (COW) para o database snapshot específico, fazendo assim com que o sparse file comece a crescer.

 

image

Quando há alguma operação de leitura que foi requisitada a partir do Database Snapshot, a realização da leitura do dado se dá pelo acesso no banco de dados fonte para as páginas que ainda não foram copiadas para a “Foto” ou seja para as páginas que ainda não foram copiadas para o Snapshot, há nele um ponteiro para a página no banco de dados fonte, por isso que podemos também ter alguns problemas de IO, isso ocorro porque a base terá diversos ponteiros para saber aonde se encontra a página que foi requisitada, sendo assim a requisição poderá ficar prejudicada.

 

image

Então quando temos a página que foi solicitada para leitura dentro do Snapshot, não é necessário que a Engine vá ao banco de dados fonte, porque a página está alocada no sparse file do Database Snapshot.

 

image

Depois de diversas inserções no database snapshot o sparse file do Database Snapshot ficará muito grande fazendo com que o mesmo tenha aproximadamente o tamanho do banco de dados original

 

Sendo assim, vamos visualizar o sparse file do banco de dados criado anteriormente.

 

image

 

image

 

 

Internamente

Quando há a criação de um database snapshot, é guardado na memória um mapa de bit que indica em nível página, se a página foi alterada ou não, se esse bit estiver marcado então a página original é copiada para o sparse file.

Pensando como a engine do SQL Server, o database snapshot é um novo banco, sendo assim cada banco de dados tem um cache no buffer pool, sendo assim quando a consulta é realizada é necessário que seja feita uma leitura física no disco para que a informação seja retornada

 

image

 

A cópia da página para a “Foto”tem um grande custo de IO, isso se torna um cenário de grande perda de performance.

A rotina de alocação de uma nova página se dá pelo fato de criar e formatar uma página. A primeira ação para alocar uma nova página, é verificar se o banco de dados contem um Database Snapshot, se houver a página anterior será copiada para o sparse file antes de ser inserida uma nova página formatada no banco de dados.

A quantidade de número de páginas lidas de cada banco de dados do buffer pool é informada através da sys.dm_buffer_descriptors, agora como as páginas já estão em cache, então não temos nenhuma leitura física.

Armazenamento do Snapshot em discos diferentes pode aumentar a performance na hora da escrita dentro do arquivo.

 

Nova Página – Realocação

Quando há um truncate na tabela ou uma desalocação de página, isso não afeta o armazenamento físico da mesma. A mudança ocorre na GAM, SGAM, IAM…

Ocorrendo um truncate na tabela e fazendo uma pesquisa pelo Snapshot, o mesmo irá buscar o dado do banco de dados fonte, isso ocorre porque a mudança física da página não é realizada, os registros são marcados como alterados porém visualizando pelo snapshot essas páginas não são copiadas, isso evita ter que copiar uma tabela inteira quando ocorre uma operação desse tipo.

 

Tempo de Criação de Database Snapshot

Quando não há muitas operações de leitura ocorrendo no servidor, a criação e manutenção dos Snapshots são bastante eficientes, porém se no banco de dados fonte, porém, havendo inúmeras operações de escrita o tempo de criação aumenta. Quanto mais páginas sejam modificadas no banco de dados fonte, maior é o tempo de criação, isso ocorre porque todas as páginas antes de serem modificadas teram que ser movidas para o snapshot.

 

Impacto de IO

O impacto de IO ocorre durante a criação de um novo snapshot, porque se houver bastante páginas modificadas no banco de dados fonte o recovery do banco de dado será demorado, porque essa operação contempla undo, redo, analysis e start do banco, antes da operação ser concluída.

 

Múltiplos Databases Snapshots

As operações de Update e Delete no banco de dados fonte, causam intensivas operações de IO no banco de dados, isso ocorre porque para cada página que é modificada pela primeira vez na fonte, todos os Databases snapshots criados para o banco de dados fonte são tocados.

 

Potenciais Problemas e Resoluções

 

Problemas Durante Rotina de Backup

Geralmente dentro de nossas instâncias do SQL Server temos algumas rotinas nas quais rodamos periodicamente. Sendo mais específico, vamos ver como um restore de database snapshot pode afetar a rotina de backup do banco de dados.

 

Vamos colocar a base em Recovery Model FULL, fazer um backup de log, senão estaremos em Truncate Log on CheckPoint e realizar algumas inserções

 

–Reverting Database Snapshot                                         

ALTER DATABASE WorkSpaceDB

SET RECOVERY FULL;

GO

 

BACKUP DATABASE WorkSpaceDB

TO DISK = ‘D:\temp\WorkSpaceDB.bak’ WITH INIT;

GO

 

CREATE TABLE WorkSpaceDB.dbo.MyTable (c1 INT);

GO

 

BACKUP LOG WorkSpaceDB

TO DISK = ‘D:\temp\WorkSpaceDB_log.bak’ WITH INIT;

GO

USE WorkSpaceDB

go

 

INSERT INTO dbo.MyTable (c1) VALUES (100000)

GO 10000

 

INSERT INTO dbo.MyTable (c1) VALUES (100000)

GO 10000

Agora criaremos o database snapshot, dropar a tabela e logo depois iremos fazer um restore do Database Snapshot

 

–Verificação dos Arquivos do Banco de Dados

SELECT  *

FROM WorkSpaceDB.sys.database_files

 

–Criação Database Snapshot

CREATE DATABASE ST_Snap ON

(NAME = WorkSpaceDB, FILENAME = ‘D:\temp\WorkSpaceDB_snap.snp’)

AS SNAPSHOT OF WorkSpaceDB;

GO

 

USE WorkSpaceDB

GO

 

–Deletar Informações, Tabela

DROP TABLE dbo.MyTable

 

–Reverting Database Snapshot

USE master

go

 

RESTORE DATABASE WorkSpaceDB FROM DATABASE_SNAPSHOT = ‘ST_Snap’

 

USE WorkSpaceDB

GO

SELECT  *

FROM dbo.MyTable

 

Depois do restore do database snapshot ter acontecido na base de dados, suponhamos que o job que realiza o backup de log do banco de dados seja executado, agora o que deveria acontecer? Logicamente o mesmo teria que realizar o backup n0rmalmente, porém…

 

BACKUP LOG WorkSpaceDB

TO DISK = ‘D:\temp\WorkSpaceDB_log.bak’;

GO

 

Msg 4214, Level 16, State 1, Line 1

BACKUP LOG cannot be performed because there is no current database backup.

Msg 3013, Level 16, State 1, Line 1

BACKUP LOG is terminating abnormally.

 

Quando é realizado um restore vindo do database snapshot, o mesmo muda a sequên cia do LSN fazendo assim com que a rotina não seja executada corretamente. A solução para isso é realizar um backup DIFERENCIAL ou FULL, fique sempre atento!!

 

 

Leituras Físicas

 

Para esse exemplo estou utilizando o nosso famoso AdventureWorks.

 

Bem, o que já sabemos é que o Database Snapshot é uma foto do banco de dados que é retirado em um momento específico, porém além de entendermos isso temos que prestar atenção no quisito performance, isso não pode fazer tanta diferença em ambientes de pequeno porte, porém em médios e grandes se não soubermos utilizá-lo bem pode até nos prejudicar, vendo isso vamos ao seguinte caso.

Vamos analisar o seguinte cenário:

 

Criando o Database Snapshot, limpando o cache e rodando a mesma consulta no banco de dados fonte e no database snapshot, qual resultado esperamos?

 

USE AdventureWorks

go

 

–Criação Database Snapshot

CREATE DATABASE [AdventureWorksSnapshot] ON

(

      NAME=[AdventureWorks_Data],

      FILENAME=N’D:\temp\AdventureWorks_DataSnapshot.mdf’

),

(

      NAME=[AdventureWorks_FLG],

      FILENAME=N’D:\temp\AdventureWorks_DataSnapshot.ndf’

)

AS SNAPSHOT OF [AdventureWorks];

 

–Buscando Informações

DBCC DROPCLEANBUFFERS

 

SET STATISTICS IO ON

SET STATISTICS TIME ON

 

–Banco de Dados Fonte

SELECT  *

FROM AdventureWorks.Sales.SalesOrderDetail AS D

INNER JOIN AdventureWorks.Sales.SalesOrderHeader AS O

ON d.SalesOrderID = o.SalesOrderID

WHERE CustomerID = 676;

 

–Banco de Dados “Fonte”

SELECT  *

FROM AdventureWorksSnapshot.Sales.SalesOrderDetail AS D

INNER JOIN AdventureWorksSnapshot.Sales.SalesOrderHeader AS O

ON d.SalesOrderID = o.SalesOrderID

WHERE CustomerID = 676;

 

 

Na primeira consulta que é executada no banco de dados fonte é esperado que o mesmo realize leituras físicas e que depois a mesma seja colocada em cache. Porém e a segunda consulta que supostamente já estaria em cache, sendo assim não seria necessário que o mesmo realizasse leituras físicas.

 

(359 row(s) affected)
Table ‘SalesOrderDetail’. Scan count 12, logical reads 44, physical reads 14, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderHeader’. Scan count 1, logical reads 38, physical reads 17, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

(359 row(s) affected)
Table ‘SalesOrderDetail’. Scan count 12, logical reads 44, physical reads 24, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table ‘SalesOrderHeader’. Scan count 1, logical reads 38, physical reads 17, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

E assim a verdade aparece! O data cache é referenciado por uma combinação de dbid, fileid e pageid, sendo assim para cada Database Snapshot a primeira leitura será física podendo causar o que o nosso sensacional MVP Luti diz no artigo de Data Cache Trashing – http://luticm.blogspot.com/2011/07/artigo-o-caso-dos-snapshots-e-data.html, sendo assim descobrimos um grande problema de performance nos Database Snapshots.

 

Melhores Práticas e Recomendações

· Colocar o arquivo de .ldf e o arquivo do Snapshot em discos diferentes. O database snapshot realiza muitas leituras de páginas no arquivo fonte, uma modificação no banco de dados fonte faz com que as páginas sejam movidas, assim essas modificações passam pelo arquivo de log do banco de dados fonte

· Tentar minimizar a quantidade de databases snapshots, de uma base de dados, quanto maior a quantidade mais intensivas operações de IO aconteceram.

· Evitar a criação de database snapshot durante alguma operação de manutenção de índice

 

Lados Positivos

· Banco de Dados para relatórios gerenciais

· Usando o banco de dados de espelhamento para visualização

· Antes de uma operação cuidadosa

· Antes de updates

· Banco de Dados de Testes

 

Lados Negativos

· O banco de dados não pode ser deletado, modificado ou restaurado.

· Copy-On-Write diminui a performance do banco de dados.

· O Database Snapshot tem que ser criado na instância da base de dados

· Não é possível realizar o Database Snapshot para banco de dados de sistema

· Nâo é possível criar em partições FAT32

· Full Text Indexing não é suportado

· Se não houver espaço o snapshot entrará em suspect, e então terá que ser dropado