none
Verificar o consumo real memória do SQL Server RRS feed

  • Pergunta

  • Bom dia,

    Estou a dias procurando um forma para descobrir o real consumo de memória do SQL Server. Tenho uma instância SQL Server em um servidor com 16GB de memória, o "maximum server memory" está configurado com 2147483647MB e o SQL Server pegou para ele 12GB. Eu gostaria de saber se tem como eu saber a quantidade de memória que o SQL realmente está usando para que um possa fazer o dimensionamento de memória que o SQL realmente precisa.

    Desde já agradeço.

    quarta-feira, 20 de janeiro de 2016 13:52

Respostas

  • Thmrs

    Tente esse código:

    with cteSizeDB as ( 
       SELECT 'DB:'+DB_NAME(database_id) AS cDBName , 
              cast(COUNT(*) * 8 / 1024.0 as decimal(7,2)) as nSizeInMemoryMB 
         FROM sys.dm_os_buffer_descriptors 
        GROUP BY DB_NAME(database_id) 
        union all 
        select 'Mem:'+type , sum(pages_kb)/1024  
          from sys.dm_os_memory_clerks 
      where type <> 'MEMORYCLERK_SQLBUFFERPOOL' 
              group by type 
    )select * 
       from cteSizeDB 
       where nSizeInMemoryMB > 0 
      union 
     select 'Todos' , SUM(nSizeInMemoryMB) 
       from cteSizeDB 
       order by 2 desc;  
       
    

    Ele mostra o quando a memória é utilizada pelos bancos de dados e por outros cache como os de objetos de programação e query ad-hoc. 

    Veja esse link também.

    http://www.fabriciolima.net/blog/2010/12/25/casos-do-dia-a-dia-diminuindo-um-problema-de-memoria-no-sql-server/

    Boa sorte !!!



    Wolney

    quarta-feira, 20 de janeiro de 2016 19:05

