Entendendo e Melhorando a performance de seus backups

Quando falamos de backup, muita gente se preocupa com o lugar onde estes serão armazenados, período de retenção dos backups, tipos de backup utilizados (full, differencial, log, etc..), mas e quanto a performance?

Sim, você pode (e deve!) configurar seus backups para que sejam feitos da forma mais performática possível, pois lembre-se, estes costumam consumir muitos recursos do seu servidor enquanto são executados.

Sei que agora virá a cabeça de muita gente “ei, mas o SQL Server 2008 tem compressão de backup, isso já não é o bastante?”. Bem, esse recurso realmente é muito bom, mas há outras coisas que podem influenciar a performance de seu backup, além de que ainda há muitos servidores com a versão 2005 ou até anteriores.

Não vou falar aqui de tecnologias (backup compression) ou ferramentas para backup, mas sim de algumas configurações existentes no próprio SQL Server e outras boas práticas que podem influenciar na performance de seus backups.

1. Local do backup

A maioria das pessoas já leu ou ouviu falar que é preferencial fazer o backup em disco físico local do que em um disco removível ou pasta compartilhada de rede e isso é sim uma grande verdade.

O ideal é sempre fazer o backup em um disco local pelo fato de que seu backup será mais rápido e, com isso, o tempo que seu servidor será afetado pela rotina de backup será menor.

Quando você precisa armazenar o backup remotamente (o que é bem interessante) normalmente fazemos o backup localmente e depois copiamos o mesmo para outro local, mas nem sempre é possível fazer isso.

Nesse caso, você pode sim fazer o backup em um disco removível ou pasta de rede, que obviamente será mais demorado, mas veremos mais adiante que isso pode afetar seu backup de uma forma que você não havia pensado.

Estas duas abordagens para backup são normalmente referenciadas como Disk-To-Disk (D2D), que significa “de disco para disco”, e Disk-To-Disk-To-Tape (D2D2T), que significa “de disco para disco e então para fita”.

obs: caso você tenha alguma rotina de cópia de arquivo de backup para uma pasta remota, é bom averiguar um bug do Windows que causa um erro de “A significant part of sql server process memory has been paged out” quando arquivos grandes são copiados para uma pasta remota.

Há uma correção para ele: kb971442 (esse é o kb para o Windows Server 2008, mas sei que esse ocorre também em outras versões do SO).

2. Trace Flags

Algumas vezes é necessário alterar o comportamento do SQL Server devido a alguma situação específica. Para isso existem os trace flags. Segue abaixo uma breve explicação de alguns trace flags que serão utilizados neste artigo, para melhor entendimento das partes em seguida:

Trace Flag 3004: exibe o que as operações de backup/restore estão executando.
Trace Flag 3213: exibe informações da configuração do Buffer de backup.
Trace Flag 3014: exibe informação adicional sobre operações de Backup e arquivos.
Trace flag 3605: força a saída de outros trace flags para o error log.

3.  Buffercount e MaxTransferSize

No comando de backup podemos configurar uma série de opções que nem sempre recebem a devida importância. Abaixo coloco a descrição de duas delas, retiradas do books online

BufferCount: especifica o número total de buffers de I/O que serão utilizados na operação de backup. Você pode especificar qualquer inteiro positivo.

MaxTransferSize: especifica a maior unidade de transferência, em bytes, que será utilizada entre o SQL Server e a mídia de backup. Os valores devem ser múltiplos de 65536 bytes (64 KB) até 4194304 bytes (4 MB).

Bem, então o que temos aqui? Uma opção que me diz quantos buffers o SQL Server vai utilizar para realizar a operação de backup e outra que define o tamanho dos “pacotes” de dados que o SQL Server irá utilizar durante o backup.

Então vamos analisar como podemos utilizar estas opções para melhorar a performance de nossos backups.

