none
Gerenciamento de Memória SQL Server Hyper-v RRS feed

  • Pergunta

  • Pessoal, gostaria de uma troca de conhecimentos sobre a gestão da memória adequada para uma instância SQL Server 2008.

     

    Tenho um servidor físico que tem 24GB de memória RAM, no qual rodava uma instância de produção do SQL Server 2008 com apenas um banco de dados de aproximadamente 50GB. 

    Recentemente decidimos utilizar esse servidor como apoio para subirmos o Hyper-v e colocarmos VMs no mesmo, inclusive a instância do SQL Server. 

    Após subirmos a instância como VM no Hyper-v com 16GB de memória, definir no max memory server 12GB e mim memory server 1GB pra SQL Server. Posterior ao processo a equipe de infra do Windows Server questionou que o servidor estava trabalhando com mais de 80% de sua capacidade, sendo que o serviço do SQL Server estava consumindo demais. Porém eles normalmente buscam essas informações do Task Manager, no qual não costumo usar como parâmetros pelo fato de mostrar as vezes resultados não tão verdadeiros. Me corrijam se estiver errado.


    Gostaria de saber a dicas de vocês da melhor maneira de acompanhar essa gestão da memória, no qual eu possa garantir que não estou tendo gargalo de memória. 

    Sei que temos os contadores de desempenho abaixo, mas a melhor maneira de analisarmos, talvez usando gráficos:


    Processo: Conjunto de trabalho

    SQL Server: Gerenciador de Buffer: Taxa de acertos do cache do buffer

    SQL Server: Gerenciador de Buffer: Páginas de Banco de Dados

    SQL Server: Gerenciador de Memória: Memória total do servidor (KB)

    Memória: Bytes disponíveis

    Memória: Páginas/segundo


    A equipe de rede nos ofertou adicionar mais memória, porém preciso ter uma visão mais clara das necessidades, pois a nível de app nenhuma reclamação de usuários quanto ao uso diário do sistema.


    Lembrando que coloquei os contadores de desempenho citado acima pra rodar no servidor desde de ontem. Ao completar 24 horas irei analisar os resultados e entender se realmente os 12GB são o suficientes pra o SQL Server e provar que os resultados retornados pelo Task não são verdadeiros. 


    Agradeço desde já!





    sexta-feira, 13 de março de 2015 13:00

Respostas

  • Jerferson,

    Você configurou o Hyper-V no mesmo servidor que o SQL Server esta rodando fisicamente?

    Em os scripts apresentados abaixo, você poderá obter mais informações sobre a memória do SQL Server:

    -- Utilização por tipo de cache
    SELECT  type, 
            SUM(single_pages_kb)/1024. AS [SPA Mem, MB],
    		SUM(Multi_pages_kb)/1024. AS [MPA Mem,MB]
    FROM sys.dm_os_memory_clerks
    GROUP BY type
    HAVING  SUM(single_pages_kb) + sum(Multi_pages_kb)  > 40000 -- Só os maiores consumidores de memória
    ORDER BY SUM(single_pages_kb) DESC
    
    -- Total utilizado
    SELECT  SUM(single_pages_kb)/1024. AS [SPA Mem, KB],
            SUM(Multi_pages_kb)/1024. AS [MPA Mem, KB]
    FROM sys.dm_os_memory_clerks


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    sexta-feira, 13 de março de 2015 17:38
    Moderador
  • Jerfeson,

    O cache_store_sqlcp é um dos components dos chamados contadores de memória existentes no SQL Server que o plano de execução quando chamado pelo Query Processor faz uso.

    Por padrão todo armazenamento do plano de execução de uma query é feito em mémória, e o cache_store_sqlcp é um dos elementos envolvidos neste cenário.

    Normalmente ele armazena em cache as instruções SQL ou lotes que não estejam em Stored Procedures, Functions ou Triggers, também incluíndo qualquer SQL dinâmico ou um simples SELECT enviadas ao servidor. Se você fizer a limpeza deste contador, provavelmente os valores utilizados pelo SQL Server no momento da execução de uma transação serão impactados pois o Plano de execução terá que ser realocado novamente e os contadores também atualizados.

    A query abaixo poderá ajudar a entender com a contagem destes operadores é armazenada para uso do Plan Cache:

    SELECT objtype AS [CacheType]
            , count_big(*) AS [Total Plans]
            , sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]
            , avg(usecounts) AS [Avg Use Count]
            , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]
            , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
    FROM sys.dm_exec_cached_plans
    GROUP BY objtype
    ORDER BY [Total MBs - USE Count 1] DESC


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 25 de março de 2015 15:54
    Moderador

