none
Configuração/desempenho de Servidor para SQL Server 2005 RRS feed

  • Pergunta

  •  

    Pessoal, ontem aqui onde trabalho formatamos o servidor, instalamos o SQL Server 2005(com todos os serviços, porém já desabilitei os desnecessários) e migramos nossa base Sql 2000 e deixamos todos os Sistemas funcionando perfeitamente.

     

    Hoje os usuários dos Sistemas reclamaram de lentidão nas consultas e etc, quando fui olhar o desenpenho do servidor percebi que o uso da CPU está em 100% a todo o momento, o que achei estranho pois temos um servidor HP muito bom, o qual descrevo abaixo:

     

  • 1 HP DL 380 Rack 19” 2U - 2 Processadores Zion - 4 GB de RAM ECC - 6 HD SCSII 15K 142 GB - Hot Plut - 2 Fontes Redundantes.

     

    Pois bem, não sei dizer ao certo qual configuração de Raid foi feita na formatação do mesmo, creio que tenha sido Raid0, pois o servidor agora encontra-se apenas com um partição a qual totaliza todos os GBs dos HDs.

     

    Bem, o queria era saber era qual configuração de RAID e numeros de partições devo fazer para ganhar o máximo de performace desde servidor, o mesmo qual configuração do SQL server 2005 faço.

     

     

    Abraços

     

     

segunda-feira, 11 de fevereiro de 2008 14:33

