Uma das funções críticas para garantir o desempenho ideal do SQL Server é o ajuste. Isolar a causa raiz da degradação nem sempre é uma tarefa fácil, justamente pelo fato que o mesmo depende de vários fatores como memória, configuração, design de consulta e uso de recursos.
Mas ao invés de esperar que os problemas ocorram, é possível investir no ajuste proativo do SQL Server. Essa atitude garantirá que as instruções sejam executadas de forma mais eficiente, colaborando para o SQL localizar a rota de entrada e saída de forma mais ágil.
Se você busca agilizar o desempenho ou apenas evitar que os problemas surjam, confira três dicas para melhorar o ajuste de desempenho do seu SQL.
Dica 1: Otimize o seu TempDB
Um dos vilões mais conhecidos da degradação do desempenho é o TempDB configurado incorretamente. Se você tem o costume de lotar o seu TempDB, é o momento de repensar essa prática.
O primeiro passo é verificar o tamanho do TempDB. Não há uma regra definida, mas a recomendação é mantê-lo em 25% do seu maior banco de dados ou no mesmo tamanho do seu maior índice. Isso evita ter que aumentar o TempDB durante as reconstruções.
Outro fator relevante é as características da unidade, quanto mais rápida, melhor. Isso porque, quando o TempDB é inserido na mesma unidade que o sistema operacional ou em uma que seja lenta, muito provavelmente haverá problemas de desempenho do banco de dados. Neste sentido, se for viável, mantenha o TempDB em um SDD local dedicado. Caso não seja possível, mantenha-o em seu próprio volume dedicado com espaço em disco pré-alocado suficiente.
Outra dica é conservar os arquivos de dados e logs em locais diferentes e estabelecer um alto valor fixo para o crescimento automático do TempDB. A consequência de não o fazer é ser uma sobrecarga desnecessária sempre que o TempDB lotar.
Agora, a questão que fica é: quantos arquivos de dados TempDB são necessários? Na teoria, haverá um arquivo de dados TempDB para cada CPU lógica, mas não mais do que oito no total (com algumas exceções). Por exemplo, se você tiver quatro CPUs lógicas, precisará de quatro arquivos de dados TempDB. Se você tiver 12 CPUs lógicas, poderá ter oito arquivos de dados TempDB.
Dica 2: Evite gargalos de desempenho
Podemos elencar três tipos principais de afunilamento de desempenho do SQL Server que colaborar para o seu mal desempenho, são eles: CPU, memória e E/S. Confira um pouco mais sobre cada um deles:
Gargalos da CPU
Causa: Recursos de hardware insuficientes
Sintomas: Uso recorrentemente alto do processador
Métricas a serem monitoradas: % de tempo do processador, solicitações em lote/seg, compilações SQL/s e recompilações SQL/s
Gargalos de memória
Causa: Limitações na memória disponível e pressão de memória causadas pelo SQL Server, sistema ou outra atividade de aplicativo
Sintomas: Capacidade de resposta lenta do aplicativo, lentidão geral do sistema e falhas do aplicativo
Métricas a serem monitoradas: Memória disponível (KB), memória total do servidor (KB), memória do servidor de destino (KB), páginas/seg, páginas de pontos de verificação/s, gravações lentas/s e taxa de acertos do cache do buffer
Gargalos de E/S
Causa: Leitura e gravação excessivas de páginas de banco de dados de e para o disco
Sintomas: Tempos de resposta longos, lentidão de aplicativos e tempos limite de tarefas
Métricas a serem monitoradas: Comprimento médio da fila de disco, média de disco s/leitura, média de disco s/gravação, %Tempo de disco, média de leituras de disco/s e Média de gravações de disco/s
Dica 3: Assegure-se de que os índices sejam projetados corretamente
Se você souber usar da forma correta os índices, terá uma excelente possibilidade acelerar determinadas operações de SQL Server. Neste sentido, a configuração correta das quatro áreas abaixo, ajuda a assegurar que os índices sejam projetados corretamente.
Tamanho da tabela
O tamanho da tabela é um fator de deve ser levado em consideração. Se ela for muito pequena, é mais apropriado para SQL Server buscar a tabela inteira ao invés de pesquisar índices. Já no caso contrário, é necessário pesar a sobrecarga potencial ao decidir quais tabelas se beneficiariam dos índices.
Tipos de índice
Na teoria, toda a tabela de banco de dados pode ter um índice agrupado e um número infinito de índices não agrupados, porém é recomendado analisar se é mesmo a melhor saída.
Isso porque, ter uma quantidade elevada de índices não reunidos pode diminuir drasticamente as operações de Inserção e Atualização. Sendo assim, aderir a um índice clusterizado e ao número mínimo de índices não clusterizados indispensáveis é uma opção mais assertiva.
Armazenamento de índice
A seleção dos critérios de armazenamento adequados para índices é essencial para o desempenho de E/S na fase de projeto. Índices agrupados particionados e índices não clusterizados podem ser armazenados no mesmo grupo de arquivos que a tabela principal ou podem ser armazenados em um grupo de arquivos diferente. O armazenamento de um índice não reunido em um grupo de arquivos localizado em uma unidade de disco diferente pode melhorar o desempenho das consultas que o usam, pois ele não é afetado pela leitura simultânea dos dados e das páginas de índice SQL que ocorrem em unidades de disco diferentes.
Fator de preenchimento
FILLFACTOR esclarece a porcentagem de espaço que será preenchida em cada página de dados ao criar um índice. Os valores de FILLFACTOR podem oscilar de 0% a 100%. Ao montar seu índice, selecione um valor FILLFACTOR que otimize o uso da página e, ao mesmo tempo, minimize o risco de fragmentação excessiva do índice.