Perguntas e respostas sobre SQL Tamanho é documento

  • Artigo

O tamanho do banco de dados, a fragmentação de índices e a disponibilidade pós-failover estão entre as questões que andam atormentando os administradores do SQL este mês.

Paul S. Randal

Medo da fragmentação

**P.**Eu tenho lido alguns posts que parecem implicar não precisamos estar preocupado com a fragmentação do índice se nossos bancos de dados hospedados no armazenamento de estado sólido — a teoria sendo que Solid-State drives (SSDs) são muito mais rápidos do que discos de fiação. Eu entendo a degradação de desempenho seria reduzida, mas pode realmente apenas completamente ignorar fragmentação do índice?

**.**Se você estiver usando discos de fiação ou .ssds., você precisa prestar atenção a fragmentação do índice. Fragmentação de índice engloba dois fenômenos — problemas de densidade de página e páginas de índice fora de ordem. O antigo impede leitura eficiente frente durante as varreduras de índice-gama e este último reduz a densidade de dados.

É verdade que as latências de leitura/gravação com SSDs são muito pequenas. Por conseguinte, a necessidade de executar e/SS read-ahead menores, mais freqüente quando o intervalo de verificação de um índice fragmentado não terá em qualquer lugar perto, tanto de um efeito de desempenho como na mesma situação quando se utiliza discos de fiação.

No entanto, a redução na densidade de dados da fragmentação do índice ainda pode ser um grande problema. A maioria dos fragmentação do índice ocorre de uma operação chamada uma "página dividida." Isso acontece quando o espaço livre é criado em uma página em um índice, movendo metade as linhas do índice para uma nova página. Isso deixa as páginas antigas e novas com cerca de 50 por cento de espaço vazio. Com um índice muito fragmentado, não é raro ver índices com uma densidade média de página de 70 por cento ou menos (com 30 por cento de espaço livre).

Com isso em mente, se um grande número de índices nos bancos de dados armazenados em seu SSDs têm página de baixa densidade, isso significa que seu caro SSDs poderia armazenar uma grande quantidade de espaço vazio. Isso não é claramente uma situação ideal. Além disso, apesar de e/SS extra necessário para ler nas páginas de baixa densidade terá baixas latências sobre os SSDs, eles vão ter mais espaço na área de buffer do SQL Server (o cache de página de arquivo de dados na memória). Isto também significa que sua memória preciosa do servidor não está sendo usada corretamente.

A outra coisa a considerar além a fragmentação do índice propriamente dito é a causa da fragmentação: página divide. Estas são operações caras que geram uma grande quantidade de registros de log de transação (dê uma olhada no meu blog post para ver o quão ruim pode ser). Esses registros de log extra significam processamento extra por qualquer coisa que lê o log de transações (como a replicação transacional, backups, banco de dados espelhamento, envio de logs). Isto pode causar degradação de desempenho para esses mesmos processos. Portanto, não ignore a fragmentação do índice só porque você está usando o SSDs.

Não olhar para o espelho.

**P.**Nós estamos redesenhando nossa estratégia de disponibilidade, mas tornam-se preso em como fazer um par de replicação transacional bancos de dados de assinatura mais altamente disponível. Nós não poderia usar o espelhamento de banco de dados no SQL Server 2005 porque teríamos que reinicializar a assinatura após um failover. É uma solução melhor agora que estamos no SQL Server 2008 R2?

**.**Você está correto em afirmar que apenas espelhamento de um banco de dados de assinatura no SQL Server 2005 fornece uma cópia redundante de dados que já tem sido espelhados para a cópia de espelho do banco de dados de assinatura. Não há nenhuma maneira de recriar a inscrição de replicação sem uma reinicialização completa. Isto, obviamente, faz uma má escolha no SQL Server 2005 de espelhamento de banco de dados.

Com o SQL Server 2008, houve um novo mecanismo introduzido na replicação transacional que permite a reinicialização parcial de uma assinatura. A opção é chamada "inicializar do lsn". Ele é especificado como o parâmetro @ sync_type ao chamar sp_addsubscription.

