Como configurar o tamanho dos recursos do servidor SQL (RAM, CPU e outros)

Question

É uma história padrão, há luta entre desenvolvedores e administradores. Uma denúncia de que o design do banco de dados e consultas são ruins, enquanto outros dizem que é falta de hardware e quantidade de dados.
Então eu estou perguntando Você é meu IBM x3400 com 2 xenons 2GHz e SCSI raid 5 e 4GB de RAM adequadamente moldado para banco de dados MSSQL de 53 GB com detalhes principais tabelas é em torno de 6,5 milhões de registros em detalhes e 2 milhões em cabeçalhos de documentos enquanto outros são em torno de 100K (como itens).

Estamos constantemente sofrendo com a falta de desempenho ao obter dados do SQL, este servidor é dedicado apenas a ser apenas o SQL Server e a atuar como assinante dos dados replicados de outro servidor SQL.

Outra questão é Como os administradores de banco de dados planejam o tamanho do hardware dos servidores DataBase? Existe alguma abordagem padrão ou é apenas experiência e sentido?

    

Answer 1

• ram de 4 GB é uma piada nos dias de hoje. Então, não - desculpe. Eu acho que você está feito com esse fim. Pode funcionar, mas isso requer padrões de uso específicos. Eu não iria executar um servidor de banco de dados em um hardware de 4 GB fora do princípio - 16 GB de RAM custa praticamente nada para começar.
• O SCSI RAID 5 não é ideal. Dependendo dos padrões de uso, você deve ter um mínimo de DOIS grupos - um rápido para gravações (log), um rápido para leituras (dados). Eu tive um bom sucesso usando um RAID 10 de 4+ discos para OS e LOG e outro para dados. Lembre-se, porém, o banco de dados era muito maior. No seu caso, jogar o RAID 5 e apenas colocar DUAS unidades SSD espelhadas fará sentido, já que seus dados têm apenas 53 GB. Um espelho de dois SSDs provavelmente explodirá seu desempenho de IO por um fator de 100. Você é provável que o IO seja ligado com a ajuda de sua RAM sendo - pelos padrões atuais - patética. Desculpe se isso soa rude, mas um servidor de banco de dados deve ter MAIS RAM do que uma estação de trabalho de desenvolvedor e, dependendo da empresa em que você está, está no mesmo nível ou abaixo disso.

Is there some standard approach or it is just experience and sense ?

Experiência e sentido. Você também pensa em avançar e verificar o que faz sentido em alguns anos. Por exemplo, o SuperMicro tem servidores NICE com espaço para 24 a 72 discos em uma configuração SAS. Assim, você pode evitar usar um SAN (mais caro) e preencher os discos conforme necessário. Outros recebem um pequeno servidor e ficam sem opções. Você também pode obter algumas idéias do teste em uma estação de trabalho normal.

It is standard story, there is fight between developers and administrators.

Não. Não é.

One denunciation that database design and queries are bad while others says It is lack of hardware and amount of data.

Não novamente. Design Db pode ser medido consideravelmente objetivamente. Como em: existem certas abordagens documentadas e conhecidas (que muitos desenvolvedores são basicamente ignorantes). Já ouviu falar da 5ª forma normal?

O mesmo com as consultas. Eu posso realmente ver se uma consulta é executada com eficiência. Não há área cinza real aqui. Dito isso, pode haver trocas, mas se isso for um jogo de culpas, posso ter certeza de que há algo errado.

Com bastante frequência, os desenvolvedores não sabem nada além de "este é um simples seleto" e não têm idéia de como lidar com um banco de dados e, em seguida, tentam lançar hardware sobre o problema. Estive lá, vi isso. Nem sempre, mas é um palpite provável.

    

Answer 2

