Otimizando o desempenho de backup e restauração em um SQL Server
O Microsoft SQL Server oferece duas maneiras para acelerar as operações de backup e restauração:
A utilização de diversos dispositivos de backup permite que os backups sejam gravados em todos os dispositivos em paralelo. A velocidade do dispositivo de backup é um gargalo potencial na taxa de transferência de backup. A utilização de diversos dispositivos pode aumentar a taxa de transferência proporcionalmente ao número de dispositivos utilizados. Da mesma forma, o backup pode ser restaurado a partir de vários dispositivos em paralelo. Para obter mais informações, consulte "Utilizando diversas mídias ou dispositivos" mais adiante neste tópico.
Utilizando uma combinação de backups completos, diferenciais e (para o modelo de recuperação completo ou de operações em massa) de log de transações para minimizar o tempo de recuperação. Normalmente, criar backups de banco de dados diferenciais é mais rápido do que criar backups de banco de dados completos, o que reduz o número de log de transações necessário para recuperar o banco de dados. Para obter mais informações, consulte Criando backups completos e diferenciais de um banco de dados do SQL Server.
Utilizando diversas mídias ou dispositivos
A cópia dos dados e log de transações dos dispositivos de backup para o banco de dados e arquivos de log de transações é realizada por meio de threads de leitura/gravação; um thread é atribuído a cada dispositivo de backup. O desempenho é limitado pela capacidade dos dispositivos de backup de entregar os dados ou pela capacidade do banco de dados e dos arquivos de log de transações de aceitar os dados. Portanto, o desempenho aumenta com o número de dispositivos de backup, até que a taxa de transferência máxima do banco de dados ou o máximo de dados aceito pelos arquivos de log de transações seja atingido.
A utilização de diversos dispositivos para as operações de backup e restauração permite que o SQL Server utilize E/S paralelas para aumentar a velocidade dessas operações porque cada dispositivo de backup pode ser gravado ou lido ao mesmo tempo que outros dispositivos de backup. Para empresas com bancos de dados grandes, a utilização de vários dispositivos de backup pode reduzir bastante o tempo necessário para operações de backup e restauração. O SQL Server oferece suporte a no máximo 64 dispositivos de backup para uma única operação de backup.
Enquanto um backup está sendo gravado em diversos dispositivos de backup, ocorrem vários pontos de sincronização internos. O ponto mais importante ocorre quando já foi feito o backup de todos os dados no banco de dados e será iniciado o backup do log de transações.
.gif "Observação importante") Importante |
|---|
Quando são utilizados diversos dispositivos de backup para executar operações de backup, as mídias de backup envolvidas só podem ser utilizadas em operações de backup do SQL Server. Para obter mais informações, consulte Usando mídia de backup. |
Criar e restaurar backups ao utilizar diversos dispositivos de backup é igual a criar e restaurar backups utilizando um único dispositivo. A única diferença é que você deve especificar todos os dispositivos de backup envolvidos na operação e não apenas um. Por exemplo, se estiver sendo criado um backup de banco de dados que utilize três dispositivos de backup em fita, como \\.\TAPE0, \\.\TAPE1 e \\.\TAPE2, cada um dos dispositivos de fita deverá ser especificado como parte da operação de backup, embora possam ser utilizados menos dispositivos de fita quando você restaura o backup.
Quando você cria um backup em diversos dispositivos de backup utilizando mídias removíveis, os dispositivos podem operar a velocidades diferentes e os volumes de mídia podem ter quantidades diferentes de espaço disponível. Durante a operação de backup, se o volume de mídia em um dispositivo de backup ficar sem espaço, a operação deixará de gravar nesse dispositivo e solicitará um novo volume de mídia. Enquanto o volume de mídia cheio não for trocado por um vazio, esse dispositivo ficará bloqueado. Enquanto isso, a operação de backup continua gravando dados nos dispositivos cujas mídias ainda têm espaço disponível. Quando você substitui o volume de mídia cheio, seu dispositivo torna-se disponível e o backup começa a gravar dados nele novamente. Esteja atento, contudo, pois se ocorrer um ponto de sincronização interno enquanto um dispositivo estiver bloqueado, a operação de backup pausará completamente até que aquele dispositivo fique disponível novamente.
Exemplo
Considere um cenário em que se utilizam três dispositivos de fita para backup com velocidades iguais para armazenar um backup completo de banco de dados. As primeiras duas fitas têm 10 gigabytes (GB) de espaço disponível, mas a terceira só possui 5 GB disponíveis. Se for executado o backup de um banco de dados de 20 GB simultaneamente nos três dispositivos de fita para backup, a terceira fita encherá antes do backup terminar. Depois que 5 GB de dados forem gravados na terceira fita, a operação de backup deixará de gravar no terceiro dispositivo. A operação bloqueia esse dispositivo e solicita uma fita nova. Enquanto isso, a operação de backup continua gravando dados nos outros dois dispositivos. Porém, antes de a terceira fita ser substituída, ocorre um ponto de sincronização interno. Nesse ponto, toda a operação de backup é pausada até que uma fita nova seja instalada no terceiro dispositivo.
Otimizando o desempenho para backups completos e diferenciais
A criação de um backup completo ou diferencial consiste nos seguintes passos:
Copiar os dados dos arquivos de banco de dados nos dispositivos de backup.
Copiar a parte do log de transações necessária para fazer o roll-forward do banco de dados para um estado consistente com os mesmos dispositivos de backup.
Criar um backup diferencial é igual a criar um backup completo, exceto que somente os dados alterados são copiados. O backup de um arquivo de banco de dados consiste em simplesmente copiar os dados do arquivo para os dispositivos de backup.
Os arquivos de banco de dados utilizados para armazenar o banco de dados são classificados por um dispositivo de disco e é atribuído a cada dispositivo um thread de leitura. O thread de leitura lê os dados dos arquivos do banco de dados. Um thread de gravação é atribuído a cada dispositivo de backup. Esse thread grava os dados no dispositivo de backup. As operações de leitura em paralelo podem aumentar distribuindo-se os arquivos de banco de dados entre mais unidades lógicas. Da mesma forma, as operações de gravação em paralelo podem aumentar com a utilização de mais dispositivos de backup.
Geralmente, o gargalo serão os arquivos de banco de dados ou os dispositivos de backup. Se a taxa de transferência de leitura total for maior que a taxa de transferência total do dispositivo de backup, o gargalo estará no lado do dispositivo de backup. Adicionar mais dispositivos de backup (e controladores SCSI, caso necessário) pode melhorar o desempenho. Porém, se a taxa de transferência total de backup for maior que a taxa de transferência total de leitura, aumente a taxa de transferência de leitura, por exemplo, adicionando mais arquivos ou dispositivos de banco de dados (ou adicionando mais discos a um dispositivo RAID).
Otimizando o desempenho do backup de log de transações
A criação de um backup de log de transações envolve simplesmente copiar a porção do log que ainda não foi gravada nos dispositivos de backup. Embora possa haver vários arquivos de log de transações, o log de transações é logicamente um fluxo lido sequencialmente por um thread.
Um thread de gravação é atribuído a cada dispositivo de backup. Pode ser obtido um desempenho melhor com a adição de mais dispositivos de backup.
Um gargalo pode ser o dispositivo de disco que contém os arquivos de log de transações ou o dispositivo de backup, dependendo de sua velocidade relativa e do número de dispositivos de backup utilizados. A adição de mais dispositivos de backup aumentará linearmente até que seja alcançada a capacidade do dispositivo de disco que contém os arquivos de log de transações, depois do que não será possível nenhum ganho adicional sem aumentar a velocidade dos dispositivos de disco que contêm o log de transações, utilizando, por exemplo, a distribuição do disco.
Otimizando o desempenho de restauração
A restauração de um backup de banco de dados ou diferencial consiste em quatro passos:
Criar os arquivos do banco de dados e de log de transações, caso ainda não existam.
Copiar os dados dos dispositivos de backup nos arquivos do banco de dados.
Copiar o log de transações dos arquivos de log de transações.
Avançar o log de transações e reiniciar a recuperação, se necessário.
A aplicação de um backup de log de transações consiste em dois passos:
Copiar os dados dos dispositivos de backup no arquivo de log de transações.
Avançar o log de transações.
A restauração de um arquivo de banco de dados consiste em dois passos:
Criar arquivos de banco de dados perdidos.
Copiar os dados dos dispositivos de backup nos arquivos do banco de dados.
Inicialização do arquivo
Se os arquivos do banco de dados e do log de transações ainda não existirem, eles devem ser criados para que os dados possam ser restaurados. Os arquivos do banco de dados e do log de transações são criados e o conteúdo do arquivo, inicializado a zero. Threads de trabalho separados criam e inicializam os arquivos em paralelo. Os arquivos do banco de dados e de log de transações são classificados pelo dispositivo de disco e é atribuído um thread de trabalho separado a cada dispositivo de disco. Como a criação e a inicialização dos arquivos exigem uma alta taxa de transferência, distribuir os arquivos uniformemente nas unidades lógicas disponíveis fornece o melhor desempenho.
Inicialização imediata de arquivo
No SQL Server 2005 e versões posteriores, os arquivos de dados podem ser inicializados instantaneamente, permitindo a execução rápida das operações de restauração de banco de dados ou grupo de arquivos. A inicialização imediata de um arquivo recupera espaço em disco sem encher esse espaço com zeros. Ao contrário, o conteúdo do disco é substituído à medida que novos dados são gravados nos arquivos. A inicialização de um arquivo de log ainda requer a anulação, mas isso acontecerá em paralelo com a transferência dos dados do backup. A etapa de roll-forward da restauração não será iniciada enquanto não forem transferidos todos os dados e todo o log seja inicializado.
.gif "Observação") Observação |
|---|
A inicialização imediata de arquivo só está disponível no Microsoft Windows XP ou em sistemas Windows Server 2003 posteriores. |
Para usar a inicialização imediata de arquivo, você deve executar a conta de serviço MSSQLSERVER em uma conta do Windows e atribuir privilégio especial de Windows SE_MANAGE_VOLUME_NAME a essa conta. Por padrão, esse privilégio é atribuído ao grupo de Administradores do Windows. Se você tiver direitos de administrador do sistema, poderá atribuir esse privilégio adicionando a conta do Windows à política de segurança Executar tarefas de manutenção de volume. Para obter mais informações sobre como atribuir direitos de usuário, consulte a documentação do Windows.
Otimizando o desempenho do dispositivo de backup em fita
Diversas variáveis afetam o desempenho do dispositivo de backup em fita e permitem que o desempenho das operações de backup e restauração do SQL Server aumente quase linearmente à medida que são adicionados mais dispositivos de fita:
O tamanho do bloco de dados do software.
O número de dispositivos de fita que compartilham um barramento de interface de sistemas de computadores pequenos (SCSI).
O tipo de dispositivo de fita.
O tamanho de bloco de dados de software é computado para a obtenção de um excelente desempenho pelo SQL Server e não deve ser alterado. O BLOCKSIZE máximo é 64 KB.
Muitas unidades de fita de alta velocidade terão melhor desempenho se tiverem um barramento SCSI dedicado para cada unidade de fita utilizada. Unidades com taxa de transferência nativa acima de 50% da velocidade do barramento SCSI devem estar em um barramento SCSI dedicado para evitar perdas no desempenho. Para obter mais informações sobre configurações que afetam o desempenho da unidade de fita, consulte a documentação do fornecedor da unidade de fita.
| Importante |
|---|
Nunca coloque uma unidade de fita no mesmo barramento SCSI que unidades de disco ou de CD-ROM. As ações de tratamento de erros desses dispositivos são mutuamente incompatíveis. |
Ao executar diversas operações de backup em uma fita carregada, você pode melhorar o desempenho especificando NOREWIND. Essa opção faz com que o SQL Server mantenha a fita ou as fitas abertas depois da operação de backup. NOREWIND implica NOUNLOAD.
Otimizando o desempenho de um dispositivo de backup em disco
A velocidade bruta de E/S do dispositivo de backup em disco afeta o desempenho do dispositivo de backup e permite que o desempenho das operações de backup e restauração do SQL Server aumente quase linearmente à medida que são adicionados vários dispositivos de disco.
A utilização do RAID para um dispositivo de backup em disco precisa ser considerada cuidadosamente. Por exemplo, o RAID 5 tem baixo desempenho de gravação, aproximadamente a mesma velocidade de um único disco (pois precisa manter informações de paridade). Além disso, a velocidade bruta da anexação de dados a um arquivo é significativamente mais lenta que a velocidade bruta de gravação do dispositivo.
Se o dispositivo de backup for muito distribuído, de forma que a velocidade máxima de gravação para o dispositivo de backup exceda muito a velocidade com que ele pode adicionar dados a um arquivo, pode ser adequado colocar vários dispositivos de backup lógicos no mesmo conjunto distribuído. Em outras palavras, o desempenho de backup pode ser aumentado colocando várias famílias de mídia de backup na mesma unidade lógica. Entretanto, será necessária uma abordagem empírica para determinar se isso é um ganho ou uma perda para cada ambiente. Normalmente, é melhor colocar cada dispositivo de backup em um dispositivo de disco separado.
Geralmente, em um barramento SCSI, podem ser operados somente alguns discos na velocidade máxima, embora barramentos Ultra-wide e Ultra-2 consigam operar mais. É necessária, porém, uma configuração cuidadosa do hardware para obter um bom desempenho.
Para obter mais informações sobre configurações que afetam o desempenho do disco, consulte a documentação do fornecedor do disco.
Compactação de dados
Unidades de fita modernas têm hardware de compactação de dados interno que pode aumentar significativamente a taxa efetiva de transferência de dados da unidade. A capacidade de compactação dos dados reais no banco de dados depende dos dados e das unidades de fita usadas. As razões de compactação de dados típicas variam de 1,2:1 a 2:1 para uma ampla gama de bancos de dados. Essa razão de compactação é típica de dados em uma grande variedade de aplicativos empresariais, embora alguns bancos de dados possam ter razões de compactação maiores ou menores. Por exemplo, um banco de dados que consiste em grande parte de imagens que já estão compactadas não será compactado pelas unidades de fita. Para obter mais informações sobre compactação de dados, consulte a documentação do fornecedor da unidade de fita.
Por padrão, o SQL Server oferece suporte a hardware de compactação, embora esse procedimento possa ser desabilitado com o sinalizador de rastreamento 3205. Desabilitar o hardware de compactação pode, em circunstâncias raras, melhorar o desempenho do backup. Por exemplo, se os dados já estiverem totalmente compactados, desabilitar o hardware de compactação evita que o dispositivo de fita desperdice tempo tentando compactar ainda mais os dados.
Para obter mais informações sobre sinalizadores de rastreamento, consulte Sinalizadores de rastreamento (Transact-SQL).
Compactação de backup
Por padrão, executar backup usando a compactação de backup aumenta consideravelmente o uso de CPU e o consumo adicional da CPU por parte do processo de compactação pode afetar negativamente as operações simultâneas. Portanto, convém criar um backup compactado de baixa prioridade em uma sessão cujo uso de CPU seja limitado pelo Administrador de Recursos quando houver contenção de CPU. Para obter mais informações, consulte Como usar o Administrador de Recursos para limitar o uso de CPU por meio de compactação de backup (Transact-SQL).
Quantidade de dados transferidos para a fita
A criação de backups de dados ou diferenciais capta somente a parte do banco de dados que contém dados reais; espaço não utilizado não é gravado no backup. Isso resulta em operações de backup mais rápidas.
Embora os bancos de dados do SQL Server possam ser configurado para crescer automaticamente de acordo com a necessidade, você pode continuar reservando espaço no banco de dados para garantir que esse espaço esteja disponível. A reserva de espaço dentro do banco de dados não afeta negativamente a taxa de transferência ou o tempo total necessário para fazer backup do banco de dados.
Otimizando a sincronização do envio de log
Ao tentar sincronizar um destino de envio de log, você não precisa usar WITH STANDBY entre os passos de RESTORE LOG.