Non-Buffer Pool is huge size?

• SQL Galaxy

  One Orange Chip

  Points: 29472

  More actions

  July 28, 2014 at 6:23 am

  #309576

  In SQL2008 R2 64 bit, i have setting cape memory 20 GB,

  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 BufCount

  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

  [Database_Name] = CASE [database_id] WHEN 32767

  THEN'MSSQL System Resource DB'

  ELSE DB_NAME([database_id]) END,

  [Database_ID],

  db_buffer_pages as [Buffer Count (8KB Pages)],

  [Buffer Size (MB)] = db_buffer_pages / 128,

  [Buffer Size (%)] = CONVERT(DECIMAL(6,3),

  db_buffer_pages * 100.0 / @total_buffer)

  FROM BufCount

  ORDER BY [Buffer Size (MB)] DESC;

  Query to find the Buffer Pool usage per each Database, its showing 14GB,

  But remaining 9 GB where it is using? Is it non-buffer pool using for 9GB?

  How to resolve this issues? how to find out what are the swmming at Non-buffer pool side?

  Thanks

  ananda

• tfendt

  Ten Centuries

  Points: 1235

  More actions

  August 4, 2014 at 2:38 pm

  #1734951

  You are only looking at a portion of total memory usage. I would bet that the 9GB is for plan cache or just free memory.

  This site has a great script to show you all the memory usage details: t-sql-script-to-monitor-memory-usage-by-sql-server-instance