SQL q &R: Afunilamentos e logs de transações

  • Artigo

Às vezes, é fácil determinar a natureza de um afunilamento de desempenho — outras vezes, nem tanto. O mesmo vale para a configuração de logs de transações.

Paul S. Randal

Gargalo de armazenamento

Q: Estou tentando determinar a natureza do afunilamento de desempenho do meu sistema. Estou convencido de que é a camada de armazenamento como eu estou vendo a fila de disco comprimentos ir superior a dois. Eu li que isso é uma boa maneira de provar que o SQL Server está sobrecarregando o armazenamento. Isso é verdade? Em caso afirmativo, o que posso fazer sobre isso?

R: Infelizmente, o que você já leu é uma lenda urbana. SQL Server foi projetado para usar e/S assíncrona e felizmente vai empurrar o comprimento da fila de disco acima de dois. Cada segmento que emite uma e/S, em seguida, continua a fazer outra coisa (potencialmente) até a IO é completo. SQL Server tenta maximizar a produtividade do subsistema de e/S por emissão IOs assíncronas simultâneas. Ele também irá executar operações como read-ahead na digitalização de grandes volumes de dados.

Na verdade, algumas operações como DBCC CHECKDB vão saturar o subsistema de e/S. Não é raro ver comprimentos de fila de disco de várias centenas. Você pode ler mais sobre esta lenda urbana de comprimentos de fila de disco neste blog post de .

A questão então torna-se, "O fazer você olhar para determinar se há um afunilamento de subsistema de e/S?" Existem dois contadores de desempenho no objeto de desempenho de disco físico. Você deve prestar atenção a estes:

  • AVG. Disk sec/Read
  • AVG. Disk sec/Write

Estes dão o tempo em milissegundos necessário para concluir uma e/S. Se esses números são consistentemente mais elevados (ou ter regulares mais elevados picos) do que a norma (que deve ser entre 5 MS e 12ms), então o disco físico é o gargalo de e/S. Naturalmente, esse disco físico pode ser um LUN de SAN, mas você não pode cavar mais fundo do Windows.

Se você tiver vários arquivos de log e dados da SQL Server nesse disco físico, talvez você precise determinar quais arquivos estão causando a carga de e/S. Use o sys.dm_io_virtual_file_stats de exibição (DMV) de gerenciamento dinâmico e executar algumas análises de séries temporais simples sobre os resultados.

Se os resultados DMV não indicam uma carga pesada nesse disco físico, um administrador de armazenamento pode ter colocado arquivos de outros aplicativos em que parte do subsistema de e/S. Essa carga de trabalho poderia ser o que é monopolizando a largura de banda de e/S. Nesse caso, você precisará pedir que o administrador específico para mover os arquivos do SQL Server para uma parte dedicada do subsistema de e/S.

Se é puramente arquivos do SQL Server no subsistema e você pode identificar quais as que estão causando o IO excessiva, considere estas estratégias:

  • Olhe para a carga de trabalho de consulta de banco de dados e determinar se ele está executando verificações de tabela excessivos por causa de uma estratégia de indexação incorreta ou planos de consulta ruim causados por estatísticas desatualizadas.
  • Mova alguns arquivos para uma parte diferente do subsistema de e/S.
  • Adicione mais memória para o servidor para permitir uma maior área de buffer de SQL Server (na memória cache de páginas de arquivo de dados) e evitar tanto ler IO.

Se nenhum desses trabalhos e ele é realmente apenas um caso onde a carga de trabalho tem ultrapassou o subsistema de e/S, mova para um subsistema de e/S mais capaz. Você também pode considerar armazenamento de memória flash de classe corporativa como Fusion-io.

Size Matters

Q: Eu estou colocando os requisitos de armazenamento para alguns novos servidores e estou tendo problemas para determinar como grande para fazer os logs de transações. No passado, eu tentei baseá-la no tamanho de transações. Às vezes, porém, parece a dobrar. Você pode explicar como eu pode chegar a uma estimativa decente?

