P+R SQL: Ajustes que garantem um desempenho perfeito
Índices duplicados, operações de reversão canceladas e picos de E/S podem causar problemas de desempenho, mas você pode contorná-los.
Paul S. Randal
Índices duplicados
**P.**SQL Server parece deixar-me a criar índices que são exatamente as mesmas na mesma tabela. Como isso ajuda meu desempenho de carga de trabalho? Consultas diferentes usará diferentes cópias do mesmo índice?
**R.**É lamentável que o SQL Server permite índices duplicados, que fornecem nenhum benefício algum. Na verdade, índices duplicados podem ser prejudiciais em muitos aspectos.
Um índice duplicado ocorre quando as chaves de índice são exatamente o mesmo que outro índice, especificado na mesma ordem e com a mesma especificação de ASC ou DESC. As colunas incluídas (se houver) também são os mesmos (embora as colunas incluídas podem ser especificadas em qualquer ordem).
SQL Server usará somente um dos índices duplicados para ajudar com as consultas, mas deve manter todos os índices em uma tabela durante a inserir, atualizar e excluir operações. Isso significa que sempre que houver um insert ou delete na tabela, ele deve traduzir-se em todos os índices. O mesmo é verdadeiro para atualizações, se as colunas sendo atualizadas fazem parte do índice.
Esta manutenção de índice utiliza recursos extras e gera registros de log de transação extra — tudo para índices que são essencialmente inúteis. Esses índices duplicados tomar espaço extra em disco e espaço extra em backups — e as páginas necessárias para a manutenção do índice tomar espaço extra na memória também.
Índices duplicados são susceptíveis de se tornar fragmentados. Eles também exigem recursos extras durante a remoção de fragmentação do índice regular. O log de transações extra de registros de manutenção de índice e remoção fragmentação também pode levar a desempenho de recursos de alta disponibilidade (HA) como espelhamento de banco de dados e replicação de transação inferior.
O SQL Server não dá nenhum aviso de que você acabou de criar um índice duplicado, por isso cabe à você para evitar fazê-lo. Verificar se você já tiver índices duplicados é pequena. Trata-se de todas as definições de índice e manualmente comparando eles, ou extensa programático analisar os catálogos de sistema de script. No ano passado, Kimberly Tripp Postado um solução completa para este problema.
Cuidado com a reversão
**P.**Recentemente tive de cancelar uma atualização de longa duração. Depois que a operação revertida, o próximo backup de log de transações diárias foi enorme. Eu esperava que fosse muito pequeno, como nada havia mudado no banco de dados. Você pode explicar esta anomalia?
**R.**Isso é um equívoco bastante comum. Se você reverter uma operação grande, o próximo backup diferencial deve ser pequeno, certo? Errado.
Qualquer tempo que SQL Server faz uma alteração no banco de dados, duas coisas acontecem. Primeiro, ela gera registros de log de transação que descrevem a alteração. Em segundo lugar, para quaisquer páginas de arquivo de dados modificadas pela mudança, o bit correspondente é definido em um bitmap diferencial. Isso significa que essas páginas devem ser apoiadas pelo próximo backup diferencial.
Quando você reverte uma operação, o SQL Server tem que desfazer as alterações operação feita. Isso significa que ele examina todos os registros de log de transações gerados pela parte direta da operação. Tem que desfazer essas alterações na ordem inversa. Cada registro de log de transações descreve uma única alteração para o banco de dados como parte da operação. Para reverter essa alteração, você tem que fazer outra alteração para o banco de dados que anula o efeito da alteração original. Por exemplo, você iria reverter uma inserção de registro, excluindo o registro. O efeito líquido é que o registro não existe.
Aqui é a parte mais confusa: cada alteração realizada durante a reversão é realmente apenas uma outra alteração de banco de dados (embora um especial). Para cada alteração no banco de dados, deve haver um registro de log de transação. Por isso mesmo as alterações feitas durante uma reversão devem estar conectadas corretamente. Isso significa que um rollback operação grande irá gerar não somente registros de log transação para a parte de frente da operação, mas também para a reversão. Backups de log de transação fará backup todos esses registros de log de transação, contabilidade para o backup de log de transações grandes.
Quando a parte de frente da operação faz com que o bitmap diferencial ter bits definidos porque partes do banco de dados foram alteradas, você não é possível limpar os bits de bitmap diferencial novamente porque o banco de dados foi alterado. Não importa se a alteração eventualmente foi revertida. As páginas de arquivo de dados ainda foram alteradas (por duas vezes, na verdade) e por isso devem ser feitas backup pelo backup diferencial.
O cerne da questão é que mesmo quando uma operação é revertida, o banco de dados ainda é alterado. Todos os backups precisam refletir essas alterações.
Em busca de picos
**P.**Estou Solucionando problemas em um problema onde vemos periódicos picos de I/O de um dos nossos servidores SQL. Eu já reduzi-lo a pontos de verificação usando o PerfMon, mas eu não posso dizer qual banco de dados é o grande culpado. Como posso perfurar em ainda mais?
**R.**Pontos de verificação existem por duas razões. Primeiro, eles atualizar páginas de arquivo de dados com o que foi gravado para o log de transações. O SQL Server usa um mecanismo chamado log de write-ahead, onde as alterações de banco de dados são descritas no log de transações antes de ser reflectida nos arquivos de dados. Isso garante a durabilidade das alterações em caso de acidente. Em segundo lugar, elas reduzem a quantidade de carga de i/O constante escrevendo somente os dados alterados páginas de arquivo periodicamente, em vez de após cada alteração para cada página do arquivo de dados.
Pontos de verificação ocorrerem separadamente para cada banco de dados. Eles são acionados com base em uma série de fatores, incluindo o intervalo de recuperação — este é o SQL Server estimar que suficiente log de transações foi gerado desde o último ponto de verificação para que a recuperação de falhas levará aproximadamente um minuto (por padrão).
Esse número equivale à geração de muitas dezenas de milhares de registros de log de transações individuais. Mais páginas de arquivo de dados alterada por esses registros de log de transação, a maiores quantidade de i/O que deve ser realizado por pontos de verificação do banco de dados.
Você pode rastrear pontos de verificação usando o contador de "Checkpoint pages/sec" no SQL Server: Objeto de desempenho Gerenciador de Buffer. Que só dá uma contagem total em todos os bancos de dados na instância do SQL Server. Para determinar qual banco de dados está sendo "verificado" a qualquer momento, você precisará usar sinalizadores de rastreamento.
Se você ativar os sinalizadores de rastreamento (trace impressão quando ocorre um ponto de verificação) 3502, 3504 (rastreamento imprimir detalhes sobre o ponto de verificação) e 3605 (permitem rastrear imprime para ir para o log de erros), você será capaz de determinar qual banco de dados é contabilizar os picos de I/O devido a pontos de verificação.
Você pode habilitar esses sinalizadores de rastreamento usando o comando:
DBCC TRACEON (3502, 3504, 3605, -1)
Desabilitá-las novamente usando o comando:
DBCC TRACEOFF (3502, 3504, 3605, -1)
Pontos de verificação subsequentes produz uma saída semelhante ao seguinte no log de erro:
2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 started (8) 2011-12-30 05:07:14.390 spid17s About to log Checkpoint begin. 2011-12-30 05:07:14.390 spid17s Ckpt dbid 21 phase 1 ended (8) 2011-12-30 05:07:14.830 spid17s FlushCache: cleaned up 4307 bufs with 201 writes in 441 ms (avoided 23 new dirty bufs) 2011-12-30 05:07:14.830 spid17s average throughput: 76.30 MB/sec, I/O saturation: 198, context switches 392 2011-12-30 05:07:14.830 spid17s last target outstanding: 15, avgWriteLatency 2 2011-12-30 05:07:14.830 spid17s About to log Checkpoint end. 2011-12-30 05:07:14.830 spid17s Ckpt dbid 21 complete
Isso permite que você veja qual banco de dados está sendo verificado e que correspondem às informações de PerfMon. Você pode então investigar porque é que há tantos dados que está sendo alterados entre pontos de verificação, execute a verificação mais frequente para reduzir o pico de I/O, ou aumentar a capacidade do subsistema I/O.
Preocupações de consolidação
**P.**Minha empresa instituiu uma nova política que exige que devemos consolidar, tanto quanto possível para reduzir os custos de hardware. Eu também estou sendo empurrado para reduzir o número de instâncias do SQL Server para economizar custos de licenciamento. Existem quaisquer orientações para quantos bancos de dados por instância do SQL Server fazem sentido?
**R.**A resposta a esta pergunta é um grande "depende". A lista de fatores inclui o tamanho dos bancos de dados, os tipos de cargas de trabalho que estão sendo executado, a volatilidade dos dados, o tipo de manutenção regular necessária e a recuperação de desastres e requisitos de alta disponibilidade.
Cada instância do SQL Server tem uma quantidade finita de espaço na memória para armazenar páginas de arquivo de dados sendo processadas em qualquer momento (isso é conhecido como o pool de buffer). Os bancos de dados mais você tem em uma instância com diferentes cargas de trabalho de todos os que exigem processamento, mais concorrência haverá entre as cargas de trabalho para o espaço do pool de buffer.
Isso pode levar a debulha a memória de pool de buffer. Haverá rotatividade constante para liberar espaço para novas páginas de arquivo de dados está sendo lidos no disco. Haverá também grandes quantidades de leitura I/O com latências de leitura mais elevadas do que o aceitáveis. Todos esses fatores diminuirá o desempenho da carga de trabalho.
Se as diversas cargas de trabalho implicam alterações banco de dados, também haverá o i º / do gravação o de pontos de verificação periódicos. Com vários bancos de dados consolidados em uma única instância, pode haver vários pontos de verificação que ocorrem simultaneamente. Isso poderia causar latências de I/O de gravação — a abrandar as operações de ponto de verificação e contribuindo para a degradação do desempenho de carga de trabalho.
Manutenção de banco de dados regular também se torna um problema com um grande número de bancos de dados. Se cada banco de dados requer índice e manutenção de estatísticas, a verificação de consistência e backups, pode ser um desafio para agendar todas essas operações para todos os bancos de dados para que eles não entrem em conflito uns com os outros e colocar ainda mais carga de i/O no servidor.
Os bancos de dados mais lá estão em uma instância, mais difícil se torna a usar as tecnologias de alta disponibilidade nativas do SQL Server para protegê-los todos. É mais provável que você precisará de algum tipo de tecnologia de replicação de nível do subsistema de i/O — até mesmo de uma perspectiva de facilidade de gerenciamento. Isto significa desembolso de capital adicional que poderia compensar a redução nos custos de consolidação de servidores.
A consolidação é um tópico enorme. Totalmente a fazer justiça está além do escopo de uma única coluna. Isso é suficiente alimento para o pensamento de fazer você cauteloso de over-consolidating. Por outro lado, você pode ter muitos pequenos bancos de dados com cargas de trabalho mínimas que você poderia hospedar em uma única instância sem problemas. Como eu disse antes, isso depende.
.jpg)
Paul S. Randal é o director-geral de administrativo SQLskills.com, diretor regional da Microsoft e um 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/reparo para o SQL Server 2005 e foi responsável pelo mecanismo de armazenamento principal durante o desenvolvimento do SQL Server 2008. Randal é especialista em recuperação de desastre, alta disponibilidade e manutenção de bancos de dados, e participa regularmente de conferências em todo o mundo. Ele bloga em SQLskills.com/blogs/paul e você pode encontrá-lo no Twitter em Twitter.com/PaulRandal.