Todas as Respostas

  • Jerferson,

    Você configurou o Hyper-V no mesmo servidor que o SQL Server esta rodando fisicamente?

    Em os scripts apresentados abaixo, você poderá obter mais informações sobre a memória do SQL Server:

    -- Utilização por tipo de cache
    SELECT  type, 
            SUM(single_pages_kb)/1024. AS [SPA Mem, MB],
    		SUM(Multi_pages_kb)/1024. AS [MPA Mem,MB]
    FROM sys.dm_os_memory_clerks
    GROUP BY type
    HAVING  SUM(single_pages_kb) + sum(Multi_pages_kb)  > 40000 -- Só os maiores consumidores de memória
    ORDER BY SUM(single_pages_kb) DESC
    
    -- Total utilizado
    SELECT  SUM(single_pages_kb)/1024. AS [SPA Mem, KB],
            SUM(Multi_pages_kb)/1024. AS [MPA Mem, KB]
    FROM sys.dm_os_memory_clerks


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    sexta-feira, 13 de março de 2015 17:38
    Moderador
  • Você configurou o Hyper-V no mesmo servidor que o SQL Server esta rodando fisicamente? Não.

    Foi criado uma VM pra SQL Server no Hyper-V pelo time de infra. A máquina física está com instalação limpa rodando apenas o Hyper-V.

    Hoje iniciei um monitoramento dos principais contadores de desempenho de memória, no qual pretendo deixar rodando até sexta. Acho que desta forma terei um parâmetro melhor do comportamento da memória. Rodei um monitoramento de 24 horas na semana passada e não conseguir identificar problemas na configuração do max server memory, mas é preciso justificar junto ao time de infra a necessidade de aumentar ou não memória pro servidor.

    Veja os resultados que encontrei no monitoramento Galvão e me corrija se estiver errado nas conclusões que tirei. Não existe em evidência problema de memória configurada pra instância SQL Server de acordo com os resultados abaixo.


    Analisando o Bytes e MBytes disponíveis as médias correspondem um valor positivo e aceitável. Considerei a seguinte afirmação "Um valor menor do que 5% do total de ram física significa que a memória é insuficiente, e isso pode aumentar a atividade de paginação". Então pra 16GB configurado pra VM e 5% seria 0,8 MB abaixo das médias.

     

    Pra o contador de páginas/s considerando a afirmação "Se o valor for maior do que 1.000, como resultado da paginação excessiva, pode haver um vazamento de memória.". Então considerando o resultado do contador a média de paginação é aceitável.

    O buffer cache hit ratio sigo o mesmo parâmetro de análise com relação a afirmação seguinte "Taxa de 90 por cento ou mais é desejável", o resultado do contador consiste na média aceitável para funcionamento ótimo da instância.

     

    Total server memory considerando a afirmação "Consistentemente alto, em comparação com a quantidade de memória física disponível no computador.". Considerei aceitável, pois a memória ram é de 16GB e o resultado marca 12GB, justamente o configurado no max server memory e não ultrapassa a memória física. 

     

    No conjunto de trabalho do serviço do SQL Server considerei a afirmação "Se esse número estiver consistentemente abaixo da quantidade de memória definida pelas opções de servidor min server memory e max server memory, o SQL Server está configurado para usar memória demais.". Neste caso o resultado apresentou o valor após.

     

    Abaixo segue resultados das querys.

    segunda-feira, 16 de março de 2015 15:20
  • Olá Jeferson!

    Você está certo em divesos pontos da sua análise mas é importante considerar alguns ajustes:

    1- É comum o servidor SQL Server manter alocado na memória o "Max Server Memory". Isto acontece porque ele só procurará devolver esta memória ao SO caso haja pressão externa (outro app requisitando memória). Quando ele sofre pressão externa entra em ação o Lazy Write, mecanismo que retira do cache as páginas menos utilizadas para devolver este espaço ao SO ou para o próprio SQL Server reutilizar. Você pode acompanhar quando isso ocorre através do contador "Lazy Writes/sec".

    2- Sobre os valores utilizados nos seus critérios, precisamos tomar cuidado na análise e na recomendação que podemos te fazer. Cache Hit Ratio você vai querer ver 99,998 ou perto disso. Esta informação sozinha diz muito pouco e o ideal é você sempre coletar isso, de 1 em 1 minuto por exemplo, pra saber quando está fora do normal. Se você combinar com o Lazy Writes/sec aí você vai perceber que quando o LW rodou o Cache Hit Ratio diminui um pouco (bem pouco). Apenas estas duas informações também não traz grandes evidências, então você combina com o Page Life Expectancy, que deve cair significativamente durante pressão interna ou externa.

    3- Até o SQL Server 2008, é comum termos um pouco mais de consumo de RAM, além do Max Server Memory, pois este só computa o tamanho do data cache. Os demais caches (que são bem menores) ficam de fora desta conta, fazendo com que o consumo de memória seja um pouco maior.

    4- Um outro contador muito importante é o Page File Usage, pra saber quanto de RAM foi necessário paginar. Pra você ter um entendimento melhor sobre isso você pode pesquisar por VAS (Virtual Address Space).

    5- O seu Target e Total Server Memory estão iguais, Page Life Expectancy está alto (em relação aos ambientes que conheço), ainda tem 1,5GB de RAM disponível e imagino que o seu arquivo de paginação tenha poucos % utilizado.

    Por favor, dê uma olhada no descritivo dos contadores neste artigo:
    http://sqldicas.com.br/dicas/perfmon-e-sql-server/

    Abraço!


    Luiz Mercante | MCITP SQL 2008 | MCTS SQL 2008 | MTA Database Fundamentals | MCTS Windows Apps | MCTS Windows Network | MCP 2003 | sqldicas@outlook.com | http://sqldicas.com.br --> Se a resposta foi útil de alguma forma, classifique como resposta ou vote como útil.

    terça-feira, 17 de março de 2015 20:27
    Moderador
  • Olá Luiz e Galvão, fiz um monitoramento essa semana (segunda à sábado) usando como parâmetro o tempo 5 em 5 minutos. Peguei informações de contadores mais abrangentes, foi possível ter uma visão melhor do comportamento da VM com SQL Server 2008.

    Interpretando a tabela 1 abaixo vejo que os resultados de alguns objetos dos contadores se mantiveram como o esperado não entendo como um possível problema de memória. 

    OBSERVAÇÃO: ressalto que os usuários não estão reclamando de lentidão no app, nem o gestor do TI. Ao contrário, me informaram que o sistema está voando. Só preciso ter esse feedback dos contadores, pois o time de infra questionou que o SO está sofrendo pressão do SQL Serber, no qual segundo eles perceberam o processo do SQL Server está consumindo mais de 80% da memória. Sendo que a MS sugere pra eles esse valor ser menor/igual 70%. Sinceramente não entendi o que querem já que não consigo observar problemas em meu servidor. Estão se baseando nos resultados vistos no gerenciador de tarefas do windows na imagem anexada acima. VM com 16GB, configurado 75% pra SQL Server, ou seja, 12GB max server memory deixando livre pra SO 4GB, sendo que essa VM é dedicada somente ao SQL Server. Um único banco de dados de 56GB com média de 130 conexões simultâneas em horário de pico.  


    1 - De acordo com a recomendação os objetos do contador de Page File devem trabalhar com valores abaixo de 70%. Observando a tabela o utilizado (%Usage) e o consumido (%Usade Peak) estão bem abaixo, tendo respectivamentes valores de 10% e 36%. Valor de curva de normalidade aceitável. Isso justificava o valor do contador Pages/s ser bem pequeno tendo um valor bem razoável não visualizando excesso de paginação.

    2 - Pra os objetos do contador de memória, todos estão seguindo o padrão recomendado. O objeto % bytes confirmados em uso está consumindo apenas 46%, sendo que o recomendado ser menor à 80%. Bytes de pool paginável e não paginável observando os gráficos vi que tem seus valores constantes durante o período que foi monitorado. O objeto entradas livres de tabela de paginação do sistema também permaneceu em seu limite aceitável,  

    3 - No buffer manager o objeto checkpoint pages/sec está com valor aceitável considerando o recomendado abaixo de 50%.O lazy writes/sec da mesma forma, no monitoramento apresentou 13%, sendo que o recomendado é abaixo de 20%. As expectativas de vida na memória das páginas está também dentro dos conformes, além dos demais.

    4 - O que me preocupou um pouco no contador memory manager foram os resultados do target server memory e total server memory, pois fazendo uma leitura um pouco mais aprofundada vi que o valor total de memória configurada no max server memory deve ficar menor que o resultado do target server memory. No meu caso o resultado indicaria uma necessidade de incremento de memória no servidor e consequentemente um aumento no max server memory.

    Novamente utilizei a querie sugerida por Galvão, no qual gerei novas resultados de uso de memória de outras caches. Não considerei um problema, pois pra os 12GB configurados outras caches estava utilizando aproximadamente cerca de 3,2GB.

    Uma dúvida: a cachestore_sqlcp que é uma cache de Ad-hoc queries e que em seu conceito diz não é muito reutilizado pelo SQL server, valeria a pena limpar para liberar mais memória pra o buffer pool e aumentaria a taxa do buffer cache hit ratio?  Entendo que posso sobrecarregar meus CPU's. Ou aumentaria de fato a memória já que a equipe de infra tem isso como sugestão e com isso alocaria mais pra max server memory?

    Obrigado Luiz pela indicação do link, inclusive já tinha lido o mesmo na pesquisa.

    Me corrijam se estiver errado em algum ponto da pesquisa. Fico no aguardo!
    Agradeço desde já o help. 




    • Editado Jerfeson S. Barbosa domingo, 22 de março de 2015 17:50 Adição de palavra no texto.
    domingo, 22 de março de 2015 17:06
  • Jerfeson,

    O cache_store_sqlcp é um dos components dos chamados contadores de memória existentes no SQL Server que o plano de execução quando chamado pelo Query Processor faz uso.

    Por padrão todo armazenamento do plano de execução de uma query é feito em mémória, e o cache_store_sqlcp é um dos elementos envolvidos neste cenário.

    Normalmente ele armazena em cache as instruções SQL ou lotes que não estejam em Stored Procedures, Functions ou Triggers, também incluíndo qualquer SQL dinâmico ou um simples SELECT enviadas ao servidor. Se você fizer a limpeza deste contador, provavelmente os valores utilizados pelo SQL Server no momento da execução de uma transação serão impactados pois o Plano de execução terá que ser realocado novamente e os contadores também atualizados.

    A query abaixo poderá ajudar a entender com a contagem destes operadores é armazenada para uso do Plan Cache:

    SELECT objtype AS [CacheType]
            , count_big(*) AS [Total Plans]
            , sum(cast(size_in_bytes as decimal(12,2)))/1024/1024 AS [Total MBs]
            , avg(usecounts) AS [Avg Use Count]
            , sum(cast((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(12,2)))/1024/1024 AS [Total MBs - USE Count 1]
            , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]
    FROM sys.dm_exec_cached_plans
    GROUP BY objtype
    ORDER BY [Total MBs - USE Count 1] DESC


    Pedro Antonio Galvao Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitario | SoroCodigos | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 25 de março de 2015 15:54
    Moderador