O SQL Server é uma das melhores plataformas de banco de dados para colocar sua aplicação em funcionamento de forma rapida. A interface gráfica do SQL Server Management Studio permite criar tabelas, inserir dados, desenvolver procedimentos armazenados e muito mais. No inicio tudo são flores, você consegue colocar seu aplicativo funciona muito bem nos ambientes de produção, teste e desenvolvimento sem nenhum problema. Mas no momento que o uso do aplicativo aumenta e o tamanho do banco de dados também cresce, você pode começar a notar alguma degradação do desempenho, começa aparecer os problemas e o o pior acontece as reclamações dos usuários.
Em ambientes de produção o monitoramento e o ajuste do desempenho são atividades constantes de um DBA, os primeiros sinais de problemas de desempenho surgem das reclamações dos usuários. Uma tela que costumava carregar imediatamente agora leva alguns segundos, um relatório que costumava levar alguns minutos para ser executado agora leva uma hora. mas com algumas etapas e técnicas, você pode monitorar esses problemas e ajustá-los adequadamente, para que seus aplicativos de banco de dados estejam sempre em execução com desempenho ideal.
Os servidores de banco de dados são complexos para gerenciar e configurar. Mas neste artigo, abordaremos uma série de dicas que você pode achar úteis para otimizar seu ambiente do SQL Server.
Aqui estão 30 dicas que você pode usar para aproveitar ao máximo seu desempenho do Microsoft SQL Server.
Analisaremos as técnicas básicas de que todos os DBAs e desenvolvedores devem estar cientes para garantir que seus aplicativos de banco de dados estejam com desempenho máximo.
1 – HARDWARE COMPARTILHADO
Não compartilhe o hardware do servidor SQL com outros serviços
Se outras cargas de trabalho estiverem em execução no mesmo servidor em que o SQL Server está sendo executado, a memória e outros recursos de hardware serão compartilhados entre essa carga de trabalho. Nessa condição, será mais difícil identificar a causa dos maus desempenhos à medida que eles surgirem. O ideal que o servidor de Banco de Dados seja o dono de todo o Servidor ou maquina Virtual;
2 – CONTROLADOR DE DISCO
O SQL Server pode aproveitar a dispersão de dados em várias unidades de disco. No entanto, um controlador de armazenamento possui limites na taxa de transferência. E, ao usar vários discos, é melhor usar também vários controladores – para evitar gargalos de I / O.
3 – DISCOS SEPARADOS PARA DADOS MAIS USADOS
Forneça um disco separado para tabelas e índices muito usados
Se você tiver acessado tabelas ou índices intensamente, aumentará o desempenho alocando esses objetos em seu próprio grupo de arquivos em um disco físico separado.
4 – CARGA DE TRABALHO
Conheça sua carga de trabalho e monitore as métricas de desempenho
Essa é a base de todo trabalho de otimização: você deve primeiro saber como usar os recursos para otimizar seu uso. Em geral, o SQL Server se beneficia de ter bastante memória, mas dependendo da carga de trabalho, você pode ter diferentes padrões de uso para processador e discos. Novamente, monitore constantemente as métricas do sistema ao longo do tempo e concentre seus esforços nos recursos com os mais altos padrões de uso.
5 – CARGA DE DADOS OLAP E OLTP SEPARADAS
As cargas de trabalho OLAP (Online Analytical Processing) e OLTP (Online Transaction Processing) no mesmo ambiente devem ser projetadas para não interferir umas nas outras. As cargas de trabalho OLAP e de relatório tendem a ser caracterizadas por consultas menos frequentes, mas de longa execução e mais pesadas. As cargas de trabalho OLTP, por outro lado, tendem a ser caracterizadas por muitas transações pequenas que retornam algo ao usuário em menos de um segundo. Consultas de longa execução para análise, relatórios ou consultas ad-hoc podem bloquear inserções e outras transações na carga de trabalho OLTP até que a consulta OLAP seja concluída. Se você precisar suportar as duas cargas de trabalho, considere criar um servidor de relatório que ofereça suporte às cargas de trabalho OLAP e de relatório. Se você executar muitas análises, considere usar o SQL Server Analysis Services para executar essas funções.
6 – USAR BANCO DE DADOS DE TAMANHO FIXO
Se você alocar espaço em disco para um banco de dados no momento da criação pode ter certeza de que o espaço alocado será contíguo e, portanto, obterá os melhores desempenhos possíveis. Mas se você você definir a opção crescimento automático, o espaço em disco será alocado somente quando necessário e provavelmente será muito fragmentado. Um banco de dados fragmentado terá desempenho abaixo de um contíguo. Portanto, especialmente na produção, é melhor alocar o espaço necessário quando você cria o banco de dados.
7 – COLOQUE O TEMPD EM UM DISCO SEPARADO
O arquivo de dados tempdb é uma área de armazenamento temporário usada ao executar operações como GROUP BY ou ORDER BY. Manter o tempdb em um disco separado garantirá que essa operação não tenha um impacto negativo no desempenho de outras operações do banco de dados. Lembrando que o TEMPDB e recriado toda vez que seu servidor SQL Server e iniciado.
8 – DADOS E LOG DE DADOS SEPARADOS EM DISCOS DIFERENTES
O arquivo de dados e os logs têm diferentes padrões de uso: o banco de dados é lido e gravado de maneira quase aleatória, enquanto os logs são geralmente gravados sequencialmente. Separá-los em diferentes discos físicos permite que a operação seja executada com o melhor desempenho possível.
9 – USAR O PARTICIONAMENTO DE TABELA
O particionamento permite manter partes da mesma tabela em diferentes discos físicos. Usando uma partição para separar os dados atuais dos dados históricos, você pode manter todos os dados na mesma tabela. Mas mantenha apenas os dados atuais em seus discos mais rápidos e melhore o desempenho de suas consultas.
10 – CRIAR INDÍCES
índices permitem pesquisar dados nas tabelas do banco de dados da maneira mais otimizada. Faça um mapeamento das tabelas que que recebem mais consultas e também considere a criação de índices em colunas freqüentemente usadas nas cláusulas WHERE, ORDER BY e GROUP BY. Essas colunas são as melhores candidatas para índices
11 – CRIAR ÍNDICES AGRUPADOS
Crie índices agrupados em vez de não agrupados para aumentar o desempenho das consultas que retornam um intervalo de valores que contêm as cláusulas GROUP BY ou ORDER BY e que retornam os resultados da classificação. Como uma tabela pode ter apenas um índice em cluster, você deve escolher as colunas para esse índice com muito cuidado. Analise todas as suas consultas, escolha as consultas usadas com mais frequência e inclua no índice clusterizado apenas as colunas que oferecem os maiores benefícios de desempenho de sua criação.
12 – CRIAR ÍNDICES NÃO CLUSTERIZADOS
Crie índices não clusterizados para aumentar o desempenho das consultas que retornam menos linhas e onde o índice tem boa seletividade. Uma tabela pode ter até 249 índices não clusterizados, mas você deve considerar cuidadosamente a criação de índices, porque cada índice pode ocupar espaço em disco e ter impacto na modificação de dados.
13 – RECOSTRUA ÍNDICES PERIODICAMENTE
Enquanto você atualiza, exclui e cria registros em suas tabelas, seus índices ficam fragmentados e o desempenho pode diminuir com o tempo. Você deve reconstruir índices periodicamente para manter o desempenho no melhor nível. Para tabelas com um índice clusterizado, reconstruir esse índice significa desfragmentar a tabela que também é adjuda no desempenho.
14 – ELININAR ÍNDICES QUE NÃO SÃO USADOS
Limite o número de índices se o aplicativo atualizar dados com muita frequência. Como cada índice ocupa espaço em disco e retarda a adição, exclusão e atualização de linhas, você deve criar novos índices somente após analisar o uso de dados, os tipos e frequências de consultas executadas e como suas consultas usarão os novos índices. Em muitos casos, as vantagens de velocidade da criação dos novos índices superam as desvantagens do espaço adicional usado e a modificação lenta das linhas.
Use o Assistente de Índice para identificar índices que não são usados em suas consultas.
15 – FAÇA LEITURA APENAS DOS DADOS QUE VOCÊ PRECISA
Às vezes, você pode se sentir tentado a usar SELECT * FROM … ao escrever suas consultas, dessa forma recuperará todos os campos de uma tabela quando precisar apenas de alguns. Para reduzir o tamanho dos dados transferidos, você deve especificar a lista apenas das colunas necessárias….SELECT ID_PROD,NOME,VENDAS FROM…….
16 – ISOLATION LEVEL
Use as dicas de nível de bloqueio e isolamento para minimizar o bloqueio
Nas transações, use a opção “WITH NOLOCK” quando possível. Você evitará longos tempos de espera para instâncias simultâneas do seu aplicativo acessando as mesmas linhas.
17 – TIPAGEM DE VARIÁVEIS CORRETA
Escolha o menor tipo de dados que funciona para cada coluna.
As conversões explícitas e implícitas podem ser caras em termos de tempo que leva para executar a conversão. Também há um custo em termos de varreduras de tabela ou índice que podem ocorrer porque o otimizador não pode usar um índice para avaliar a consulta.
18 – USE VARCHAR
Colunas que usam o tipo de dados de texto têm sobrecarga extra porque são armazenadas separadamente nas páginas de texto / imagem e não nas páginas de dados. Use o tipo varchar em vez de texto para obter um desempenho superior em colunas que contêm menos de 8.000 caracteres.
19 – USE UNICODE SOMENTE QUANDO NECESSÁRIO
Os tipos de dados Unicode, como nchar e nvarchar, ocupam o dobro do espaço de armazenamento em comparação com os tipos de dados ASCII, como char e varchar.
20 – RECURSOS APROPRIADOS AO DESIGN DO ESQUEMA
Reserve um tempo e dedique os recursos necessários para reunir os requisitos de negócios – para projetar o modelo de dados correto e testar o modelo de dados. Verifique se o seu design é apropriado para o seu negócio e se o design reflete com precisão os relacionamentos entre todos os objetos. Alterar um modelo de dados após o sistema já estar em produção é caro, demorado e afeta inevitavelmente muito código.
21 – EVITAR AÇÕES LONGAS DE GATILHOS
Código do gatilho geralmente é ignorado quando os desenvolvedores avaliam os sistemas quanto a problemas de desempenho e escalabilidade. Como os gatilhos sempre fazem parte das transações de chamada INSERT, UPDATE ou DELETE, uma ação de execução demorada em um gatilho pode fazer com que os bloqueios sejam mantidos por mais tempo do que o pretendido, resultando no bloqueio de outras consultas. Mantenha seu código de gatilho o mais pequeno e eficiente possível. Se você precisar executar uma tarefa de longa duração ou com muitos recursos, considere usar o enfileiramento de mensagens para realizar a tarefa de forma assíncrona.
22 – EVITE OPERADORES CAROS PARA AMBIENTE
Alguns operadores em junções ou predicados tendem a produzir operações com muitos recursos. O operador LIKE com um valor entre caracteres curinga (“% a value%”) quase sempre causa uma varredura de tabela. Esse tipo de verificação de tabela é uma operação muito cara devido ao curinga anterior. Os operadores “LIKE” com apenas o curinga de fechamento podem usar um índice porque o índice faz parte de uma árvore B +, e o índice é percorrido ao corresponder o valor da string da esquerda para a direita. Operações negativas, como <> ou NOT LIKE, também são muito difíceis de resolver com eficiência. Tente reescrevê-los de outra maneira, se puder. Se você está apenas verificando a existência, use a construção “SE EXISTE” ou “SE NÃO EXISTE”. Você pode usar um índice. Se você usar uma verificação, poderá interromper a verificação na primeira ocorrência.
23 – AVALIE O PLANO DE EXECUÇÃO DA CONSULTA
No SQL Query Analyzer, ative a opção Exibir plano de execução e execute sua consulta em uma carga de dados significativa para ver o plano criado pelo otimizador. Avalie esse plano e identifique os bons índices que o otimizador poderia usar. Além disso, identifique a parte da sua consulta que leva mais tempo para ser executada e que pode ser melhor otimizada. Compreender o plano real que é executado é o primeiro passo para otimizar uma consulta. Assim como na indexação, leva tempo e conhecimento do seu sistema para poder identificar o melhor plano.
24 – MANTENHA AS ESTATÍSTICAS ATUALIZADAS
As estatísticas são usadas pelo SQL Server Query Optimizer para selecionar o melhor índice a ser usado ao extrair dados da sua tabela. Se as estatísticas não estiverem atualizadas, você pode acabar mantendo um índice que nunca é usado.
25 – TAREFAS DE UM ADMINISTRADOR DE BANCO DE DADOS
Não se esqueça de levar em consideração as tarefas de administrador de banco de dados ao pensar em desempenho. Por exemplo, considere o impacto que backups de banco de dados, atualizações estatísticas, verificações de DBCC e reconstruções de índice têm em seus sistemas. Inclua essas operações em sua análise de teste e desempenho.