Peer-to-peer replicação transacional no SQL Server 2008 foi aprimorada para que você possa adicionar e remover nós em uma topologia de peer-to-peer sem ter que fazer todas as atividades na topologia completamente quiescente pela primeira vez. Isso foi um grande impulso para a topologia de peer-to-peer de disponibilidade de dados fornece. A opção "inicializar do lsn" foi adicionada como esses aprimoramentos foram para fora no lugar.

Para o espelhamento de banco de dados, não há suporte adicional para o espelhamento de bancos de dados de assinatura (como existe no Log Reader Agent para espelhamento de bancos de dados de publicação). No entanto, você pode usar o método "initialize da lsn" para fornecer uma reinicialização rápida de uma assinatura após um failover de espelhamento.

Esta metodologia baseia-se em determinar o número de seqüência de Log (LSN — um número exclusivo que identifica um registro de log de transação) do mais recente replicados operação aplicada ao banco de dados de assinatura antes de ocorrer o failover de espelhamento. Vamos chamar esse LSN2.

Algumas dessas operações serão têm também sido espelhadas para a cópia de espelho do banco de dados antes de ocorrer o failover. Isso poderia ir até LSN3, por exemplo, um pouco mais voltar no tempo de LSN2. Também haverá algumas operações que ainda não foram aplicadas ao banco de dados de assinatura em tudo. Estas são mais recentes no momento que LSN2 ou LSN3. Vamos chamar essas LSN1.

Todas as operações de backup para LSN2 foram aplicadas para o banco de dados de assinatura principal. Todas as operações de backup para LSN3 foram aplicadas para o banco de dados de assinatura principal e espelhadas para o banco de dados de assinatura de espelho. Para executar uma inicialização "inicializar do lsn" de uma nova assinatura após um failover de espelhamento, a chamada para sp_addsubscription deve usar LSN3 como ponto de partida.

A retenção de distribuição período também deve ser definida para que operações são mantidas no banco de dados de distribuição para algum tempo depois que tiver sido aplicados para o banco de dados de assinatura. Em suma, você agora pode usar o espelhamento de banco de dados para fornecer maior disponibilidade de um banco de dados de assinatura com apenas uma reinicialização mínima exigida após um failover de espelhamento. Para obter uma explicação mais aprofundada sobre isso, baixe o white paper, "replicação do SQL Server: Espelhamento de alta disponibilidade usando o banco de dados. "

Demasiado grande para identificador

**P.**Nosso banco de dados principal atingiu quase 9 TB. Estamos descobrindo que simplesmente não têm a capacidade de executar tarefas de manutenção regular sem afetar seriamente nossas cargas de trabalho regulares. Estamos mais preocupados com a possibilidade de fazer backup do banco de dados para permitir a recuperação de desastres. Você tem algum Conselho?

**.**Este é um caso onde separarem o banco de dados em pedaços mais gerenciáveis seria benéfica. Você pode fazer isso de várias maneiras, o mais comum do que usa o SQL Server tabela/índice particionamento recurso (na Enterprise Edition) ou dividir manualmente as coisas em tabelas separadas.

Em ambos os casos, o ponto crucial é criar vários grupos de arquivos no banco de dados. Com o particionamento, cada partição de tabelas/índices de maiores reside em um grupo de arquivos separado. Com separação manual, cada tabela grande reside em um grupo de arquivos separado (possivelmente com todos os seus índices também).

Usando grupos de arquivos separados, você tem mais granulares unidades do banco de dados que você pode fazer backup e restaurar. Você não terá que operam em todo o 9 de TB cada vez. Se você tivesse um banco de dados de venda, por exemplo, com dados a partir de 2012 volta para 2008, você pode particionar várias tabelas por intervalo de dados em partições do ano civil. Cada partição ano seria em um grupo de arquivos separado.

Com apenas as 2012 arquivos submetidos alterações, você poderia backup com freqüência. Você pode fazer backup outros grupos de arquivos imutáveis muito menos frequentemente. Isso economiza em espaço de armazenamento de backup e a quantidade de tempo que i/o extra o sobrecarga de executar o backup será computado no sistema de produção.

