none
memory usage

    Pergunta

  • Hi,
    In the task manager, I see sql server being used by SYSTEM and it is using about 3 GB of memory.
    How can I find out what it is that is using so much memory?
    Thanks
    quinta-feira, 7 de junho de 2012 14:03

Respostas

  • Run this:

    -- Note: querying sys.dm_os_buffer_descriptors
    -- requires the VIEW_SERVER_STATE permission.
    
    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 = 'Total 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;

    quinta-feira, 7 de junho de 2012 14:06
  • Did you run the code above?  I am going to guess that you didn't since it is nothing like sp_who2 in output or information.  The above shows you actual buffer pool usage by database by reading the buffer descriptors from a DMV.  It has nothing to do with executing session information at all. 

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    quinta-feira, 7 de junho de 2012 14:35
    Moderador

Todas as Respostas

  • Run this:

    -- Note: querying sys.dm_os_buffer_descriptors
    -- requires the VIEW_SERVER_STATE permission.
    
    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 = 'Total 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;

    quinta-feira, 7 de junho de 2012 14:06
  • Read this blog post that explains how SQL Server uses memory:

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx


    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    quinta-feira, 7 de junho de 2012 14:07
    Moderador
  • Run this:

    -- Note: querying sys.dm_os_buffer_descriptors
    -- requires the VIEW_SERVER_STATE permission.
    
    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 = 'Total 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;

    Isn't this similar to sp_who2 ?
    It does not seem to be any runnable queries. It is something else I am trying to find.
    Any thoughts please?
    quinta-feira, 7 de junho de 2012 14:19
  • Did you run the code above?  I am going to guess that you didn't since it is nothing like sp_who2 in output or information.  The above shows you actual buffer pool usage by database by reading the buffer descriptors from a DMV.  It has nothing to do with executing session information at all. 

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    quinta-feira, 7 de junho de 2012 14:35
    Moderador
  • Did you run the code above?  I am going to guess that you didn't since it is nothing like sp_who2 in output or information.  The above shows you actual buffer pool usage by database by reading the buffer descriptors from a DMV.  It has nothing to do with executing session information at all. 

    Jonathan Kehayias | Principal Consultant, SQLSkills.com
    SQL Server MVP | Microsoft Certified Master: SQL Server 2008
    Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
    Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
    Please click the Mark as Answer button if a post solves your problem!

    yes, ran it now. Noticed the figures.
    Then changed the recovery model of one of the active databases to from full to simple (Since there is no requirement for transaction logs).
    Then ran your query again. The buffer had reduced by 150 MB
    quinta-feira, 7 de junho de 2012 14:53
  • Thanks
    quinta-feira, 14 de junho de 2012 11:22