O primeiro pensamento provavelmente seria “ah, vou aumentar tudo para valores bem altos ai o SQL Server vai fazer o backup super rápido”. Não é bem assim, por alguns fatores. Primeiro essa operação consome memória e isso pode afetar (e muito!) o seu servidor, então não adianta dizer ao SQL Server que consuma toda memória para o backup e os demais processos sejam prejudicados. Além disso, o tamanho do “pacote” que o SQL Server pode transferir para a mídia tem um limite e, nem sempre a mídia pode suportar o tamanho que você especificar.

Vamos então analisar os fatores que podem nos ajudar a definir um bom valor para estas duas variáveis.

Pela explicação anterior, podemos concluir então que:

Espaço Total de buffer = Max transfer size * buffer count

ou seja, o total de memória consumida pela operação de backup será esta. Um ponto MUITO importante muitas vezes nem é a QUANTIDADE de memória que será utilizada, mas sim DE ONDE ela será utilizada.

Para realizar este processo são utilizados setores CONTÍNUOS de buffers de memória do non-Puffer Pool (MemtoLeave). Como sabemos esta região de memória é bem menor do que o buffer de dados do SQL Server, principalmente se você estiver em um ambiente 32 bits! Caso você aumente demais os valores das duas variáveis, você pode terminar recebendo erros de “Out of Memory” (falta de memória) e o seu backup falhar.

Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Msg 701, Level 17, State 17, Line 1
There is insufficient system memory to run this query.
Backup/Restore buffer configuration parameters

Isso pode terminar confundindo alguns, por não saberem que é a MemtoLeave que é utilizada para o backup, vão se questionar sobre o porquê de seu backup falhar por falta de memória se há memória “sobrando” no servidor. Outro ponto importante é que pode até haver espaço suficiente na MemtoLeave, mas se ela estiver muito fragmentada pode haver o mesmo erro, já que precisamos de espaços contínuos na mesma. Caso o SQL Server não consiga um espaço contínuo na MemtoLeave grande o suficiente para realizar o backup, ele pode realizar uma tentativa de diminuir o tamanho dos buffers e gravar uma mensagem similar a esta no log de erros.

Downgrading backup buffers from 960K to 64K

obs: essa mensagem era do SQL Server 2000, não sei se continua a mesma!

Já sabemos então que devemos ter cuidado ao definir estes valores, mas e caso nós não definirmos estes valores, como eles são configurados?

Esses valores quando não especificados são definidos de acordo com a tabela abaixo, dependendo do tipo de mídia onde será feito o backup:

MaxBufferSize:

Disk  Há diferentes valores padrão para operações de leitura e escrita. 

  • Se o dispositivo estiver em modo de leitura(restore) ou for uma edição Desktop ou Express o valor é 64K
  • Se o dispositivo estiver em modo de escrita (backup) e não for edição Desktop e não for Express o valor é 1MB  (o maior valor de escrita reduz os custos de extensão de arquivo e a manutenção de metadata de NTFS)
Tape  Backup ou Restore utilizam 64K por padrão a não ser que o block size na mídia de fita indique um tamanho diferente deva ser utilizado.
VDI Backup ou Restore utilizam 64K por padrão

BufferCount

Disk Padrão é 3
Tape Padrão é 1
VDI Padrão é 1 e 4 é o máximo baseado nas necessidades de spanning

Outro detalhe é que o número de devices também interfere na quantidade padrão de BufferCount de acordo com a tabela abaixo:

Backup Database bufferCount += backupDeviceCountbufferCount += 2 * databaseDeviceCount
Restore Database or  Restore File bufferCount += 2 * databaseDeviceCount
Backup Log or Restore Log bufferCount += 2 * backupDeviceCount

Há um artigo do Bob Dorr que explica isso com bem mais detalhes, sugiro que leiam ele:

http://blogs.msdn.com/b/psssql/archive/2008/02/06/how-it-works-how-does-sql-server-backup-and-restore-select-transfer-sizes.aspx

Caso deseje, você pode verificar o número de Buffers e o TransferSize habilitando os Trace Flags 3605 e 3213, explicados anteriormente.

A saída destes flags seria algo como a figura abaixo:

Trace flags 3605 e 3213

