Perguntas e respostas sobre o SQL: Dados dinâmicos e a recuperação de desastres

Artigo
08/19/2016

Soluções deste mês para o sucesso do SQL executar toda a gama de expandindo um tempdb e agrupamento Enigma e planos de recuperação de desastres pela metade.

S de Paul. Randal

Preenchimento de espaço

P. Um dos servidores de produção dos quais eu sou responsável tem um problema. O tempdb cresce realmente grandes intervalos de alguns dias. Este é um problema relativamente novo. Não vejo nenhuma diferença no número de conexões ao servidor ou ao uso de memória. Como monitorar a situação para determinar o que está usando o espaço de tempdb?

R. Existem algumas razões pôde aumentar o uso de tempdb:

O uso do sistema de controle de versão (para isolamento de instantâneo ou operações de indexação on-line, por exemplo) pode fazer com que o armazenamento de versão em tempdb a crescer.
Um plano de consulta podem ser alteradas por causa das estatísticas desatualizadas, que por sua vez causaria um operador do plano de consulta que resulta em um grande vazamentos de memória em tempdb.
Alguém possa ter implantado o novo código de aplicativo que usa tabelas temporárias para os dados de armazenamento parcialmente processado.

Seja qual for, existem algumas maneiras simples para rastrear o que está acontecendo. A primeira coisa a que fazer é examinar o uso de espaço de tempdb geral com o sys.dm_db_file_space_usage de DMV (exibição de gerenciamento dinâmico). Se você capturar os resultados dessa DMV a cada 30 segundos, por exemplo, você poderá dizer se o uso de espaço extra é do armazenamento de versão, os objetos de usuário ou objetos criados para auxiliar o processamento de consultas.

Se o armazenamento de versão que está ocupando todo o espaço, você pode aprofundar ainda mais usando o sys.dm_tran_top_version_generators do departamento de trânsito. Você precisará associar-se com sys.partitions e sys.indexes para obter informações úteis verdadeiramente fora dele, mas que permitirá que você saiba quais tabelas possuem a maioria das versões que estão sendo gerados.

Se ele for else ocupando espaço, você pode aprofundar, capturando os resultados de sys.dm_db_task_space_usage a uma freqüência semelhante. Em seguida, associar-se o departamento de trânsito com sys.dm_exec_requests para descobrir quais conexões e consultas estão ocupando espaço.

Se ele acaba por ser um procedimento armazenado longo, talvez você precise instrumentar o procedimento de saída periodicamente a quantidade de espaço de tempdb utiliza para que você possa trabalhar fora quais instruções dentro do procedimento são os culpados. Tive de fazer isso várias vezes em sistemas clientes.

Você pode encontrar muito mais informações sobre como usar esses DMVs no white paper, "Trabalhando com tempdb no SQL Server 2005." (Este artigo também se aplica a versões posteriores do SQL Server.)

Clusters de BOM

P. Eu já te pediram para projetar o esquema para um banco de dados que armazenará os dados para um novo aplicativo. Eu já li todos os tipos de conselhos sobre como escolher as chaves de índice de cluster "bons" para minhas tabelas. Você sabe explicar o que torna uma chave de índice de cluster "boa" e por que é importante tanto?

R. Esta é uma pergunta complexa e quase impossíveis de maneira abrangente responder aqui. Em poucas palavras, uma "boa" chave do índice de cluster é que um foi escolhido cuidadosamente para minimizar o espaço desperdiçado e baixo desempenho. São as quatro qualidades de uma chave de índice de cluster boa: estreito, estático, exclusivo e crescentes:

Restringir (ocupando o menor número de bytes possível): todos os registros de índice não clusterizado incluem a chave do índice de cluster. Quanto maior, mais espaço duplicados informações em índices não clusterizados ocupa.
Estático (inalterado): alterações nos valores de chaves são caras. SQL Server oferece uma atualização da chave como uma exclusão + inserir operação (consulte meu blog para postar aqui), e a qualquer momento que uma chave de índice de cluster é atualizada, todas as linhas correspondentes em índices não clusterizados também precisam ser atualizados. Principais alterações também podem levar a um espaço vazio em páginas de arquivo de dados se naquela posição chave no índice não for usada novamente.
Recursos exclusivos: isso evita a necessidade de adicionar uma coluna oculta de quatro bytes para valores de chave duplicados "uniquify" SQL Server — tornando a chave mais larga.
Crescentes: O padrão de inserção de novos registros resulta em inserções aleatórias para o índice de cluster pode causar caras operações de divisão da página. Isso gera a fragmentação lógica e o espaço desperdiçado em páginas de arquivos de dados.

Não dado essas qualidades de uma chave de índice de cluster em boas condições, lá freqüentemente é uma chave natural que se ajuste (por exemplo, um derivado de dados de tabela), para que você precise usar uma chave substituta (por exemplo, uma coluna de tabela artificial). Uma coluna de identidade BIGINT é um exemplo de uma chave substituta em boas condições. Leia mais explicações detalhadas e justificativas na categoria do blog de Kimberly Tripp Chave de cluster.

Preparar para a pior.

P. No encalço dos recentes terremotos na Nova Zelândia e Japão, eu examinou o nosso plano de recuperação de desastres e encontrado que ele realmente tem ultrapassado. Eu sem êxito tentando obter de nossa empresa para renovar e testar o plano. Eles simplesmente não pense que jamais teremos um desastre. Você pode me dar algumas dicas sobre como abordar isso com gerenciamento?

R. Estou satisfeito em saber que você está analisando proativamente sua estratégia de DR (Disaster recovery) de desastres no encalço desses desastres recentes. Muitas empresas são complacent e tiveram uma atitude que você descreva sua dúvida. Apesar de desastres naturais em larga escala são relativamente raras, mais localizados problemas como a criação de acionado ou quedas de energia são relativamente comuns e uma empresa não deve presumir é imune a falhas aleatórias.

Mesmo se você não é possível obter o gerenciamento do seu lado, há muito o teste você mesmo pode fazer, como a restauração de cópias de bancos de dados de backups. Isso testa a integridade do backup e a estratégia de backup e você pode garantir que o tempo de restauração cumpre os requisitos de tempo de inatividade máximo permitido para qualquer banco de dados específico. Muito freqüentemente, este é o primeiro problema encontrado durante o teste da estratégia de recuperação de desastres. Volumes de dados crescem ao longo do tempo e restauração aumentos de tempo de commensurately.

Outras partes da estratégia de recuperação de desastres são muito mais difícil de testar os seus conhecimentos, como, por exemplo, um sociedade ou em um cluster de failover de espelhamento de banco de dados de failover. Ambas exigem algum tempo de inatividade do aplicativo (tanto para failover e failback).

Enquanto estiver convencer de gerenciamento está preocupado, pergunte se localizariam em vez disso, a estratégia de recuperação de desastres não funciona durante um teste planejado com todos os funcionários disponível para ajudar na recuperação de fora, ou quando ocorre um desastre para o real às 2h em um feriado público quando uma equipe de bare-mínimo está ligado-mão.

Existem muitos incidentes altamente divulgados de empresas a sofrer interrupções porque uma estratégia de recuperação de desastres foi insuficiente. Gerenciamento deseja que sua empresa seja um as notícias? Isso pode parecer melodramatic, mas é um ponto justo.

Recuperação de desastres é minimizar o custo para a empresa e seus clientes. Se os clientes sofrem por causa de uma paralisação ou perdem a fé na capacidade da empresa para recuperar rapidamente, podem se seus negócios em outro lugar. Isso obviamente atinge o resultado da empresa.

Como tecnólogos, precisamos perguntar o gerenciamento de desastres IT em termos do impacto financeiro na empresa. Descobri isso seja uma tática eficaz persuadir gerenciamento investir tempo e dinheiro em modernizar e teste a estratégia de recuperação de desastres. Leia mais sobre isso na minha postagem de blog recentes aqui.