Com o dimensionamento de RAM, é importante saber qual é o seu "conjunto de trabalho" provável e certificar-se de que você tenha RAM suficiente para isso multiplicado algumas vezes, no mínimo. Seu conjunto de trabalho normal é todas as páginas de índice e dados que são comumente usadas e poder armazenar tudo na RAM com espaço suficiente para consultas "mais excepcionais, mas ainda não incomuns" reduzirá a E / S de disco necessária para operações de leitura consideravelmente .

Por exemplo, um banco de dados de 10 Gb (menor que o seu por uma quantidade considerável, mas a teoria contém o tamanho de seus dados) para um dos aplicativos do cliente contém cerca de 1Gb de páginas de dados ativos: índices e linhas de tabela ser acessado rotineiramente conforme os usuários realizam seus negócios normais. Outros ~ 4Gb são dados mais antigos que não são lidos na maioria das sessões normais (como a maioria das exibições padrão exibe apenas no máximo um mês ou três dados, e os dados, índices e consultas são bem planejados o suficiente para que as páginas de dados mais antigas não sejam vai ser lido a menos que o usuário pede para ver algo mais para trás no tempo (o que é relativamente raro no dia-a-dia) .O final ~ 5Gb é blobs - documentos que os usuários anexaram aos registros e que raramente são acessados mais do que um pouco depois de ser adicionado, exceto durante uma auditoria.Mesmo com esse tamanho de dados, duvido de 2Gb RAM seria suficiente para manter o acesso rápido (o DB que eu estou falando vive em um servidor de 4Gb dedicado a executar MSSQL, outra máquina atua como servidor web e outros serviços relacionados ao aplicativo), então talvez seja necessário repensar o tamanho da RAM para o tamanho dos dados - a RAM é relativamente barata atualmente, portanto, supondo que seu servidor consiga extrair até 8, 12 ou até mesmo 16Gb podem dar um bom retorno sobre o desempenho do investimento.

Quando a E / S do disco não puder ser evitada, eu me afastaria do RAID5 e do RAID6 se os dados veem muita atividade de gravação. O RAID10 padrão (ou arranjos RAID10 um pouco menos padronizados, como aqueles oferecidos pelo driver Linux RAID10 e algumas soluções de hardware) funcionarão também para a maioria das cargas de leitura, notavelmente melhores para a maioria das cargas de gravação e oferecem redundância semelhante (qualquer unidade pode falhar). Se você não quiser pular para quatro unidades, poderá tentar o RAID10 de três unidades (chamado RAID1E pela maioria dos controladores IBM) se suportado pelo seu ambiente. Também vale muito a pena considerar dividir seu array em dois, como a TomTom sugere. Para operações de gravação, você provavelmente descobrirá que ter o log de transações no array RAID1 de duas unidades e os arquivos de dados em outra funcionará significativamente melhor do que usando RAID10 - o desempenho RAID10 de RAID10 pode ser rapidamente eliminado pelo RAID10. natureza de acesso aleatório de gravações de bancos de dados (atualização de dados e páginas de índice que podem estar espalhadas pelo espaço no arquivo, atualizando o log de transações antes de confirmar os dados e marcando-os como confirmados no log quando feito e assim por diante). Separar o log e a atividade do arquivo de dados em diferentes eixos pode significativamente aumentar o desempenho do banco de dados em muitos casos. Se você tiver espaço suficiente para as unidades requeridas, manter o tempdb (ou qualquer que seja o equivalente do RDBMS ao tempdb do MS) e em um terceiro array pode fazer uma diferença significativa também se suas consultas e procedimentos armazenados usarem tabelas temporárias em algum momento pode ser surpreendente em quantas circunstâncias o planejador de consulta considerará usando tempdb nas suas costas!). É claro que o uso de SSDs também pode ser uma resposta para o desempenho de acesso aleatório - se você obter uma melhor relação preço / desempenho com esses ou outros arranjos de matriz (ou os outros e outros arranjos de matriz) depende significativamente seu DB (s) e padrões de acesso típicos.