Encontrei um KB que sugere também uma forma de calcular o valor para o BufferCount, coloco aqui também como referência:

SQL Server 2005 and later versions:
NumberofBackupDevices * 3 + NumberofBackupDevices + (2 *NumberofVolumesInvolved)

SQL Server 2000:
NumberofBackupDevices * 3 + NumberofBackupDevices + (NumberofVolumesInvolved)

Esse valor é arredondado para o múltiplo de 2 mais próximo.

http://support.microsoft.com/kb/904804/en-us

4. Blocksize

Um outro ponto que devemos levar em consideração na tentativa de melhorar a performance de nossos backups é o Blocksize. Segundo o books online este pode ser definido por:

Especifica o tamanho do bloco físico, em bytes. De forma geral, esta opção não é necessária, já que o comando BACKUP automaticamente seleciona um tamanho de bloco que é apropriado para o disco ou dispositivo de fita. Definindo explicitamente um block size sobrescreve a seleção automática. 65,536 (64 KB) é o tamanho máximo de block size que o SQL Server suporta.

Este tamanho do Blocksize pode variar de 512 bytes até 64KB e basicamente é o tamanho que o SQL Server usa para ler e escrever dados no Buffer de backup (definido pelo MaxTransferSize).

Temos que levar em contato então o tamanho do Blocksize em relação ao tamanho dos buffers que serão criados e também em relação a mídia de backup que será utilizada. Por exemplo, ao realizar backup para um caminho de rede, temos que considerar o tamanho do blocksize em relação ao pacote de rede.

No caso específico de escrita em redes, temos que lembrar que ele é importante em 2 momentos. Na área de preencher o pacote de rede, onde pode ser necessário um ou vários blocks para encher o pacote, e na hora da escrita, onde um pacote pode ser suficiente para encher o buffer de escrita, ou pode ser necessário esperar por vários pacotes (se for usar JumboFrames lembre de ajustar seu blocksize!).

5. Quantidade de arquivos de destino do backup

Antes de comentar esse ponto, vamos entender melhor o processo de backup.

Neste processo temos os readers (leitores) e os writers (escritores), onde o primeiro lê os dados do arquivo do banco de dados e o segundo escreve no device de destino do backup.

Temos também 2 filas, a de buffers livres (free) e a de buffers com dados (data). O leitor obtém um buffer livre da fila de buffers livres, preenche o mesmo com dados e o coloca na fila de buffers com dados. O writter obtém um buffer com dados da fila, processa o buffer e o envia de volta a fila de buffers livres.

Considerando então que, como vimos anteriormente, caso você não especifique um valor para BufferCount, o SQL Server irá calcular um valor baseado em vários fatores, entre eles a quantidade de arquivos de saída, esta definição se torna importante. Além disto, caso você possua arquivos de saída distribuídos por discos DIFERENTES, a performance pode ser aumentada, já que as escritas não serão concorrentes entre si no disco.

Um ponto interessante aqui é que, como normalmente temos mais writers que readers, os readers utilizam I/O assíncrono para manter o ritmo dos writers.

Readers e Writers

6. Origem/Destino dos backups

Outro ponto importante é a origem e destino dos arquivos de backups. Se você faz o seu backup no mesmo disco (físico! não importa a unidade lógica), a cabeça de leitura terá de se movimentar de forma concorrente para as leituras e escritas, então seus readers e writers não terão a performance desejada.

7. Analisando e melhorando a performance dos backups

Bem, agora que sabemos que opções podemos utilizar para otimizar nossos backups e como elas se comportam por default, podemos começar a analisa-las mais profundamente.

Para analisarmos a performance de nossos backups precisamos primeiro analisar qual a capacidade de nosso sistema, para então termos uma noção de quão bem estes estão sendo utilizados.

Para realizar tal análise, vamos utilizar um dispositivo de backup que nos permite obter valores com o mínimo de influência de variáveis do ambiente, o dispositivo NUL.

