R: & SQL Q jogar a carta de índice

Artigo
08/19/2016

Os índices do SQL podem ser problemáticos, mas se você ficar de olho neles e tentar evitar problemas, verá que eles podem ser fáceis de gerenciar.

S de Paul. Randal

O DBA acidental

**P.**Estou "DBA não oficial" em minha empresa. Temos está usando cada vez mais, o SQL Server e instâncias do SQL Server ameaçado por toda a empresa e sabe que precisamos de um DBA real para ajudá-lo para fora. Enquanto estamos está contratando uma pessoa, é preciso ser capaz de descobrir o que está fazendo que algumas instâncias são executados mais lentamente. Você tem qualquer recomendações gerais para onde devo começar procurando?

**R.**Esta é uma boa pergunta. Pode ser frustrante ter uma instância do SQL Server não está bom desempenho e não sabe por onde começar a procurar a causa. Há todos os tipos de coisas que podem contribuir para a degradação do desempenho, para que eu sempre mais fácil simplesmente perguntar ao próprio SQL Server.

SQL Server controla os dois conjuntos de informações: estatísticas de i/O e estatísticas de espera. Elas devem fornecer uma boa idéia de onde o problema reside.

A maioria das instalações do SQL Server nos dias de hoje são vinculada/S-(ou seja, que seu desempenho é restrito por algo a ver com leitura e gravação de dados). A lentidão pode ser um subsistema de e/S lento, uma rede lenta, conectando a uma SAN para o servidor, não há memória suficiente no servidor que está forçando o SQL Server para permutar as páginas dentro e fora do memória, a estratégia de indexação ruim ou uma série de outras coisas.

Você pode usar o sys.dm_io_virtual_file_stats de DMV (exibição de gerenciamento dinâmico) para ver o que SQL Server sabe sobre o volume de i/O, vagas e atrasos para todos os i/O para os arquivos de dados e log. Você pode encontrar o subsistema de e/S não é o ponto de acesso, mas i/O ainda pode ser o problema. O subsistema de e/S pode ser forma inadequada enfrentar a carga de i/O.

É onde entra a outra parte do quebra-cabeça: aguardar estatísticas. Mantém controle de SQL Server sempre que um segmento de execução tem de esperar para um recurso disponível e quanto tempo o segmento tiveram de aguardar. Você também pode trabalhar fora quanto o segmento tiveram de aguardar após ter sido notificada da disponibilidade do recurso, mas antes de poder ser executado em uma CPU. Agregando dados, é fácil ver as principais áreas causando esperas para SQL Server. Isso lhe dá um ponteiro de onde começar a procurar a causa.

Esta é uma visão detalhada dessa metodologia. Para uma discussão mais detalhada, incluindo um script que você pode usar, leia a minha postagem de blog "Aguardar estatísticas.” Ele também possui os resultados de uma pesquisa com leitores de mais de 1800 instâncias do SQL Server e os tipos de espera predominante com explicações. Linha inferior: não desperdice tempo poking ao redor no SQL Server até que você pediu SQL Server o que pensa sobre o problema.

Análise de índice ausente

**P.**Logo descobri ausente DMVs de índice. Agora eles estão me dizendo tenho centenas de falta de índices em uma instância do SQL Server. Devo apenas criar todos eles ou que irá causar problemas?

**R.**Não crie imediatamente todos os índices sem primeiro fazer algumas análises. O processador de consultas do SQL Server 2005 e versões posteriores pode determinar quando um índice beneficiaria o plano para uma consulta (ou em lote ou procedimento armazenado). Ele faz isso ao compilar o plano de consulta.

Toda vez que ele determina que existe um índice faltando, anota esse fato. Ele também mantém uma contagem de quantas vezes cada índice ausente poderia foi usada, junto com o aperfeiçoamento projetado no plano de consulta tivesse existido de índice no momento em que o plano de consulta foi compilado.

Você pode acessar todas essas informações usando três DMVs (sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats e sys.dm_db_missing_index_details). Há também um departamento de trânsito que informa quais colunas em uma tabela estão faltando os índices (sys.dm_db_missing_index_columns). As três primeiras DMVs são mais comuns. Consultá-los juntos para a maneira mais fácil de obter esses dados. Bart Duncan amplamente usados no script "Você está usando DMVs de índice ausente do SQL?” também pode ajudar.

Estas são informações valiosas, mas você deve fazê-lo com cuidado. Em primeiro lugar, há um bug potencial no índice DMVs ausente. Ele poderá ajudá-lo um índice que existe realmente está faltando. Esse bug será corrigido na próxima versão do SQL Server. Você pode ler mais sobre isso na minha postagem de blog, "Faltando bug DMVs de índice que poderá custar sua sanidade.”

Em segundo lugar, o mecanismo que determina um índice ausente em um processador de consultas só considera se um índice é útil para a consulta que está sendo compilada. Ele não considere o impacto de desempenho possível para inserir/atualizar/excluir as operações que precisam manter esse índice. Isso pode ser enorme se a tabela tiver proporcionalmente muitas outras alterações que lê. Ela também não considerar o tamanho do índice criado. Que é um contraponto que só podem ser feitas.