Com esse acordo, recuperação de desastres também se torna mais rápida (usando o Enterprise Edition). Você só precisará restaurar rapidamente os grupos de arquivos necessários para que a parte de processamento de transações Online (OLTP) da carga de trabalho on-line. Você pode fazer isso com uma restauração parcial e, em seguida, usar disponibilidade parcial do banco de dados para trazer o banco de dados on-line. Você pode restaurar os grupos de arquivos que contém os dados antigos mais tarde usando a restauração on-line, enquanto a atividade OLTP está ocorrendo nos grupos de arquivos já estão online.

Você pode ler que mais sobre esta abordagem nestes white papers:

Sob pressão

**P.**Uma coisa que confunde a nossa equipa DBA é como dizer se o pool de buffers é ser pressionado. Há um monte de informações conflitantes sobre quais contadores PerfMon e quais os limites para usar. A maioria dos que eu li diz para usar a expectativa de vida de página (PLE) e 300 como um limiar. Você pode lançar alguma luz sobre isso?

**.**Você não está sozinho em sua confusão. O número 300 foi referenciado pela primeira vez em um Microsoft white paper publicado há cinco anos. Isto é agora mal desatualizado.

PLE é o contador certo para usar, mas você tem que entender o que significa e quando a ser em causa. Esse número fornece uma medida de quão agressivamente o pool de buffers é fazer espaço para páginas de arquivo de dados necessários ser lidos do disco para a memória instantânea. Não é uma média. É o tempo esperado em segundos que uma página de leitura do disco será estadia na memória antes de você ter que limpá-lo tão outra página pode tomar o seu lugar.

Como tal, olhando para um único valor PLE não lhe dizer muito. Você precisa olhar para as tendências de valor. É perfeitamente possível para as operações válidas do SQL Server fazer com que o PLE cair drasticamente. Muitas vezes, em seguida, recuperará o valor antigo. Se PLE cai e permanece baixa, que é um motivo de preocupação.

O limiar para quando a ser em causa não é um valor fixo, como muitas pessoas descrevem. Os meios de 300 valor o pool de buffer inteiro está sendo substituído cada 300 segundos. Se você tiver um pool de buffers de 100 GB, por exemplo, isso significa que 100 GB de dados novos são lidos na memória a cada cinco minutos. Isso é claramente um problema de desempenho. No entanto, torna-se uma maneira de problema de desempenho enorme antes PLE hits 300. Você pode calcular um valor mais razoável usando (memória de pool do buffer em GB / 4) * 300, conforme explicado no este post no blog.

Você também precisará estar ciente da configuração de acesso (NUMA) de memória não-uniforme do seu servidor. O contador PLE no objeto de desempenho do Gerenciador de Buffer é realmente a média de PLEs para cada nó NUMA, se você tiver configurada NUMA. Isso significa monitoramento PLE de gerenciar o Buffer não é um verdadeiro indicador de pressão de memória no servidor. Nesse caso, você deve medir o contador PLE em cada um dos objetos de desempenho Buffer Partition. Você pode ler mais sobre PLE e na este post no blog.

PLE é o direito de contador para monitorar, mas você só deve se preocupar se o valor cai significativamente abaixo do normal e permanece lá por muito tempo. Que é uma orientação geral, mas infelizmente não há nenhum informações específicas que se aplicam a todas as situações.

Paul S. Randal

**Paul S. Randal**é o director-geral da administrativo SQLskills.com, diretor regional da Microsoft e MVP do SQL Server. Ele trabalhou na equipe do mecanismo de armazenamento do SQL Server na Microsoft a partir de 1999 a 2007. Ele escreveu o DBCC CHECKDB/repair para SQL Server 2005 e foi responsável pelo mecanismo de armazenamento principal durante o desenvolvimento do SQL Server 2008. Randal é um especialista em recuperação de desastres, alta disponibilidade e manutenção de banco de dados e é um apresentador regular em conferências em todo o mundo. Ele blogs em SQLskills.com/blogs/paul, e você pode encontrá-lo no Twitter em twitter.com/PaulRandal.

Conteúdo relacionado