Análise de backups

Introdução

Muitas vezes temos problemas com backups, mas na verdade o problema não é a operação de backup em sí ou o SQL Server. O grande vilão dos backups é o espaço reservado para esta operação.

Para um maior sucesso nestas operações, temos que levar em conta alguns factores, como tempo de retenção e o crescimento da base de dados.

Sobre o Script

O script que trago hoje ajuda neste trabalho, trazendo informações historiais sobre o tamanho do ficheiro de backup, o tempo que o backup leva para completar e é ainda o possível estimar o tamanho do backup, caso a opção de backup compression esteja activa.

Este script tem várias utilidades, e uma delas é verificar um padrão de crescimento do backup das bases de dados, o que permite prever as necessidades de disco no futuro. Outra possibilidades é o cálculo do tempo necessário estimado para a janela de backups, além de ser possível verificar se a activação do backup compression pode trazer ou não benefícios, em termos de espaço em disco.


SELECT TOP 30 database_name, 
              CASE
                WHEN type = 'D' THEN 'Full Backup'
                WHEN type = 'L' THEN 'Log Backup'
                WHEN type = 'D' THEN 'Database'
                WHEN type = 'I' THEN 'Differential database'
                WHEN type = 'L' THEN 'Log'
                WHEN type = 'F' THEN ' File or filegroup'
                WHEN type = 'G' THEN 'Differential file'
                WHEN type = 'P' THEN 'Partial'
                WHEN type = 'Q' THEN 'Differential partial'
              END
              AS backup_type, 
              backup_start_date, 
              backup_finish_date, 
              CONVERT(CHAR(5), backup_finish_date - backup_start_date, 108) 
              AS elapsed_time, 
              Cast(( backup_size / 1024 / 1024 ) AS DECIMAL(38, 2)) 
              AS backup_size_in_mb, 
              Cast(( compressed_backup_size / 1024 / 1024 ) AS DECIMAL(38, 2)) 
              AS
              compressed_backup_size_in_mb 
FROM   msdb..backupset 
ORDER  BY database_name, 
          backup_set_id DESC
Para fazer download do script clique aqui.

Sumário

Neste artigo foi passado uma forma de analisar o histórico de backups, de forma a recolher dados importantes para se traçar uma boa estratégia de backups.

Partition Offset e Cluster Size – Como verificar?

Boa tarde!

Várias vezes referi a importância de termos o offset das partições e o tamanho do cluster bem definidos.

Para uma performance óptima, do lado do SQL Server, temos que assegurar que o offset da partição é de 1024 Kb (ou 1048576 Bytes). Já no caso do cluster, o valor a se ter em conta é 64Kb.

O ideal é verificar estes valores antes da instalação do SQL Server, pois o “remédio”é reformatar os disco.

Como verificar estes valores?

Verificar o offset de todas as partições:

Execute na command line o seguinte:
wmic partition get BlockSize, StartingOffset, Name, Index

O output será algo como o seguinte:
BlockSize  Index  Name                   StartingOffset
512        0      Disk #4, Partition #0  1048576
512        0      Disk #5, Partition #0  135266304
512        1      Disk #5, Partition #1  17408
512        0      Disk #3, Partition #0  1048576
512        0      Disk #1, Partition #0  1048576
512        1      Disk #1, Partition #1  105906176
512        2      Disk #1, Partition #2  52428800000
512        0      Disk #2, Partition #0  1048576
512        0      Disk #0, Partition #0  1048576

A coluna StartingOffset devolve os valores que queremos. Para relacionar os discos com uma letra, verifique o valor na coluna “Name”, e compare o número do disco com o valor descrito na tool “Disk Management” do Windows.

Verificar o tamanho da allocation unit:

Execute na command line o seguinte:
fsutil fsinfo ntfsinfo <drive letter>
Exemplo:

fsutil fsinfo ntfsinfo f:

O output será equivalente ao seguinte:
NTFS Volume Serial Number :       0x66ac6ab3ac6a7d85
Version :                         3.1
Number Sectors :                  0x0000000055701fff
Total Clusters :                  0x000000000aae03ff
Free Clusters  :                  0x000000000963a5c8
Total Reserved :                  0×0000000000000000
Bytes Per Sector  :               512
Bytes Per Cluster :               4096
Bytes Per FileRecord Segment    : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length :           0×0000000000140000
Mft Start Lcn  :                  0x00000000000c0000
Mft2 Start Lcn :                  0×0000000000000002
Mft Zone Start :                  0x00000000000c0140
Mft Zone End   :                  0x00000000000cc940
RM Identifier:        BE2060B1-AFBC-11DF-9B68-1CC1DE750143

A informação a reter aqui é “Bytes Per Cluster”.  Neste caso o disco está formatado com blocos de 4Kb. O está longe dos 64Kb recomendados.


A verificação destes pontos é muito importante e, acredite, isso influencia na performance do SQL Server, principalmente se o offset estiver mal definido.

Até a próxima!