Início / dba / Perguntas / 104460
Accepted
RayofCommand
Asked: 2015-06-19 06:38:44 +0800 CST

Internos de backup - O que acontece quando uma tarefa de backup está em execução - em termos de bloqueio e sobrecarga de desempenho no SQL Server?

  • 772

Para o MySQL, eu sei que o backup do banco de dados é feito tabela por tabela em instruções SQL, isso resulta em bloqueio e se você atualizar colunas durante o backup, poderá acabar com problemas de integridade.

Pelo que entendi, isso não se aplica ao Microsoft SQL Server, mas como o SQL Server lida com isso? Existe algum congelamento interno para manter o banco de dados consistente?

Também ouvi dizer que o backup é de thread único, o que significa que usa apenas um núcleo, supondo que você faça backup em um único arquivo. Supondo também que você tenha uma máquina multicore, por exemplo, 16 núcleos, ou pelo menos um número significativamente maior que um.

Pela minha experiência pessoal, nunca tive problemas ao fazer backups, nem bloqueios nem problemas de sobrecarga, mas minha experiência é limitada. É por isso que sempre recomendo ativar a compactação de backup nas propriedades do servidor.

Então, o que acontece quando uma tarefa de backup está em execução? E também existem diferenças significativas para as diferentes versões? por exemplo 2008,2012 e 2014 (não as licenças).

sql-server sql-server-2012