Todas as Respostas

  •  

    Olá Cleilson!!

     

    Primeiramente, uma situação que geralmentese enfrenta em migrações do SQL 2000 para 2005 é relacionada aos índices e estatísticas (principalmente estatísticas). Sempre que se faz essa migração, é altamente recomendável que se atualize a estatística de todas tabelas e índices, assim o SQL irá poder obter o máximo de performance da forma como a base está criada. Tive um caso onde uma consulta levava em torno de 30 segundos para ser executada no 2000, e logo após a migração, ela passou a levar mais de 1 minuto (isso sem atualizar as estatísticas). Após a atualização das estatísticas, ela passou a levar em torno de 15 segundos sem fazer qualquer modificação.

     

    Sobre a questão dos discos, se ele está mostrando a partição com o tamanho de todos os discos, certamente foi definido como RAID 0 mesmo, neste caso, recomendaria que fosse implementada uma rotina de backups onde fossem salvadas as informações a cada pouco tempo, por o RAID 0 não tem qualquer redundância, somente aproveitamento de espaço.

     

    Se seu objetivo principal é desempenho, crio que o RAID 1 vá lhe ajudar, pois o mesmo consegue fazer a leitura de informações de mais de um disco paralamente. O RAID 5 só passa a ser interessante para desempenho quando temos mais de 10 discos no RAID.

     

    Mas, recomendo rever inicialmente essa configuração de disco, primeiramente pensando em segurança, depois desempenho. Resumindo

    - RAID 0: não tem segurança

    - RAID 1: tem segurança (redundância), bom desempenho (principalmente para leitura) mas o custo não é muito bom, já que apenas a metade do tamanho total estará disponível (Ex.: de 100GB somente 50 poderão ser usados).

    - RAID 5: tem segurança, bom desempenho (principalmente com mais de 10 discos) e bom custo/benefício, somente o espaço equivalente a 1 disco será "perdido".

     

    Espero ter ajudado.

     

     

    Abraço!!

    segunda-feira, 11 de fevereiro de 2008 15:10
  • Ajudou sim, obrigado pelo informação, o RAID configurado no servidor foi RAID 5 para todos os 6 HD, só me diz como atualizo as estatísticas e indices, que farei agora.

     

    E me diz se preciso atualizar preciso reniciar?

    segunda-feira, 11 de fevereiro de 2008 15:16
  • Como atualizar as estatísticas já no servidor 2005, pois já está em funcionamento.

    segunda-feira, 11 de fevereiro de 2008 15:21
  •  

    A forma mais prática de atualizar todas estatísticas é usar a seguinte instrução:

     

    Code Snippet

    sp_MSForEachTable 'UPDATE STATICSTICS ? WITH FULLSCAN'

     

     

    Basta se conectar na database que foi migrada e rodar a essa instrução. Eu recomendo que faça isso em horário fora de expediente, pois ele consome muito processamento e leitura de disco.

     

    E, não precisa reiniciar, o processo é completamente online.

     

     

    Abraço!!!

     

    segunda-feira, 11 de fevereiro de 2008 15:24
  • Essa instrução já atualiza também os indices?

    segunda-feira, 11 de fevereiro de 2008 15:28
  •  

    Ela atualiza as estísticas de todos índices de todas tabelas.

     

    Se mesmo assim não resolver, poderá fazer um reindex, usando essa instrução:

     

    Code Snippet

    sp_MSForEachTable 'DBCC DBREINDEX (''?'', '''', 0)'

     

     

     

    Se executar essa instrução, recomendo executar em seguida a instrução anterior que passei para atualizar as estatísticas.

     

     

     

    Abraço!!!

    segunda-feira, 11 de fevereiro de 2008 15:35
  • Cleison,

     

    Sim ela já realização o processo completo de atualização.

    segunda-feira, 11 de fevereiro de 2008 15:40
    Moderador
  •  Alexandre VM wrote:

     

    Ela atualiza as estísticas de todos índices de todas tabelas.

     

    Se mesmo assim não resolver, poderá fazer um reindex, usando essa instrução:

     

    Code Snippet

    sp_MSForEachTable 'DBCC DBREINDEX (''?'', '''', 0)'

     

     

    Alexandre, como ele está utilizando o SQL Server 2005, não seria melhor ele usar as novas instruções ALTER INDEX pra reindexar? Ou mesmo criar um plano de manutenção dos índices?

     

    Se executar essa instrução, recomendo executar em seguida a instrução anterior que passei para atualizar as estatísticas.

     

     

     

    Abraço!!!

    segunda-feira, 11 de fevereiro de 2008 17:21
  • Cleilson, não podemos esquecer por se tratar de um servidor novo que acabou de receber o banco nas primeiras horas ele tende a ficar com um consumo de CPU acima do normal, pois ele precisa refazer e colocar no cache todas as queries. O processo de compilação é altamente custoso pra CPU.

     

    Se você quiser monitorar como vai o andamente disso, veja no Perfmon o contador: SQLServerTongue TiedQL Statistics\Compilation/sec

     

    Qualquer dúvida retorne.

     

    Abraços,

    segunda-feira, 11 de fevereiro de 2008 17:25
  •  

    Rodrigo,

     

    Como é apenas uma instrução executada para fazer a recriação uma única vez após a migração, até não faz muita diferença, o resultado vai ser o mesmo. Mas, se a idéia é manter a database com bom desempenho, realmente, é interessante usar os planos de manutenção.

     

    Com relação a instrução em si, se formos usar ela em alguma solução continuará sendo utilizada futuramente, é interessante trocarmos pelo ALTER INDEX, pois o DBREINDEX será descontinuado nas versões futuras.

     

    Com o ALTER INDEX ficaria assim:

     

    Code Snippet

    sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'

     

     

     

     

    Abraço!!!

    segunda-feira, 11 de fevereiro de 2008 17:35
  • Pessoal, ao executar as instruções:
    • sp_MSForEachTable 'DBCC DBREINDEX (''?'', '''', 0)'
    • sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'
    Da o sequinte erro: Msg 8606, Level 17, State 1, Line 1
    This index operation requires 2048 KB of memory per DOP. The total requirement of 8192 KB for DOP of 4 is greater than the sp_configure value of 2048 KB set for the advanced server configuration option "index create memory (KB)".  Increase this setting or reduce DOP and rerun the query.

    E ao executar a instrução:

    • sp_MSForEachTable 'UPDATE STATICSTICS ? WITH FULLSCAN'
    Aparece os erro:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'dbo'.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'dbo'.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'dbo'.
    o erro vai aparecendo até:
    The query has exceeded the maximum number of error messages. Only  the first 1000 messages will be displayed.

    O que faço?
    segunda-feira, 11 de fevereiro de 2008 17:51
  • Pessoa, outra coisa, estou em dúvida em saber qual versão do Sql Server estou usando, tem algum comando para isso?
    segunda-feira, 11 de fevereiro de 2008 17:58
  • Code Snippet
    SELECT @@VERSION

     

     

     

    segunda-feira, 11 de fevereiro de 2008 18:05
  • Cleilson, por favor poste aqui a versão do SQL Server.

     

    Em seguida poste também a saída do comando:

     

    Code Snippet

    EXEC sp_configure

     

     

    Estou estranhando o 'Index Creation Memory', estar configurado no seu ambiente.

     

    Aguardo retorno.

     

    Abraços,

     

     

    segunda-feira, 11 de fevereiro de 2008 18:10
  •  

    Rodrigo, tbm estou achando estranho estar configurada essa opção. Não é exatamente comum alterar esse parâmetro.

     

     

     

    Abraço

    segunda-feira, 11 de fevereiro de 2008 18:16
  • SELECT @@VERSION

     

    Retornou:

    Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86)   Mar 23 2007 16:28:52   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

     

     

    E EXEC sp_configure

     

    Retornou (veja na imagem ou link abaixo):

     



    http://www.pauloafonso.ba.gov.br/download/cleilson/Sp_configure.jpg

     

     

    Estranhei a quantidade de configurações existentes, pois tinha lido em um artigo já sobre essa procedure e lá relatava vários comandos que não existem no meu servidor.

     

     

    segunda-feira, 11 de fevereiro de 2008 19:16
  • O parâmetro Index Creation Memory eu que tinha configurado, pois estava procurando em vários artigos ajuda, e vi algo sobre ele, mas já voltei para 0, que era o padrão.

     

    Irei tentar executar as intruções novamente.

    segunda-feira, 11 de fevereiro de 2008 19:21
  •  

    Diz quais configurações devo fazer utilizando a procedure SP_CONFIGURE afim de melhorar o desempenho, baseado no link que passei. (http://www.pauloafonso.ba.gov.br/download/cleilson/Sp_configure.jpg)
    segunda-feira, 11 de fevereiro de 2008 19:23
  •  

    Cleilson,

     

    Sobre a quantidade de parâmetros, basta rodar o seguinte:

     

    Code Snippet

    EXEC sp_configure 'show advanced options', 1

    GO

    RECONFIGURE

    GO

     

     

    Assim, serão mostrados os parâmetros que por padrão ficam ocultos.

     

    Bom, no local onde estou neste momento, não tenho como acessar a imagem que você postou, então não terei como analisar seu problema agora.

     

     

    Abraço!!

     

     

     

    segunda-feira, 11 de fevereiro de 2008 19:23
  •  

    Tentou executar os comandos depois de voltar o parâmetro para zero??\

     

     

     

    Abraço!!!

    segunda-feira, 11 de fevereiro de 2008 19:26
  • Executei a instrução
     

    Code Snippet

    EXEC sp_configure 'show advanced options', 1

    GO

    RECONFIGURE

    GO

     

     

     

    novamente e estou postando abaixo o retorno em TEXTO (vermelho)

     

    Code Snippet

    name                                minimum     maximum     config_value run_value

    ----------------------------------- ----------- ----------- ------------ -----------

    Ad Hoc Distributed Queries          0           1           0            0

    affinity I/O mask                   -2147483648 2147483647  0            0

    affinity mask                       -2147483648 2147483647  0            0

    Agent XPs                           0           1           1            1

    allow updates                       0           1           0            0

    awe enabled                         0           1           0            0

    blocked process threshold           0           86400       0            0

    c2 audit mode                       0           1           0            0

    clr enabled                         0           1           0            0

    common criteria compliance enabled  0           1           0            0

    cost threshold for parallelism      0           32767       5            5

    cross db ownership chaining         0           1           0            0

    cursor threshold                    -1          2147483647  -1           -1

    Database Mail XPs                   0           1           0            0

    default full-text language          0           2147483647  1033         1033

    default language                    0           9999        0            0

    default trace enabled               0           1           1            1

    disallow results from triggers      0           1           0            0

    fill factor (%)                     0           100         0            0

    ft crawl bandwidth (max)            0           32767       100          100

    ft crawl bandwidth (min)            0           32767       0            0

    ft notify bandwidth (max)           0           32767       100          100

    ft notify bandwidth (min)           0           32767       0            0

    index create memory (KB)            704         2147483647  0            0

    in-doubt xact resolution            0           2           0            0

    lightweight pooling                 0           1           0            0

    locks                               5000        2147483647  0            0

    max degree of parallelism           0           64          0            0

    max full-text crawl range           0           256         4            4

    max server memory (MB)              16          2147483647  2147483647   2147483647

    max text repl size (B)              0           2147483647  65536        65536

    max worker threads                  128         32767       0            0

    media retention                     0           365         0            0

    min memory per query (KB)           512         2147483647  3072         3072

    min server memory (MB)              0           2147483647  0            0

    nested triggers                     0           1           1            1

    network packet size (B)             512         32767       6000         6000

    Ole Automation Procedures           0           1           0            0

    open objects                        0           2147483647  0            0

    PH timeout (s)                      1           3600        60           60

    precompute rank                     0           1           0            0

    priority boost                      0           1           0            0

    query governor cost limit           0           2147483647  0            0

    query wait (s)                      -1          2147483647  -1           -1

    recovery interval (min)             0           32767       0            0

    remote access                       0           1           1            1

    remote admin connections            0           1           0            0

    remote login timeout (s)            0           2147483647  20           20

    remote proc trans                   0           1           0            0

    remote query timeout (s)            0           2147483647  600          600

    Replication XPs                     0           1           0            0

    scan for startup procs              0           1           0            0

    server trigger recursion            0           1           1            1

    set working set size                0           1           0            0

    show advanced options               0           1           1            1

    SMO and DMO XPs                     0           1           1            1

    SQL Mail XPs                        0           1           0            0

    transform noise words               0           1           0            0

    two digit year cutoff               1753        9999        2049         2049

    user connections                    0           32767       0            0

    user options                        0           32767       0            0

    Web Assistant Procedures            0           1           0            0

    xp_cmdshell                         0           1           0            0

     

     

     

    Abraços
    segunda-feira, 11 de fevereiro de 2008 19:29
  • Executei o comando

     

    Code Snippet

    sp_MSForEachTable 'ALTER INDEX ALL ON ? REBUILD'

     

     

    E estou esperando acabar, até agora ta indo, deve demorar, pois meu banco tem em média 4 GB.

     

     

    segunda-feira, 11 de fevereiro de 2008 19:31
  •  

    Blz. Se puder, depois deste, execute a atualização de estatísticas. Então, por final faça os testes com os usuários.

     

     

     

    Abraço!!!

    segunda-feira, 11 de fevereiro de 2008 19:36
  •  

    Tem como me me dizer se as configurações que te passei da SP_CONFIGURE estão ok?
    segunda-feira, 11 de fevereiro de 2008 19:48
  •  

    Nas configurações que estão ativas, não vejo problema algum. Tudo ok.

     

     

    Quando terminar de rodar aí, avise se funcionou.

     

     

    Abraço!!!

    segunda-feira, 11 de fevereiro de 2008 19:56
  • Alexandre, cancelei a execução do Script pois alguns usuários estavam reclamando de lentidão, ai deixarei para fazer isso pela noite. De qualquer forma obrigado.

    segunda-feira, 11 de fevereiro de 2008 20:01
  •  

    Cleilson,

     

    É, eu até estranhei que você estava rodando durante horário de expediente. Até tinha lhe avisado a respeito desse problema. Amanhã retorne com o resultado.

     

     

    Abraço!!

    segunda-feira, 11 de fevereiro de 2008 22:26
  • Ao executar
    • sp_MSForEachTable 'UPDATE STATICSTICS ? WITH FULLSCAN'
    Aparece os erro:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'dbo'.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'dbo'.
    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near 'dbo'.
    o erro vai aparecendo até:
    The query has exceeded the maximum number of error messages. Only  the first 1000 messages will be displayed.

    Mudei o comando para

    sp_MSForEachTable 'UPDATE STATISTICS ? WITH FULLSCAN'

    Ai apareceu outro erro

    Incorrect syntax near the keyword 'FULL'.


    O QUE FAÇO???
    terça-feira, 12 de fevereiro de 2008 05:58
  • Mudei o script para

    • EXEC sp_MSForeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

    E o mesmo esta executando, vamos ver no que vai dar, agora vou dormir Big Smile


    Abraços,
    terça-feira, 12 de fevereiro de 2008 06:04

  • Pessoa, executei todas as atualizações porém não percebi muitos resultados, pensei que instalando o SQL Server 2005 teria ganhos de performace, mas pelo que vi não.


    terça-feira, 12 de fevereiro de 2008 12:34
  • Cleilson,

     

    O normal é ter ganho de desempenho, agora, quanto vai haver de ganho vai depender da forma e da qualidade do código desenvolvido para acessar o banco de dados, inclusive as consultas.

     

    Eu recomendo que você colete as consultas mais pensadas e analise estas para tentar melhorar elas, sendo nos índices ou na própria escrita da consulta.

     

     

     

    Abraço!

    terça-feira, 12 de fevereiro de 2008 13:53
  • Cleilson,

     

    Concordo com o Alexandre!!!

     

    Muitas vezes o problema de lentidão esta relacionado com a forma que os dados estão sendo acessados e também como estas informações estão sendo distribuidas na rede para os demais pontos de acesso.

     

    Tenho como costume criar uma script's para realizar testes de performance, sendo estes, bem simples fazendo acesso a grande bases de dados, e depois vou refinando estes scripts até deixar de uma forma mais otimizada.

     

    Gosto também de trabalhar com Database Tunning Advisor, é uma ótima ferramenta para análise e otimização de índices.

    terça-feira, 12 de fevereiro de 2008 15:33
    Moderador