Mas o que seria o dispositivo NUL? Bem, desde a época do DOS temos uma série de dispositivos que podem ser tratados como arquivos, como LPT1, COM1 e CON. NUL é um dispositivo como estes (também chamados as vezes de arquivos virtuais) e é equivalente ao \dev\nul que vemos no UNIX. Resumindo, seria um “arquivo” que apaga qualquer dado escrito nele.

Então aonde o NUL pode nos ajudar em nossa análise?  Ao tentar configurar um ambiente para melhor performance de backup, você deve antes tentar descobrir os limites teóricos de seu ambiente, para depois analisar se seus backups estão obtendo valores próximos a eles. É nessa hora que utilizamos o NUL, pois com este conseguimos realizar backups sem “interferências” do ambiente e ter essa idéia do limite do ambiente.

O método então é realizar uma série de backups para o NUL, variando os valores de BUFFERCOUNT, MAXTRANSFERSIZE e BLOCKSIZE para termos uma base de dados de análise. O que veremos é que inicialmente os valores irão melhorar, até chegar um ponto em que a vazão do disco continuará a mesma, mas a latência ira aumentar.

Aqui teremos uma noção também de que recurso está limitando a performance. Se a CPU chegar a 100%, sabemos então que ela é o nosso limitador. Se a CPU se mantiver abaixo de 100%, a leitura de disco é o seu limitador.

Após esses testes com o device NUL, você pode então testar seu backup no dispositivo real, variando as mesmas configurações e, se não obter valores semelhantes, saberá que a escrita no mesmo é o seu limitador (inicie seus testes pela configuração que deu o melhor resultado com o device NUL).

Ao fazer os testes no device de escrita real, varie também o número de arquivos físicos de destino, principalmente se estes estiverem em discos distintos, para tentar fugir das limitações de escrita.

8. Resultados

Abaixo coloco tabelas com os resultados de alguns testes que realizei em um ambiente de testes. Não darei a descrição do ambiente, pois este não deve servir de motivo de comparação e sim apenas para ilustração do método.

Veja que, de acordo com os resultados obtidos, não basta apenas aumentar os valores das opções apresentadas para obter os melhores resultados, devemos testar em nosso ambiente e encontrar as melhores configurações e os melhores resultados para ele.

O ambiente para os testes era um servidor SQL Server 2005 SP4 x64 Enterprise Edition em Windows Server 2008 x64 (virtualizado!) e o banco de dados utilizado tinha cerca de 4.5GB.

Abaixo o comando utilizado para os backups:

BACKUP DATABASE [Banco]
TO
DISK = 'U:\Teste1.bak' --, DISK = 'U:\Teste2.bak', DISK = 'U:\Teste3.bak', DISK = 'U:\Teste4.bak' | \\Caminho_de_Rede\Teste.bak
WITH STATS = 10, INIT, FORMAT,
MAXTRANSFERSIZE = 4194304, --65536, 131072, 262144, 524288, 1048576, 2097152, 4194304
BUFFERCOUNT = 64, --1, 2, 4, 8, 16, 32, 64
BLOCKSIZE = 65536  --512, 1024, 2048, 4096, 8192, 16384, 32768, 65536

Backup no device NUL

TEMPO Throughput (MB/s) MAXTRANSFERSIZE BUFFERCOUNT BLOCKSIZE Backup Devices
52.5 89.2

65536

1

512

N/A

32.8 142.7

524288

1

512

N/A

27.9 167.6

4194304

1

512

N/A

29.5 158.4

65536

8

512

N/A

18.5 252.6

65536

64

512

N/A

35.0 133.5

65536

1

4096

N/A

34.1 137.0

65536

1

65536

N/A

24.8 188.8

524288

8

4096

N/A

19.5 239.5

4194304

8

4096

N/A

18.5 251.8

4194304

64

4096

N/A

17.3 270.3

4194304

64

65536

N/A

Veja que aqui conseguimos um pico de 270 MB/s o que, em teoria, deve ser nosso limite máximo de performance.

Backup em compartilhamento de rede