Os custos de compactação

P. Eu realmente gostaria de usar o recurso de compactação de dados do SQL Server 2008 para reduzir os custos de armazenamento, mas eu já li é somente para data warehouses e que eu vai aumentam os problemas de desempenho enorme se eu tentar usá-lo em uma transação on-line (OLTP) sistema de processamento. Isso é verdade?

R. Você está certo de que o recurso de compactação de dados foi concebido originalmente para uso de depósito de dados. Compactação de dados reduz o tamanho dos registros da tabela e índice. Isso significa mais registros caibam em uma página de arquivo de dados de 8 KB e, portanto, menos páginas de arquivo de dados são necessários para armazenar os dados no disco. Isso se traduz em menor espaço em disco necessário para o banco de dados que contém os dados compactados, que por sua vez podem levar a economias significativas como menos armazenamento corporativo é necessário.

A compensação, claro, é que os dados precisam ser descompactado antes do uso. Os dados não serão descompactados quando for lida no pool de buffers do SQL Server (na memória cache de páginas de arquivo de dados). Ele só será descompactado quando, na verdade, são necessárias para satisfazer uma consulta. Descompactação usa recursos de CPU, então uma compensação é o uso de espaço em relação a recursos da CPU.

Um depósito de dados típica tem uma grande quantidade de dados (pense centenas de gigabytes a vários terabytes). O padrão de acesso para que os dados geralmente é uma grande quantidade de dados de leitura para o pool de buffer, processada uma vez e, em seguida, não usado novamente por muito tempo suficiente que tiver espirado fora da memória.

Com esse padrão de acesso, faz sentido para minimizar o número de operações de e/S de leitura ao compactar os dados para um tamanho muito menor. Isso requer menos páginas do arquivo de dados do SQL Server para armazená-lo e menos operações de i/O para ler essas páginas. Isso geralmente leva a conclusão mais rápida desses tipos de consultas. Portanto, outra desvantagem é a velocidade de consulta contra os recursos de CPU (para descompactar os dados).

Se você considerar uma carga OLTP, normalmente há muito mais alta volatilidade de dados que em um data warehouse. Isso significa que se você usar a compactação de dados, você vai experimentar um alto custo de CPU devido a descompactação constante de dados que está sendo lidos e compactação de dados inseridos ou atualizados. Você precisará examinar mais cuidadosamente as compensações ao considerar a compactação de dados para um banco de dados OLTP.

Voltando à pergunta, embora a compactação de dados originalmente visava data warehouses, muitos clientes do SQL Server viram têm uma grande quantidade de CPU "sala de cabeça" em seus servidores. Eles podem arcar com o uso de CPU extra e potencialmente mais longos tempos de execução de consulta para obter a economia de espaço grande e a economia de custos de armazenamento associada ao uso de compactação de dados. Compactação de dados pode pode ser útil para ambientes de OLTP. Apenas certifique-se de que avaliar os custos de desempenho e a economia de espaço para sua carga de trabalho antes de entrar em produção.

Para economia de espaço, você pode usar o procedimento sp_estimate_data_compression_savings para lhe dar uma idéia do que a economia de porcentagem, que você pode esperar. É importante fazer isso porque ativar (ou desativar) é feita a compactação de dados usando uma operação de reconstrução. Isso pode ser caro por si só. Para obter mais informações, consulte o white paper, "compactação de dados: Estratégia, planejamento de capacidade e práticas recomendadas. "

Paul S. Randal

**S de Paul. Randal**é o diretor administrativo da SQLskills.com, diretor regional da Microsoft e do SQL Server MVP. Ele trabalhou na equipe do mecanismo de armazenamento do SQL Server da Microsoft entre 1999 e 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 é 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. He bloga em SQLskills.com /blogs/Paul, e ele pode ser encontrado no Twitter no twitter.com/PaulRandal.