SQL Q & A: Velocidade e tamanho importam
A velocidade relativa das consultas do SQL Server e o tamanho dos arquivos de dados e dos registros de transação parecem ser os fatores principais do desempenho.
Paul S. Randal
Plano de consulta
**P.**Recentemente tivemos um problema com uma consulta que estava tirando um tempo para ser executado. A consulta correu mais rápido depois que reconstruímos o índice clusterizado na tabela envolveu. Eu não podia ver qualquer varreduras no plano de consulta, então por que a fragmentação atingiram o tempo de execução de consulta?
**.**Neste caso, parece que a fragmentação do índice não tinha nada a ver com o tempo de execução de consulta. Um plano de consulta não ideal foi provavelmente a fonte do problema.
Quando você faz uma recompilação de índice, todas as estatísticas de coluna de índice são automaticamente reconstruídas. É o equivalente a uma verificação completa. Todos os valores de coluna são considerados ao criar as estatísticas, por isso cria uma representação completa da distribuição do valor comum.
Todos os planos de consulta criados usando essas estatísticas são essencialmente invalidados e serão recompilados. Recompilar um plano significa que da próxima vez que você executa a consulta, o otimizador de consulta vai passar pelo processo de escolher uma maneira nova e mais ideal de produzir os resultados da consulta. Este é o plano de consulta.
Neste caso, sua recriação de índice desencadeou uma recompilação de plano de consulta. O novo plano foi mais ideal do que o anterior. Há um número de possíveis motivos para isso. O plano de consulta mal poderia ter sido altamente ideal e permitidas consultas rápidas quando foi compilado. Como a distribuição de valores de dados dentro da tabela alterada ao longo do tempo, o plano de consulta poderia ter se tornado menos ideal.
O antigo plano de consulta poderia ter sido usando um índice não clusterizado baseado no fato de que uma determinada coluna (parte do índice não clusterizado) foi altamente seletiva. Portanto, faz sentido usar o índice não clusterizado para encontrar valores de dados e, em seguida, outras colunas da tabela própria. Isso é chamado uma operação de pesquisa da chave.
Se a distribuição de dados mudou drasticamente que a coluna não era altamente seletiva, isso poderia ter causado um grande número de pesquisas de chaves caros. Considerando a nova distribuição de dados, um plano melhor seria usar um índice não clusterizado diferente.
Quando o índice clusterizado foi reconstruído, as estatísticas foram atualizadas. Isto causou uma recompilação de plano, que escolheu o índice não clusterizado mais seletivo. Este por sua vez produziu um plano mais eficiente.
Enquanto eu estou hipotetizar sobre a causa da aceleração a consulta, você pode ver o que quero dizer sobre a recompilação de índice simplesmente ser o gatilho para recompilação do plano. Ele poderia não ter diretamente corrigido a causa raiz do problema de desempenho em primeiro lugar.
Mais arquivos, mais espaço
**P.**Eu tenho um grupo de arquivos com dois arquivos e ambos são muito completo. Quero adicionar alguns mais espaço para o grupo de arquivos, então eu vou adicionar mais dois arquivos e depois ter o SQL Server reequilibrar os dados em todos os quatro arquivos. Isso é possível?
**.**Infelizmente, não há uma boa maneira de reequilibrar os dados através de arquivos em um grupo de arquivos depois que você adicionar novos arquivos para espaço extra. Eu tenho um blog no passado sobre como ter mais de um arquivo por grupo de arquivos pode levar a ganhos de desempenho para algumas cargas de trabalho. É de conhecimento comum que é esse o caso.
Isso é uma grande generalização, embora. Quanto ganho, você vai conseguir depende do subsistema de I/O, o layout do arquivo de dados e a carga de trabalho. Haverá um ponto em que o número de arquivos de dados torna-se demais e é na verdade um prejuízo de desempenho. Confira esses posts de blog sobre benchmarking vários arquivos de dados e vários arquivos de dados em drives de estado sólido (SSDs).
SQL Server simplesmente não tem um mecanismo de reequilíbrio para dados em um grupo de arquivos. O arquivo de dados onde virá a próxima atribuição é determinado pela alocação round-robin e preenchimento proporcional. Round robin é onde as alocações acontecem de cada arquivo de dados por sua vez. Haverá uma repartição de arquivo de um e, em seguida, uma repartição de arquivo dois, então volta ao arquivo um novamente. No entanto, as alocações são feitas de forma proporcional. Mais as alocações são feitas a partir de arquivos de dados que têm proporcionalmente mais espaço livre do que outros arquivos de dados no grupo de arquivos.
A premissa básica do preenchimento proporcional é que cada arquivo tem um coeficiente de ponderação, onde arquivos com menos espaço livre terá uma maior ponderação. Arquivos com muito espaço livre terá um menor coeficiente de ponderação. Os arquivos com ponderações inferiores serão alocados de mais freqüentemente, ou seja, os arquivos com mais espaço livre será atribuído mais.
Tudo isso significa que quando você adiciona novos arquivos para um grupo de arquivos completo, alocações subseqüentes serão provenientes principalmente os novos arquivos. Eles terão muito ponderações de preenchimento proporcional menores do que os arquivos mais antigos que possuem inerentemente mais dados. Os novos arquivos tornam-se pontos quentes de alocação, levando potencialmente menor desempenho geral com algumas cargas de trabalho.
Você não pode contornar o algoritmo de preenchimento proporcional. Nem você pode alterar os coeficientes de ponderação. Mesmo tentar algo como reconstruir os índices no grupo de arquivos não vai funcionar, como as dotações para os novos índices virá de novos arquivos de dados.
Se você quiser adicionar mais arquivos para um grupo de arquivos, a melhor maneira é criar um novo grupo de arquivos com mais arquivos. Mova os dados de tabela e índice para o novo grupo de arquivos usando o CREATE INDEX... COM (DROP_EXISTING = ON) comando, especificando o novo grupo de arquivos como o local de destino. Depois de ter movido todos os dados, você pode soltar o grupo de arquivos antigo, vazio. Você ainda pode mover dados de linha de negócios para o novo grupo de arquivos, usando algum truque de De Kimberly Tripp.
Limpar o registro
**P.**Recentemente tive um problema com um arquivo de log de transação que cresceu muito grande. Eu tenho sido incapaz de reduzi-lo. Você pode sugerir algumas coisas para me dar?
**.**Existem duas causas comuns de uma SHRINKFILE (DBCC) não funciona corretamente no log de transação de verificação de consistência de banco de dados. Como uma nota lateral, encolher um arquivo de log não apresentar desempenho prejudicial a fragmentação do índice da maneira que faz um psiquiatra de arquivo de dados. No entanto, ainda deve ser uma operação de rara.
Um arquivo de log do psiquiatra simplesmente remove quaisquer partes inativas ou atualmente não utilizadas da transação de log no final do arquivo de log de transação. Essas "partes" do log de transações são conhecidos como arquivos de log virtuais (VLFs). Há dois problemas que podem impedir que você seja capaz de reduzir VLFs: não executar a operação real que permite VLFs tornam-se inativos e não tendo VLFs inativos no final do log de transações.
VLFs tornam-se inativos através de um processo conhecido como "limpar o log." Você pode fazer isso com um ponto de verificação, se usando o modelo de recuperação simples. Você também pode fazer isso com um backup de log de transação, se usando os modelos de recuperação completa ou BULK_LOGGED. Como os registros de log de transação nos VLFs não são exigidos pelo SQL Server de qualquer maneira, você pode fazer os VLFs inativo.
SQL Server pode ainda exigir o log registra para determinadas situações, como se eles são parte de uma transação de longa duração, se eles ainda não foi examinados pelo trabalho do Log Reader Agent de replicação, ou se eles estão em vias de serem enviados para um banco de dados espelho ou réplica de grupo de disponibilidade. Você pode pedir o SQL Server porque um log de transação específica não vai "limpar" usando o seguinte comando:
SELECT [log_reuse_wait_desc] FROM sys.databases WHERE [name] = N'MyDBName';
Use a saída do comando como um indicador do que fazer em seguida. Uma vez que o log de transações é capaz de limpar, se DBCC SHRINKFILE ainda não é capaz de encolher o log, isso significa que ele só foi capaz de encolher até o VLF ativo (ou VLFs). Estes podem acontecer de ser no meio do arquivo de log de transações. Nesse caso, execute novamente a operação de limpeza de registro e depois outra psiquiatra.
Você pode ter que fazer isso algumas vezes e, em última análise, pode ser difícil ou impossível reduzir o log de transações ao seu tamanho mínimo um banco de dados de produção ocupado. No entanto, abordar estes problemas comuns deve ajuda você encolher a transação log arquivo bastante satisfeitos. Você pode ler mais sobre estes temas no meu fevereiro de 2009 TechNet Magazine artigo, "compreensão log e recuperação no SQL Server."
Integridade de e/s
**P.**Eu continuo vendo as mensagens no log de erro de uma das minhas instâncias de SQL Server e/SS tem que ser tentado várias vezes antes que sejam bem sucedidos. Isso parece ameaçador para mim. Você pode explicar o que significam as mensagens?
**.**Essas mensagens são instâncias de 825. Esta mensagem foi introduzida no SQL Server 2005. É uma alerta que seu subsistema I/O tem problemas de integridade.
Se SQL Server emite uma leitura I/O e a falha de I/O (ou o sistema operacional diz SQL Server falha de e/s, ou dados retornados pelo sistema operacional são julgados pelo SQL Server como corrupto), SQL Server tentará novamente a operação de leitura mais quatro vezes para ver se um deles terá êxito. A premissa para isso é que às vezes subsistemas I/O tem falhas transientes, repetir assim uma falha de e/s pode funcionar em uma tentativa subseqüente. Isso evita a possibilidade imediata de inatividade.
Se nenhuma das tentativas de repetição bem-sucedida, SQL Server gera um erro 823 ou 824, e a conexão está quebrada (como esses erros são gravidade 24). Se uma das tentativas de repetição for bem-sucedido, a carga de trabalho continua como normal, e SQL Server grava a mensagem 825 no log de erros.
825 Mensagem tem o seguinte formato:
Msg 825, Level 10, State 2, Line 1.
Isso significa uma leitura do arquivo "J:\SQLskills\MyDatabase_DF1. FDN"no deslocamento 0 × 000004AA188000 sucedeu após falhar uma vez com o erro: soma de verificação incorreta (espera-se: 0 × 33d1d136; real: 0 × 0a844ffd). Mensagens adicionais no SQL Server erro log e sistema de log de eventos podem fornecer mais detalhes.
Esta condição de erro ameaça a integridade do banco de dados e você vai precisar corrigir a situação. Complete um total de DBCC CHECKDB. Este erro pode ser causado por muitos fatores. Para obter mais informações, consulte SQL Server Books Online. O que ele está realmente dizendo é que o subsistema I/O está começando a falhar. Uma instalação similar existe no Exchange Server, onde surgiu a idéia para esse mecanismo.
Embora esse recurso é útil, a mensagem 825 é apenas gravidade 10 (que significa informativos). A menos que você está examinando os logs de erro ou tiver um agente de alerta para mensagem 825, essas mensagens críticas podem passar despercebidas. No entanto, você deve ter um alerta Prepare para 825 mensagens e agir como uma mensagem de repetição de leitura acontece. Você pode ler mais sobre esta mensagem e sobre como configurar um alerta para pegá-lo neste blog post.
.jpg)
Paul S. Randal é o diretor administrativo da 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 desde 1999 a 2007. Ele escreveu o DBCC CHECKDB/repair para o 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 é 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..