3 Replies Latest reply on Nov 19, 2015 7:27 AM by andrep1

    High Memory Utilization From EPO Database in SQL Server

    shavinka.fernando

      Hi All,

       

      Recently I noticed that our server with SQL Server 2008 R2 is having high memory usage all the time due to SQL Server process utilizing 15GB of ram.

       

      When investigating about this I ran the following query which gave me the output below.

       

       

      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;
      
      

       

      m1.PNG

      It seems that EPO database is using 10GB of the 15GB allocate to SQL Server. Ran the below SQL query and got the following output.

      (SQL query location - How do I find what is consuming SQL Server's memory?)

       

      USE ePO_Master;
      
      
      WITH src AS(   SELECT       [Object] = o.name,       [Type] = o.type_desc,       [Index] = COALESCE(i.name, ''),       [Index_Type] = i.type_desc,       p.[object_id],       p.index_id,       au.allocation_unit_id  
      FROM       sys.partitions AS p   INNER JOIN       sys.allocation_units AS au       ON p.hobt_id = au.container_id   INNER JOIN       sys.objects AS o       ON p.[object_id] = o.[object_id]   INNER JOIN       sys.indexes AS i       ON o.[object_id] = i.[object_id]       AND p.index_id = i.index_id   WHERE       au.[type] IN (1,2,3)       AND o.is_ms_shipped = 0)
      SELECT   src.[Object],   src.[Type],   src.[Index],   src.Index_Type,   buffer_pages = COUNT_BIG(b.page_id),   buffer_mb = COUNT_BIG(b.page_id) / 128
      FROM   src
      INNER JOIN   sys.dm_os_buffer_descriptors AS b 
      ON src.allocation_unit_id = b.allocation_unit_id
      WHERE   b.database_id = DB_ID()
      GROUP BY   src.[Object],   src.[Type],   src.[Index],   src.Index_Type
      ORDER BY   buffer_pages DESC;
      
      

       

      m2.PNG

       

      I would like to get support in order to know what and why such memory is used. Also how to reduce this and maintain it in the future.

       

      Appreciate you support.

       

      Thanks & Regards,

      Shavinka