Uma outra coisa: antes de investir tempo + esforço + dinheiro em reorganizar seu subsistema de E / S, certifique-se de executar algumas métricas de desempenho em horários de pico para garantir que não haja procedimentos incorretos que estejam sobrecarregados com CPU. Às vezes, procedimentos complexos (particularmente aqueles que usam cursores de maneiras menos ideais) podem atrelar o subsistema de memória da CPU por longos períodos (adicionar mais RAM e melhor capacidade de E / S farão pouca diferença aqui) e podem ser otimizados significativamente repensando a cursores / loops ou conseguindo removê-los completamente. Uma mistura de log de rastreamento SQL personalizado e log do Windows Performance Monitor (ou ferramentas de monitoramento equivalentes para pessoas usando uma combinação OS + DB diferente) pode ser uma grande ajuda para descobrir onde estão os principais afunilamentos (falta de memória, desempenho de E / S do que o código ideal, ...) e você deve tentar corrigir um problema até ter certeza de que está corrigindo o problema certo.

    

Answer 3

Eu imagino que o hardware e o código estão com falhas, pelo menos em algum grau.

Primeiro, você pode usar o SQL Profiler e exibições de gerenciamento dinâmico para provar quais consultas são lentas e por que estão lentas. Usando essas ferramentas é bastante fácil em um nível alto, você pode facilmente ver se você está ligado à CPU, ligado ao disco, ligado à memória, etc. Mas entender os planos complexos de consulta e otimização não é algo que você vai aprender sem colocar em algum tempo. Existe um excelente índice em falta e um relatório de consulta lento no Painel de desempenho do SQL que pode ajudá-lo a encontrar alguns resultados acessíveis.

Por outro lado, esse hardware de servidor é positivamente fraco por padrões modernos. Minha estação de trabalho pessoal no escritório é mais poderosa em todos os sentidos (incluindo SSD em vez de discos mecânicos). Estávamos implantando servidores de banco de dados de 64 bits com > 16 GB de RAM cinco anos atrás . O hardware é a parte mais barata de qualquer operação de TI - certamente mais barata do que as dezenas a centenas de horas-homem que você gasta com esse problema.

Sugestões:

1. Use o monitor de desempenho para descobrir onde seus gargalos realmente são. Números duros, não palpites. tem muito bom MSDN e outro artigo em quais contadores monitorar. Geralmente é um disco ou CPU gargalo. Se você está vendo alto Comprimento da fila de disco, você geralmente precisa de mais memória, disco não mais rápido! Compre tanto quanto RAM como você pode pagar. Isso significa você precisa de um sistema operacional de 64 bits e 64 bits versão do servidor SQL. Você pode essencialmente manter todo o banco de dados na memória RAM no servidor de 64 GB. Seu contador de desempenho da taxa de acerto do cache de buffer deve ficar acima de 99% durante as cargas de pico.
2. Adicione apenas CPU quando tiver certeza de que indexou o banco de dados corretamente para suas consultas mais frequentes. Um corretamente consulta projetada e inexed pode salvar um fator de 1000 (não um erro de digitação) em tempo de execução sobre um mal escrito 1. Evite vistas de aninhamento, que desenvolvedores adoram por causa do código reutilize, mas sugue para o desempenho porque os índices geralmente se tornam sem utilidade. Qualquer desenvolvedor que use os cursores afirmando que eles são mais rápidos ou "a única maneira de fazer isso" deve ser acionado imediatamente.
3. Considere os SSDs em vez do disco mecânico se o seu conjunto de trabalho for maior que a maior memória disponível no servidor de dois soquetes. Isso é atualmente 192 GB, eu acho, então você não tem tamanhos de dados tão próximos ainda.
4. Essencialmente, faça isso .

Answer 4

Você está cansado da HP ou Del l ferramentas de dimensionamento? não o seja tudo & terminar tudo, mas pelo menos um bom ponto de partida.