R: Não há nenhuma fórmula fácil para calcular o tamanho do log de transações ideal. Infelizmente, também é difícil para impedir a transação de log de crescimento a menos que você tenha desativado crescimento automático para o arquivo de log. No entanto, como desativar auto-crescer nunca é aconselhável.

O primeiro deles é o nível RAID subjacente. Os diferentes níveis RAID têm diferentes variações tanto quanto desempenho e redundância. Por exemplo, a configuração de RAID mais barata que ainda oferece alguns redundância é RAID-5, mas esta configuração somente pode lidar com uma falha de disco único (a menos que usando RAID-6, ou configurado discos sobressalentes) e às vezes pode prejudicar o desempenho para cargas de trabalho pesadas de gravação, dependendo de quantas unidades estão na matriz.

O log de transações deve sempre ser capaz de auto-grow. Isto é particularmente verdadeiro para situações de emergência quando tamanho do seu arquivo de log Falha na vigilância. Por exemplo, que você tenha um alerta do SQL Server Agent no contador de desempenho % Log usado indo superior a 90%, mas o contato de emergência notificado por e-mail/pager está fora por doença. Se o log não pode crescer, tudo atualmente executar transações modificar o banco de dados irá parar e reverter. Isso se traduz em tempo de inatividade para sua carga de trabalho.

No entanto, dizendo que não há nenhuma fórmula fácil é um pouco enganador. Existem numerosas operações que ocupam espaço de log de transação. Você pode usar o tamanho dessas operações para estimar suas necessidades de log de transação. Estes podem ser operações efectuadas no âmbito de sua carga de trabalho diária, ou ações mais freqüentes, como manutenção de banco de dados. Você tem que considerar todos eles. A lista de operação inclui:

  • A maior transação de insert/update/delete única sua carga de trabalho executa (se uma transação implícita de instrução simples que afetam milhões de linhas da tabela, ou uma transação explícita que executa operações de muitos).
  • A maior operação de em massa realiza sua carga de trabalho, como um BULK INSERT. Se você estiver usando o modo de recuperação completa, você pode ser capaz de reduzir a quantidade de logs de transação gerada usando o modelo de recuperação BULK_LOGGED. Se você usar o modelo de recuperação BULK_LOGGED para registro mínimo para algumas operações, ele pode afetar a capacidade de recuperação após desastres. Veja meu blog post, " um SQL Server DBA mito por dia: BULK_LOGGED recuperação modelo . "
  • Uma reconstrução de índice de seu maior índice clusterizado. Você pode ser capaz de usar BULK_LOGGED aqui também.

Com todas essas operações, não é apenas a quantidade de logs de transações, que você precisa considerar, você também precisa levar em conta o espaço o sistema de gerenciamento de log de transação será "reserva" para permitir a reversão de transação adequada. Se uma transação gera 100 MB de registros de log de transações, o sistema irá reservar cerca de 100 MB de espaço vazio no log de transações para garantir que pode anular a transação e corretamente reverter. É um mecanismo de segurança para impedir que um banco de dados se tornar inconsistente. Eis porque você pode ter visto o log de transação crescer, mesmo que você pense que você deu espaço suficiente para a maior transação.

Outra coisa a considerar é se existem quaisquer motivos por que registros de log de transações devem permanecer no log. Isso poderia levar a um log de transações crescente necessidade de crescer ainda mais. Algumas das razões possíveis incluem:

  • O banco de dados é usando os modelos de recuperação completa ou BULK_LOGGED e não a realização de backups de log de transação (ou executando-as com pouca freqüência). Você deve fazer backup registros de log antes de descartá-los.
  • Há uma transação invulgarmente demorada. Isso impedirá que descartar todos os registros de log de transações gerados desde que comecei a transação de longa duração.
  • Espelhamento de banco de dados está em uso e alguns registros de log de transações ainda não foram enviados do servidor principal para o servidor espelho. Você não pode descartar estes até que tenha sido enviados.
  • A replicação transacional (ou replicação ponto a ponto) estiver em uso e há alguns registros de log de transação que do trabalho do Log Reader Agent não processada.