3 respostas

  1. Best Answer

    Todos os seus pontos são cobertos em mitos de backup - por Paul Randal

    30-01) operações de backup causam bloqueio

    Não. As operações de backup não aceitam bloqueios nos objetos do usuário . Os backups causam uma carga de leitura muito pesada no subsistema de E/S, portanto, pode parecer que a carga de trabalho está sendo bloqueada, mas na verdade não está. Só está sendo desacelerado. Há um caso especial em que um backup que precisa coletar extensões de log em massa receberá um bloqueio de arquivo que pode bloquear uma operação de ponto de verificação - mas o DML nunca é bloqueado.

    Também ouvi dizer que o backup é de thread único, o que significa que usa apenas um núcleo, supondo que você faça backup em um único arquivo.

    Um backup feito em um único arquivo ou dispositivo usará 1 thread de gravação. Portanto, se você estiver fazendo backup em vários arquivos/dispositivos (sejam vários arquivos .bak), haverá um thread de gravação por arquivo/dispositivo.

    A maneira mais fácil de melhorar o desempenho do backup é permitir que a operação de backup seja paralelizada, o que é conhecido como divisão de backup. Por padrão, há um único thread de leitor de dados para cada letra de unidade ou ponto de montagem sendo lido e um único thread de gravador de dados para cada dispositivo de backup sendo gravado.

    Verificar

    1. Vídeos de prontidão do SQL Server 2008 Microsoft Certified Master (MCM), especialmente backup interno.
    2. Uma olhada nos internos do backup e como rastrear a taxa de transferência de backup e restauração (parte 1) - Por: Jonathan Kehayias
    3. Uma olhada nos internos do backup e como rastrear a taxa de transferência de backup e restauração (parte 2) - Por: Jonathan Kehayias
    • 10

  2. O artigo escrito por Paul sobre internos de backup é excelente e você deve lê-lo. Adicionando ao que os outros disseram e enfatizando uma parte específica da sua pergunta

    Também ouvi dizer que o backup é de thread único, o que significa que usa apenas um núcleo, supondo que você faça backup em um único arquivo. Supondo também que você tenha uma máquina multicore, por exemplo, 16 núcleos, ou pelo menos um número significativamente maior que um.

    Operação de backup, can use parallelismmas lembre-se de que não é o paralelismo conduzido pelo Optimizer no SQL Server, mas sim pelo número de discos envolvidos de onde o backup tem que ler o arquivo de dados e onde o backup grava o arquivo de dados e a quantidade de arquivos de backup criados.

    Você não pode usar MAXDOPa dica ao fazer backup do SQL Server

    Você não pode gerar plano de execução no SSMS para operação de backup TSQL simples.

    O paralelismo orientado pelo otimizador de consulta no SQL Server é basicamente para os operadores envolvidos (na verdade, é mais complexo, mas para simplificar, você pode usar isso), pois a operação de backup não envolve nenhum operador, portanto, não pode usar o paralelismo orientado pelo otimizador.

    Escrevi um artigo no Technet Wiki sobre backup e paralelismo onde usei exemplos simples para explicar o paralelismo durante o backup do SQL Server. Segue a conclusão

    1. Se os arquivos do banco de dados estiverem em vários discos, a operação de backup será iniciada no thread por unidade de dispositivo para ler os dados. Da mesma forma, se a restauração for feita em várias unidades/pontos de montagem, a operação de backup iniciaria um encadeamento por unidade/ponto de montagem

    2. Mesmo se você estiver despejando várias cópias de backup na mesma unidade, teremos um thread por arquivo de backup despejado.

    3. O paralelismo associado ao backup está relacionado às listras. Cada faixa obtém seu próprio thread de trabalho e essa é realmente a única parte do backup/restauração que deve ser considerada como operações paralelas.

    4. O grau máximo de paralelismo não afeta a operação de backup.

    Recebi algumas opiniões de especialistas sobre isso de Paul e Bob Dorr.

    Então, o que acontece quando uma tarefa de backup está em execução? E também existem diferenças significativas para as diferentes versões? por exemplo 2008,2012 e 2014 (não as licenças).

    Sugiro que você leia este artigo do blog.msdn de Bob Dorr. Alguns pontos importantes que ele enfatizou é

    1. Quando um backup é iniciado, ele cria uma série de buffers, alocados da memória fora do buffer pool. O alvo geralmente é de 4 MB para cada buffer, resultando em aproximadamente 4 a 8 buffers. Detalhes sobre o cálculo estão localizados em: http://support.microsoft.com/kb/904804/en-us

    2. Os buffers são transicionados entre as filas livres e de dados. O leitor extrai um buffer livre, preenche-o com dados e o coloca na fila de dados. O(s) gravador(es) puxa(m) buffers de dados preenchidos da fila de dados, processa o buffer e o devolve à lista livre.

    3. Você obtém um gravador por dispositivo de backup, cada um recuperando da fila de dados. Portanto, um comando de backup com quatro (4) especificações de disco terá quatro gravadores e um leitor. O leitor usa E/S assíncrona para poder acompanhar os gravadores.

    Você pode habilitar trace flags 3213 and 3605, ambos não estão documentados, portanto, use-o no ambiente de teste e veja qual mensagem interessante é despejada no log de erros do SQL Server. Algo como abaixo apareceria

    Memory limit: 249MB
BufferCount:                7
Sets Of Buffers:            1
MaxTransferSize:            1024 KB
Min MaxTransferSize:        64 KB
Total buffer space:         7 MB
Tabular data device count:  1
Fulltext data device count: 0
Filestream device count:    0
TXF device count:           0
Filesystem i/o alignment:   512
Media Buffer count:            7
Media Buffer size:          1024KB

    Não estou ciente de nenhuma alteração significativa no código de backup para várias versões, essas coisas não estão documentadas. Eu só sei sobre o aprimoramento introduzido na SQL Server 2012 SP1 Cumulative Update 2,habilitação de backup e restauração do serviço de armazenamento de Blob do Windows Azure do SQL Server usando TSQL ou SMO. Leia aqui

    • 8

  3. Basicamente, o SQL Server faz uma cópia suja de todas as páginas do disco. Essas páginas provavelmente são inconsistentes se houver atividade simultânea ou se houver atividade anterior sem pontos de verificação.

    Em seguida, o SQL Server também copia a parte necessária do log de transações que é necessária para trazer as páginas desatualizadas para a versão mais recente e tornar tudo consistente na restauração.

    Não posso falar sobre a multitarefa da operação de backup. Espero que seja paralelizado. De que outra forma você poderia fazer backup de um banco de dados de 10 TB em um subsistema IO de 10 GB/s?

    • 5

relate perguntas