Todas as Respostas

  • Thmrs,

    Com esta configuração padrão de "2147483647MB" o SQL Server poderá utilizar toda a memória disponível no sistema operacional.

    Você pode consultar o quanto de memória o SQL Server está usando através da consulta abaixo utilizando a DMV dm_os_process_memory:

    SELECT
    (physical_memory_in_use_kb/1024) AS Memory_usedby_Sqlserver_MB,
    (locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,
    (total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
    process_physical_memory_low,
    process_virtual_memory_low
    FROM sys.dm_os_process_memory;

    Referência para leitura:

    https://msdn.microsoft.com/en-us/library/ms176018.aspx?f=255&MSPPError=-2147217396


    Felipe Lauffer MCSA: SQL Server | MCP


    • Editado FLauffer quarta-feira, 20 de janeiro de 2016 14:08
    quarta-feira, 20 de janeiro de 2016 14:08
  • Olá Thmr, Você precisa executar Perfmon

    (iniciar-> Painel de controle> administrativa tools-> desempenho).

    Os contadores que você deseja, creio eu, estão em SQL Server: Gerenciador de memória. Servidor de destino da memória é o quanto ele está autorizado a tomar, se necessário, Memória Total Server é o quanto ele está usando atualmente. Se você clicar em Explicar que vai abrir pop uma caixa com os detalhes.

    Caso queira mais algumas informações sobre a ferramenta veja esses links:

    http://www.brentozar.com/archive/2006/12/dba-101-using-perfmon-for-sql-performance-tuning/

    ou

    http://sqldicas.com.br/dicas/perfmon-e-sql-server/


    If the answer was helpful vote / mark as answered

    Se a resposta foi útil vote / marque como respondido


    • Editado Jean LLopes quarta-feira, 20 de janeiro de 2016 14:12
    • Sugerido como Resposta Jean LLopes quarta-feira, 20 de janeiro de 2016 14:12
    quarta-feira, 20 de janeiro de 2016 14:10
  • Bom dia,

    Esse é o valor default do SQL Server, quer dizer que o SQL Server pode utilizar toda a memória que ele quiser.

    Como vc olhou no Task Manager sua instancia está alocando 12 GB, se precisar de mais ele vai consumir mais.

    É importante que a memoria alocada no SQL Server ela não é devolvida para sistema operacional automaticamente.

    Exemplo:

    Voce alocou 15 GB, o SQL esta consumindo os 15GB, vc precisa alterar o parâmetro de memoria, para desalocar a memoria.


    Se a resposta foi útil por favor classifique. Tiago Neves - @tiagolneves - acesse o meu blog http://www.tiagoneves.net

    quarta-feira, 20 de janeiro de 2016 14:11
  • Felipe Lauffer,

    Ao executar sua consulta aparece os mesmos 12GB que no Task manager, só que quando eu olho no banco há pouquíssimas operações sendo executadas. Sendo assim o SQL está utilizando a memória para o quê? Ele apenas reservou e não está utilizando? E se outra aplicação de necessitar de memória o SQL vai liberar memória para a aplicação utilizar?


    quarta-feira, 20 de janeiro de 2016 18:32
  • Tiago_Neves,

    Fiquei confusa o minimun server memory não é a memória realmente reservada para o SQL Server  e o maximum server memory não é o máximo de memória de memória que o SQL Server pode usar e quando esse parâmetro está configurado com 2147483647MB o SQL não fica obrigado a devolver um pouco de memória para uma aplicação que necessitar de memória  e esse processo de liberação não é automático? 


    quarta-feira, 20 de janeiro de 2016 18:36
  • Thmrs,

    Pode utilizar a consulta abaixo para verificar primeiramente qual database está consumindo todo este espaço na memória. Geralmente o SQL Server não desaloca a memória, mas em algumas situações aonde ocorrem pressão de memória pode ocorrer liberação de uma pequena quantidade de memória para o sistema operacional.

    DECLARE @total_buffer INT;
    
    SELECT @total_buffer = cntr_value
    FROM sys.dm_os_performance_counters 
    WHERE RTRIM([object_name]) LIKE '%Buffer Manager'
    AND counter_name = 'Database Pages';
    
    ;WITH src AS
    (
    SELECT 
    database_id, db_buffer_pages = COUNT_BIG(*)
    FROM sys.dm_os_buffer_descriptors
    --WHERE database_id BETWEEN 5 AND 32766
    GROUP BY database_id
    )
    SELECT
    [db_name] = CASE [database_id] WHEN 32767 
    THEN 'Resource DB' 
    ELSE DB_NAME([database_id]) END,
    db_buffer_pages,
    db_buffer_MB = db_buffer_pages / 128,
    db_buffer_percent = CONVERT(DECIMAL(6,3), 
    db_buffer_pages * 100.0 / @total_buffer)
    FROM src
    ORDER BY db_buffer_MB DESC; 

    Referência: https://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/


    Felipe Lauffer MCSA: SQL Server | MCP


    • Editado FLauffer quarta-feira, 20 de janeiro de 2016 18:48
    quarta-feira, 20 de janeiro de 2016 18:39
  • Então o minimum é com qts GB que a instancia vai começar alocando e o maximum é até onde ela pode alocar.

    Uma vez alocado o maximo de memoria o SQL Server não devolve a memoria para o SO. O que tem que ser feito é uma redução manual onde vc desaloca e depois configura novamente. 

    Se você observar quando a instancia é reiniciada ela não começa alocando o maximo de memória ele vai alocando conforme vai necessitando. 


    Se a resposta foi útil por favor classifique. Tiago Neves - @tiagolneves - acesse o meu blog http://www.tiagoneves.net


    • Editado Tiago_Neves quarta-feira, 20 de janeiro de 2016 18:57
    quarta-feira, 20 de janeiro de 2016 18:55
  • Thmrs

    Tente esse código:

    with cteSizeDB as ( 
       SELECT 'DB:'+DB_NAME(database_id) AS cDBName , 
              cast(COUNT(*) * 8 / 1024.0 as decimal(7,2)) as nSizeInMemoryMB 
         FROM sys.dm_os_buffer_descriptors 
        GROUP BY DB_NAME(database_id) 
        union all 
        select 'Mem:'+type , sum(pages_kb)/1024  
          from sys.dm_os_memory_clerks 
      where type <> 'MEMORYCLERK_SQLBUFFERPOOL' 
              group by type 
    )select * 
       from cteSizeDB 
       where nSizeInMemoryMB > 0 
      union 
     select 'Todos' , SUM(nSizeInMemoryMB) 
       from cteSizeDB 
       order by 2 desc;  
       
    

    Ele mostra o quando a memória é utilizada pelos bancos de dados e por outros cache como os de objetos de programação e query ad-hoc. 

    Veja esse link também.

    http://www.fabriciolima.net/blog/2010/12/25/casos-do-dia-a-dia-diminuindo-um-problema-de-memoria-no-sql-server/

    Boa sorte !!!



    Wolney

    quarta-feira, 20 de janeiro de 2016 19:05
  • Tiago de que forma eu posso voltar a memoria ao SO?
    segunda-feira, 14 de outubro de 2019 11:32