TEMPO Throughput (MB/s) MAXTRANSFERSIZE BUFFERCOUNT BLOCKSIZE Backup Devices
210.8 22.2

65536

1

512

1

267.2 17.5

524288

1

512

1

138.4 33.8

4194304

1

512

1

125.7 37.2

65536

8

512

1

128.1 36.5

65536

64

512

1

129.8 36.0

65536

1

4096

1

154.0 30.4

65536

1

65536

1

219.4 21.3

524288

8

4096

1

114.2 41.0

4194304

8

4096

1

114.3 40.9

4194304

64

4096

1

111.8 41.8

4194304

64

65536

1

Veja que aqui nosso performance diminuiu BASTANTE, mesmo tentando diversas opções de configuração. Neste ambiente não havia Jumboframes.

Podemos perceber também que nesse caso, a variação do BLOCKSIZE teve um impacto importante em algumas situações. Se compararmos o primeiro, o sexto e o sétimo resultados, vemos que, com as demais opções configuradas igualmente, o tamanho do bloco fez uma diferença bastante razoável (o pacote desta rede é de 4KB).

Backup em disco

TEMPO Throughput (MB/s) MAXTRANSFERSIZE BUFFERCOUNT BLOCKSIZE Backup Devices
234.5 19.9

65536

1

512

1

210.9 22.2

524288

1

512

1

216.0 21.6

4194304

1

512

1

209.6 22.3

65536

8

512

1

182.9 25.6

65536

64

512

1

222.1 21.0

65536

1

4096

1

219.7 21.3

65536

1

65536

1

185.5 25.2

524288

8

4096

1

165.8 28.2

4194304

8

4096

1

158.7 29.5

4194304

64

4096

1

155.9 30.0

4194304

64

65536

1

143.6 32.6

4194304

64

65536

2

166.5 28.1

4194304

64

65536

4

Veja que aqui também não conseguimos um desempenho próximo ao nosso limite teórico, apesar de que eu fui obrigado a fazer o backup no mesmo RAID onde estava sendo feito a leitura (onde estava o banco de dados), mas veja que mesmo assim o desempenho muda bastante dependendo da configuração utilizada.

O aumento do número de devices influenciou positivamente até certo ponto apenas, principalmente pelo fato de que estavam todos no mesmo RAID de destino.

Podemos perceber também que aqui os fatores mais importantes foram MAXTRANSFERSIZE e BUFFERCOUNT, principalmente quando combinados em valores mais altos. Acredito que estes poderiam ter um impacto ainda maior, como já observei em ambiente de produção.

9. Extended Events

Por fim, eu não poderia deixar de citar o uso de Extended Events (SQL Server 2008 em diante) para obter informações sobre os backups.

O Jonathan Kehayias postou duas entradas em seu blog demonstrando o uso do XEvents para monitorar todo o processo de backup e até medir o throughput do processo.

Se você deseja entender melhor o processo de backup e ver como utilizar os XEvents para monitorar o processo, recomendo a leitura desses dois posts.

http://www.sqlskills.com/blogs/jonathan/post/An-XEvent-a-Day-(17-of-31)-A-Look-at-Backup-Internals-and-How-to-Track-Backup-and-Restore-Throughput-(Part-1).aspx

http://www.sqlskills.com/blogs/jonathan/post/An-XEvent-a-Day-(18-of-31)-A-Look-at-Backup-Internals-and-How-to-Track-Backup-and-Restore-Throughput-(Part-2).aspx

Segue o comando para criar e ativar a sessão de XEvents para monitorar os eventos de backup:

–Criar a sessão

* veja que há o database_id e session_id fixos para algumas partes do comando, você deve ajustar esses valores para o database_id do banco que deseja monitorar e da sessão realizando o backup

** no ambiente utilizado pelo Jonathan Kehayias, ele ativou os trace flags 3004, 3213 e 3014 antes de executar os backups. Comentei sobre estes trace flags em uma seção anterior deste artigo.