Por último, ele procura o índice absoluto melhor ajudar o plano de consulta que está sendo compilado. Por exemplo, pode ser uma tabela com colunas de 30 e um índice de cluster e uma consulta que solicita 25 das colunas da tabela. O mecanismo de determinação do índice ausente recomendaria a criação de um índice não clusterizado para cobrir a consulta de coluna de 25. Na maioria dos casos, que não faria sentido.

Use script de Duncan para examinar a saída ausente-índices agregados. Em seguida, examine os índices de 10 ou 20 principais e fazer algumas análises para determinar se eles são realmente vale a pena criar. Na maioria das vezes, você encontrará alguns que não têm criando, portanto, sempre vale a pena realizar essa análise.

Será que não podemos nos entender?

**P.**Sou um de uma equipe de DBAs da nossa empresa que lida com várias equipes de desenvolvimento de aplicativo. Não há constante animosity entre as equipes. Ele é prejudicial para o ambiente de trabalho. Você tem alguma idéia de como suavizar as relações entre as equipes?

**R.**Este é um problema comum que pode tornar o ambiente de trabalho desagradáveis com animosity, desconfiança dos e grudges. Nenhum dele ajuda a produtividade e a empresa sofre. Felizmente, há uma solução. É fácil de descrever, mas é mais difícil de colocar em prática:

É necessário treinar entre si. Cada equipe precisa compreender a outra equipe motivações e o que eles acham que são os seus limites de responsabilidade. Você ficará surpreso com o que cada equipe pensa que devia estar fazendo a outra equipe.
Cada equipe precisa compreender os pontos problemáticos para outras equipes. Você pode fazer isso de forma anônima, sem fazer coisas pessoais.
Cada equipe precisará treinar outras equipes em como fazer outras equipes de trabalho afeta. Por exemplo, digamos que o dev team grava alguns códigos, somente os testes com um pequeno conjunto de dados e lança ao longo da parede em produção – e impressionantemente falhar. Se a equipe de desenvolvimento espera que a equipe DBA para solucionar e corrigir o código, que é claramente um processo quebrado.

Reconhecendo e Noções básicas sobre o problema são a única maneira de se motivar a ambos os lados para trabalhar na direção de uma solução que fará com que o ambiente de trabalho tranqüilo e produtivo novamente.

Normativa complicado indexação

**P.**Eu sou um administrador do SharePoint e sei que também uma quantidade razoável sobre o SQL Server. O SQL Server 2008 que hospeda nossos bancos de dados do SharePoint tem muita fragmentação do índice. Isso afeta o desempenho do SharePoint. Eu sei que não é possível alterar os índices, mas existe algo que eu possa fazer além de constantemente ter que recriá-los?

**R.**Reconstrução de índices constantemente gera uma carga pesada em SQL Server em termos de e/S e CPU recursos, geração de log de transações e potencialmente bloqueando outros processos. Mesmo que executa o db_index_physical_stats DMV para determinar os índices fragmentados pode ser um desperdício de recursos pesado.

Muitos índices se tornam fragmentados em um ambiente do SharePoint porque o uso de esquema de banco de dados do SharePoint GUID de chaves de índice em cluster. Minha esposa, Kimberly discute isso no seu blogue "GUIDs como chaves PRIMÁRIAS e/ou a chave de cluster.”

Quando um índice tem o que é essencialmente uma chave aleatória, inserções de índice acontecem aleatoriamente e levam a um processo chamado de divisões de página. Uma página dividida causas fragmentação, o que é um cara de processo (consulte minha postagem no blog "O custo são divisões de página em termos de log de transações?”). Uma divisão da página ocorre quando uma página está totalmente cheia, mas o espaço é necessária nessa página (por exemplo, quando uma inserção ocorre em um índice com um valor de chave aleatório que deve ser armazenado nessa página). Uma nova página é alocada e aproximadamente metade dos registros da página inteira é movida para a nova página, criando assim o espaço livre. Que é o processo básico.

Você não pode alterar os índices em um banco de dados do SharePoint, como o que violaria o contrato de suporte. No entanto, você pode alterar seu fator de preenchimento de padrão. Quando você cria ou recriar um índice, você pode instruir o SQL Server para deixar uma certa quantidade de espaço livre nas páginas de índice para permitir inserções aleatórias. Isso significa que é mais provável que páginas de índice já tem espaço neles para novos registros sem exigir uma divisão da página dispendiosos. Definindo um fator de preenchimento dos meios de 80 páginas serão preenchidas a capacidade de 80 por cento quando o índice é recriado, deixando espaço livre de 20 por cento.

Em seguida, a pergunta é, "O que é o fator de preenchimento melhor"? Infelizmente, há uma resposta válida. Para um depósito de dados onde os dados não são alterados e não há nenhuma atividade de inserção de processamento (OLTP) de transações on-line, o fator de preenchimento melhor costuma ser o padrão do SQL Server de 100 (ou seja, não há espaço livre).

Para um ambiente OLTP, a resposta depende de quão rapidamente ocorre a fragmentação e com que freqüência você reconstruí-lo para remover a fragmentação. É uma boa idéia começar com 70 (30 por cento de espaço livre) e monitorar a fragmentação para verificar se é necessário ajustar para cima ou para baixo, ou de índice manutenção mais ou menos freqüentemente.

Paul S. Randal

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