Se você está vendo o crescimento de log de transação e você não tiver certeza por que isso está acontecendo, pergunte do SQL Server. Execute a consulta:

SELECT [log_reuse_wait_desc] FROM sys.databases
WHERE [name] = 'dbmaint2008';
GO

O resultado será a razão você não pode descartar alguns registros de log e reutilizar o espaço de log (chamado "limpar" ou "truncar" o log).

Como você pode ver, há algumas coisas que podem afetar o tamanho do log de transações, ainda mais se você está considerando o banco de dados tempdb também. Você pode ler um pouco mais sobre este tema no meu blog, " de importância do gerenciamento de tamanho de log de transações adequada," e em technet Magazine artigo " Noções básicas sobre SQL Server log e recuperação de ."

Registro obrigatório

Q: Você pode explicar por que eu não posso fazer operações do SQL Server não registrados? Eu li que truncamento de tabela é não registrada em log — porque não pode haver uma configuração para fazer todas as operações não-registradas, de modo SQL Server seja executado mais rapidamente? O que acontece se eu não me importo sobre a possibilidade de recuperação após um desastre? Especialmente, gostaria de ser capaz de ignorar o log de transações tempdb.

R: O que você já leu sobre uma operação TRUNCATE TABLE não é verdade. Todas as operações em todos os bancos de dados são registradas em algum grau. Alguns são "minimamente registradas," tais como tabela de truncamento. Simplesmente afirmou, uma operação minimamente registrada é um onde apenas a alocação e desalocação de páginas de arquivo de dados é registrado. Todas as operações em registros de tabela/índice nas páginas não são registradas. Isso acelera as operações e significa menos log de transações é gerado — mas ainda há algum registro.

Um truncamento de tabela é sempre minimamente registrado em todos os modelos de recuperação. Outras operações minimamente conectadas (tais como cargas de construção/reconstrução e em massa de índice) são registradas apenas minimamente ao usar os modelos de recuperação simples ou BULK_LOGGED.

As operações só verdadeiramente não-registrado no SQL Server são aquelas que afetam o armazenamento de versão em tempdb, que oferece suporte a recursos como o isolamento de instantâneo e operações de índice online. Estas podem ser não-registradas porque nunca há uma necessidade de reverter uma operação de loja de versão ou execução de recuperação de falhas no banco de dados tempdb.

Isso fica para o cerne da sua pergunta. Por que operações no SQL Server não podem ser não registrado? SQL Server sempre tem que ser capaz de reverter uma operação se algo der errado. Se não havia que nenhuma descrição (como os registros de log de transações) de que a operação tinha feito, como SQL Server saberia o que fazer durante a reversão? Você só pode obter isso através de registro em log.

Mesmo se você não se preocupam com recuperação de falhas, SQL Server ainda tem de ser capaz de reverter operações se o banco de dados é executado fora do espaço ou encontra um setor danificado, ou se a consulta é executada fora da memória. Se o SQL Server não pode reverter uma operação, o banco de dados torna-se inutilizável e a carga de trabalho pára.

Isto também se aplica ao banco de dados tempdb. Embora o log é simplificado e reduzido em tempdb, você nunca pode removê-lo inteiramente pelas mesmas razões. Além disso, o SQL Server tem que ser capaz de executar a recuperação de falhas depois de cada queda para garantir que cada banco de dados consistente. Caso contrário, o banco de dados é inutilizável. Linha inferior: Não há nenhuma maneira de tornar as operações registradas no SQL Server e não esperaria que mudar.

Paul Randal

Paul S. Randal é diretor 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/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 bloga em SQLskills.com/blogs/paul e você pode encontrá-lo no Twitter em Twitter.com/PaulRandal.

Conteúdo relacionado