CREATE EVENT SESSION BackupMonitoring
ON SERVER
ADD EVENT sqlserver.sql_statement_starting
(   ACTION (sqlserver.database_id, sqlserver.sql_text)
WHERE (sqlserver.session_id = 97)),
ADD EVENT sqlserver.sql_statement_completed
(   ACTION (sqlserver.database_id, sqlserver.sql_text)
WHERE (sqlserver.session_id = 97)),
ADD EVENT sqlserver.databases_backup_restore_throughput
(   WHERE (sqlserver.session_id = 97)),
ADD EVENT sqlos.wait_info
(   ACTION (sqlserver.database_id)
WHERE (sqlserver.session_id = 97  AND duration > 0)),
ADD EVENT sqlos.wait_info_external
(   ACTION (sqlserver.database_id)
WHERE (sqlserver.session_id = 97  AND duration > 0)),
ADD EVENT sqlserver.trace_print
(   WHERE (sqlserver.session_id = 97)),
ADD EVENT sqlserver.file_read
(   WHERE (sqlserver.session_id = 97)),
ADD EVENT sqlserver.file_read_completed
(   WHERE (sqlserver.session_id = 97)),
ADD EVENT sqlserver.physical_page_read
(   WHERE (sqlserver.session_id = 97)),
ADD EVENT sqlserver.databases_log_cache_read
(   WHERE (database_id = 41)),
ADD EVENT sqlserver.databases_log_cache_hit
(   WHERE (database_id = 41)),
ADD EVENT sqlserver.databases_log_flush
(   WHERE (database_id = 41)),
ADD EVENT sqlserver.checkpoint_begin
(   WHERE (database_id = 41)),
ADD EVENT sqlserver.checkpoint_end
(   WHERE (database_id = 41))
ADD TARGET package0.asynchronous_file_target(
SET filename=’C:\SQLBlog\BackupMonitoring1.xel’,
metadatafile = ‘C:\SQLBlog\BackupMonitoring1.xem’)
GO

— Iniciar a sessão
ALTER EVENT SESSION BackupMonitoring
ON SERVER
STATE=START
GO

Após realizar o backup (preferencialmente, sem outras operações ocorrendo no servidor), utilize o seguinte comando para obter os dados capturados pela sessão de XEvents.

DROP TABLE #EventData
DROP TABLE #TestResults

— Criar tabelas temporárias intermediárias para os dados dos eventos
CREATE TABLE #EventData
(Rowid INT IDENTITY PRIMARY KEY, event_data XML)

— Criar tabela para os dados de resultado final já compilado
CREATE TABLE #TestResults
(Rowid INT PRIMARY KEY, event_name VARCHAR(50), package_name VARCHAR(50),
[timestamp] datetime2, database_id INT, trace_print NVARCHAR(4000),
[count] bigint, increment bigint, wait_type NVARCHAR(100), opcode NVARCHAR(10),
duration bigint, max_duration bigint, total_duration bigint, signal_duration bigint,
completed_count bigint, source_database_id INT, [object_id] INT, object_type INT,
[state] NVARCHAR(50), offset bigint, offset_end INT, nest_level INT, cpu INT,
reads bigint, writes bigint, mode NVARCHAR(50), FILE_ID INT, page_id INT,
file_group_id INT, sql_text NVARCHAR(4000))

— Inserir os dados na tabela temporária intermediária
INSERT INTO #EventData (event_data)
SELECT
CAST(event_data AS XML) AS event_data
FROM sys.fn_xe_file_target_read_file(‘C:\SQLBlog\BackupMonitoring1*.xel’,’C:\SQLBlog\BackupMonitoring1*xem’, NULL, NULL)

— Inserir os dados na tabela final
INSERT INTO #TestResults
(Rowid, event_name, package_name, [timestamp], database_id, trace_print,
[count], increment, wait_type, opcode, duration, max_duration, total_duration,
signal_duration, completed_count, source_database_id, [object_id], object_type,
[state], offset, offset_end, nest_level, cpu,  reads, writes, mode, FILE_ID,
page_id, file_group_id, sql_text)

SELECT
RowID,
event_data.value(‘(event/@name)[1]’, ‘varchar(50)’) AS event_name,
event_data.value(‘(event/@package)[1]’, ‘varchar(50)’) AS package_name,
DATEADD(hh,
DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
event_data.value(‘(event/@timestamp)[1]’, ‘datetime2’)) AS [timestamp],
COALESCE(event_data.value(‘(event/data[@name=”database_id”]/value)[1]’, ‘int’),
event_data.value(‘(event/action[@name=”database_id”]/value)[1]’, ‘int’)) AS database_id,
event_data.value(‘(event/data[@name=”message”]/value)[1]’, ‘nvarchar(4000)’) AS trace_print,
event_data.value(‘(event/data[@name=”count”]/value)[1]’, ‘bigint’)  AS [count],
event_data.value(‘(event/data[@name=”increment”]/value)[1]’, ‘bigint’)  AS [increment],
event_data.value(‘(event/data[@name=”wait_type”]/text)[1]’, ‘nvarchar(100)’) AS wait_type,
event_data.value(‘(event/data[@name=”opcode”]/text)[1]’, ‘nvarchar(10)’) AS opcode,
event_data.value(‘(event/data[@name=”duration”]/value)[1]’, ‘bigint’)  AS duration,
event_data.value(‘(event/data[@name=”max_duration”]/value)[1]’, ‘bigint’)  AS max_duration,
event_data.value(‘(event/data[@name=”total_duration”]/value)[1]’, ‘bigint’)  AS total_duration,
event_data.value(‘(event/data[@name=”signal_duration”]/value)[1]’, ‘bigint’)  AS signal_duration,
event_data.value(‘(event/data[@name=”completed_count”]/value)[1]’, ‘bigint’)  AS completed_count,
event_data.value(‘(event/data[@name=”source_database_id”]/value)[1]’, ‘int’)  AS source_database_id,
event_data.value(‘(event/data[@name=”object_id”]/value)[1]’, ‘int’)  AS OBJECT_ID,
event_data.value(‘(event/data[@name=”object_type”]/value)[1]’, ‘int’)  AS object_type,
event_data.value(‘(event/data[@name=”state”]/text)[1]’, ‘nvarchar(50)’) AS state,
event_data.value(‘(event/data[@name=”offset”]/value)[1]’, ‘bigint’)  AS offset,
event_data.value(‘(event/data[@name=”offset_end”]/value)[1]’, ‘int’)  AS offset_end,
event_data.value(‘(event/data[@name=”nest_level”]/value)[1]’, ‘int’)  AS nest_level,
event_data.value(‘(event/data[@name=”cpu”]/value)[1]’, ‘int’)  AS cpu,
event_data.value(‘(event/data[@name=”reads”]/value)[1]’, ‘bigint’)  AS reads,
event_data.value(‘(event/data[@name=”writes”]/value)[1]’, ‘bigint’)  AS writes,
event_data.value(‘(event/data[@name=”mode”]/text)[1]’, ‘nvarchar(50)’) AS mmode,
event_data.value(‘(event/data[@name=”file_id”]/value)[1]’, ‘int’)  AS FILE_ID,
event_data.value(‘(event/data[@name=”page_id”]/value)[1]’, ‘int’)  AS page_id,
event_data.value(‘(event/data[@name=”file_group_id”]/value)[1]’, ‘int’)  AS file_group_id,
event_data.value(‘(event/action[@name=”sql_text”]/value)[1]’, ‘nvarchar(4000)’) AS sql_text
FROM #EventData
ORDER BY Rowid

— Ver os resultados
SELECT
Rowid,
event_name,
database_id,
trace_print,
[count],
increment,
wait_type,
duration,
signal_duration,
cpu,
reads,
writes,
mode,
FILE_ID,
page_id,
file_group_id,
sql_text
FROM #TestResults
ORDER BY Rowid

Aproveito para colocar aqui uma tabela de resultados dos testes dele, como mais uma referência para a análise das N variáveis que temos nesse processo.

Test Number Backup File Count Buffer Count Max Transfer Size Backup Time (s) BACKUPBUFFER (wait ms) BACKUPIO (wait ms) BACKUPBUFFER (wait count) BACKUPIO (wait count)
1 1 7 1024 122.5 159471 62587 81616 22815
2 1 16 4096 105.2 90963 69091 14513 7982
3 1 32 4096 99.5 75236 88634 12298 8679
4 1 128 4096 95.9 70173 63435 8292 4679
5 1 256 4096 95.9 50988 48942 1538 1135
6 2 128 4096 96 152323 63800 12416 4925
7 2 256 4096 96.4 109565 46953 3067 1195

10. Referências

http://blogs.msdn.com/b/psssql/archive/2008/02/06/how-it-works-how-does-sql-server-backup-and-restore-select-transfer-sizes.aspx

http://support.microsoft.com/kb/904804/en-us

http://sql-articles.com/articles/dba/how-much-memory-is-needed-taken-for-my-database-backup/

http://henkvandervalk.com/how-to-increase-sql-database-full-backup-speed-using-compression-and-solid-state-disks

http://blogs.msdn.com/b/psssql/archive/2008/01/28/how-it-works-sql-server-backup-buffer-exchange-a-vdi-focus.aspx

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/06/incorrect-buffercount-data-transfer-option-can-lead-to-oom-condition.aspx

http://sqlblog.com/blogs/linchi_shea/archive/2007/03/30/sql-server-backup-i-o-performance.aspx

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/01/08/backup-database-5x-quicker-using-blocksize-option.aspx

http://sqlcat.com/whitepapers/archive/2009/08/13/a-technical-case-study-fast-and-reliable-backup-and-restore-of-a-vldb-over-the-network.aspx

http://sqlinthewild.co.za/index.php/2009/08/31/backing-up-to-nul-vs-backup-with-truncate-only/

http://www.r71.nl/kb/technical/186-disk-performance-and-backup-to-nul-command

http://www.sqlskills.com/blogs/jonathan/post/An-XEvent-a-Day-(17-of-31)-A-Look-at-Backup-Internals-and-How-to-Track-Backup-and-Restore-Throughput-(Part-1).aspx

http://www.sqlskills.com/blogs/jonathan/post/An-XEvent-a-Day-(18-of-31)-A-Look-at-Backup-Internals-and-How-to-Track-Backup-and-Restore-Throughput-(Part-2).aspx

http://sqlcat.com/technicalnotes/archive/2008/04/21/tuning-the-performance-of-backup-compression-in-sql-server-2008.aspx

http://sqlcat.com/technicalnotes/archive/2009/02/16/tuning-backup-compression-part-2.aspx

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/05/06/incorrect-buffercount-data-transfer-option-can-lead-to-oom-condition.aspx

http://blogs.msdn.com/b/psssql/archive/2008/01/23/how-it-works-what-is-restore-backup-doing.aspx


Esse post foi publicado em Artigos, Virtual PASS BR. Bookmark o link permanente.

11 respostas para Entendendo e Melhorando a performance de seus backups

  1. Paulo Martim disse:

    Excelente artigo, me ajudou muito.

    Parabéns.

    Att,
    Paulo Martim

  2. Marilia Barros disse:

    Excelente artigo, bem satisfatorio e didatico!!

  3. Pingback: DPM 2012 – Cuidado com o espaço no seu disco de log! | Vladimir M. B. Magalhães – Learn and Share

  4. Pingback: DPM 2012 – Be careful with the free space on your log disk! | Vladimir M. B. Magalhães – Learn and Share

  5. Pingback: Entendendo e Melhorando seus backups (SQL Server) | Alex Souza

  6. Pingback: [SQL Server Backup Internals] – Backup Full | Edvaldo Castro

  7. Pingback: [SQL Server Backup Internals] – Backup Full | EDVALDO CASTRO - DBA

  8. Pingback: [PT-BR] [SQL Server Backup Internals] – Backup Full | Edvaldo Castro

  9. Parabéms Vlad! Artigo muito bom🙂

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s