Thursday, September 26, 2013

2 SQL Server memory manager

SQL Server 2005/2008/2008R2
SQL server has 2 Memory Manager. Buffer Pool and Memory manager. Buffer Pool manage the single page , the primary consumer for the single pages are Data Buffer and procedure cache. There are other components not data buffer also use single page, it is call stolen page.  When we configure SQL server MAX Memory, we actually only setup the the Max Size of the Buffer Pool ( Single page) only. When SQL Server needs multi pages ( page more than 8K), it switch to new implement memory manger.  components outside of single pages are CLR, MPA, DWA and TS.
SQL Server 2012
Only one memory manager that mange all pages, including Buffer Pool. CLR does not directly manage by memory manager but track through the VAS allocator. components outside of memory manager are DWA and TS.




VAS size in 64-bit

x64 has 8TB . x86 has 4GB. WOW64 has 4GB user space memory, kernel space memory live outside of woo64.
Christian Bolton has VAS Summary query on his blog post SQL Server memtoleave, VAS and 64-bit - Christian Bolton's SQL Server Blog:
With VASummary(Size,Reserved,Free) AS 

(SELECT 

Size = VaDump.Size, 

Reserved =  SUM(CASE(CONVERT(INT, VaDump.Base)^0) 

WHEN 0 THEN 0 ELSE 1 END), 

Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) 

WHEN 0 THEN 1 ELSE 0 END) 

FROM 

( 

SELECT  CONVERT(VARBINARY, SUM(region_size_in_bytes)) 

AS Size, region_allocation_base_address AS Base 

FROM sys.dm_os_virtual_address_dump  

WHERE region_allocation_base_address <> 0x0 

GROUP BY region_allocation_base_address  

UNION   

SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address 

FROM sys.dm_os_virtual_address_dump 

WHERE region_allocation_base_address  = 0x0 

) 

AS VaDump 

GROUP BY Size)

 

 

SELECT SUM(CONVERT(BIGINT,Size)*Free)/1024 AS [Total avail mem, KB] ,CAST(MAX(Size) AS BIGINT)/1024 AS [Max free size, KB]  

FROM VASummary  

WHERE Free <> 0

TS – Thread stack

Below the T-SQL can use to check the current work threads.
-- Current max worker thread setting

sp_configure "max worker threads"

-- Max worker threads 

select max_workers_count from sys.dm_os_sys_info

-- current active worker thread

select count(*) from sys.dm_os_workers
image
In 64-bit environment :
  • CPU ( Logical processes )  < 4 : Max worker threads is 512.
  • CPU (Logical processes )  > 4 : Max worker threads is 512 + ( number of CPU –4 ) * 16
  • Each worker thread require 2MB memory. There the Thread stack memory size is 2MB * number of worker threads.
  • It is important to reserve the enough memory for the max worker threads . In the example above, the max worker thread is 512 . It will need 1024MB memory for TS.

DWA – Direct Windows Allocation

This is the access through extend store procedure, OLE automation ( sp_OA ) , Link server etc.
sys.dm_os_load_modules can show the DLL that load within SQL Server.
print 'Display 3rdparty DLL '

select * from sys.dm_os_loaded_modules where company <> 'Microsoft Corporation' 

Memory Pressure

  • External Physical memory pressure: Windows Memory manager
  • Internal Logical memory pressure:  Buffer Pool
  • Internal Virtual memory pressure:  VAS
sys.dm_os_memory_brokers shows the memory notification.
image
Internal resource pool is reserve for SQL Server internal only. We cannot alter it.

Buffer Pool ( Data )

  • Biggest consumer for the Memory.
  • Each Hardware NUMA node has its own memory. Hence, it has its own buffer pool.
  • Each Hardware NUMA node has its own lazy writer thread to perform the buffer pool trimming process to write the dirty pages to disk base on LRU ( Last Recent Used) Algorism . Lazy writer would only start the trimming it has low free page ( Internal logical pressure ) or Resource monitor notification ( External physical memory pressure ).
  • Lazy writer always check 16 buffers at a time.
sys.dm_os_memory_cache_clock_hands shows the lazywriter clock .
Below query shows what is in buffer pool for the current database.
-- This query show the current database objects that exist in the buffer pool 

-- DBCC DROPCLEANBUFFERS can clean the buffer pool 

SELECT obj.name              AS TableName, 

ind.name              AS IndexName, 

part.object_id        AS ObjectID, 

part.index_id         AS IndexID, 

part.partition_number AS PartitionNumber, 

buf.page_level        AS IndexLevel, 

alloc.type_desc       AS AllocationType, 

buf.page_type         AS PageType, 

buf.page_id           AS PageNumber 

FROM   sys.dm_os_buffer_descriptors buf 

INNER JOIN sys.allocation_units alloc 

ON alloc.allocation_unit_id = buf.allocation_unit_id 

INNER JOIN sys.partitions part 

ON part.hobt_id = alloc.container_id 

INNER JOIN sys.indexes ind 

ON ind.object_id = part.object_id 

AND ind.index_id = part.index_id 

INNER JOIN sys.objects obj 

ON obj.object_id = part.object_id 

WHERE  buf.database_id = Db_id() 

AND alloc.type IN ( 1, 3 ) 

AND obj.is_ms_shipped = 0 

UNION ALL 

SELECT obj.name              AS TableName, 

ind.name              AS IndexName, 

part.object_id        AS ObjectID, 

part.index_id         AS IndexID, 

part.partition_number AS PartitionNumber, 

buf.page_level        AS IndexLevel, 

alloc.type_desc       AS AllocationType, 

buf.page_type         AS PageType, 

buf.page_id           AS PageNumber 

FROM   sys.dm_os_buffer_descriptors buf 

INNER JOIN sys.allocation_units alloc 

ON alloc.allocation_unit_id = buf.allocation_unit_id 

INNER JOIN sys.partitions part 

ON part.partition_id = alloc.container_id 

INNER JOIN sys.indexes ind 

ON ind.object_id = part.object_id 

AND ind.index_id = part.index_id 

INNER JOIN sys.objects obj 

ON obj.object_id = part.object_id 

WHERE  buf.database_id = Db_id() 

AND alloc.type = 2 

AND obj.is_ms_shipped = 0 

ORDER  BY tablename, 

indexid, 

pagenumber; 
image

Procedure Cache

usually the 2nd biggest consumer beside buffer in the memory .
image
Below is the query shows each CACH Objects size for SQL 2008 . The 2012 would have some slightly difference.
EXECUTE ( 'SELECT name,  type, single_pages_kb,  multi_pages_kb, entries_count FROM   sys.dm_os_memory_cache_counters 

WHERE  type IN ( '
'CACHESTORE_SQLCP'', ''CACHESTORE_OBJCP'', ''CACHESTORE_PHDR'',''CACHESTORE_XPROC'')')
image

LPIM ( Lock pages in memory)

  • Generally, it is not required.
  • To set up, the sql server account needs to be grand “Lock pages in memory” privilege.
http://blogs.msdn.com/b/psssql/archive/2009/06/05/sql-server-and-large-pages-explained.aspx

Put it together :

Here is the query that shows the various memory status within the SQL server.
-- This is the modify version of the script that from Microsoft workshop plus  
-- DBCC FREESYSTEMCACHE('SQL Plans')
-- DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);
-- Display OS memory 
SELECT physical_memory_in_use_kb / ( 1024 )          AS 
       sql_physical_mem_in_use_mb, 
       locked_page_allocations_kb / ( 1024 )         AS awe_memory_mb, 
       total_virtual_address_space_kb / ( 1024 )     AS max_vas_mb, 
       virtual_address_space_committed_kb / ( 1024 ) AS sql_committed_mb, 
       memory_utilization_percentage                 AS working_set_percentage, 
       virtual_address_space_available_kb / ( 1024 ) AS vas_available_mb, 
       process_physical_memory_low                   AS 
       is_there_external_pressure, 
       process_virtual_memory_low                    AS is_there_vas_pressure 
FROM   sys.dm_os_process_memory 
 
-- Display SQL Server Max/Min memory  
USE master; 
 
SELECT name, 
       value, 
       value_in_use, 
       [description] 
FROM   sys.configurations 
WHERE  name LIKE '%server memory%'; 
 
-- Display Performance Counter: Memory Manager 
-- Total Server Memory (KB)"  does not show all the memory the SQL Server process is using. 
-- It only represent the total buffer pool is using 
SELECT object_name, 
       counter_name, 
       instance_name, 
       cntr_value, 
       cntr_type 
FROM   sys.dm_os_performance_counters 
WHERE  object_name LIKE '%Memory Manager%'; 
-- Display Performance Counter: Buffer Node
 
select * from sys.dm_os_performance_counters
      where object_name like '%Buffer Node%' 
 
-- Display Cache Objects memory usage
SELECT objtype,count(*) AS 'Number of Plans', SUM(cast(size_in_bytes AS bigint ) ) /1024/1024 AS 'Plan Cache Size(MB)',
avg(usecounts) AS 'Avg Use Count'
FROM sys.dm_exec_cached_plans
GROUP BY objtype
 
-- Display Buffer Pool size used by each DB
SELECT 
CASE database_id    
    WHEN 32767 THEN 'ResourceDB'
    ELSE db_name(database_id)
    END AS 'Database',count(*)*8/1024 AS 'Cache Size(MB)'
FROM sys.dm_os_buffer_descriptors
GROUP BY db_name( database_id) ,database_id
ORDER BY 'Cache Size(MB)' DESC
 
 
-- Display memory Clerks 
DECLARE @pversion SQL_VARIANT; 
 
-- Initialize the variable. 
SELECT @pversion = Serverproperty('ProductVersion') 
 
PRINT 'Product Version :' 
      + CONVERT(VARCHAR(30), @pversion) 
 
SELECT * 
FROM   sys.dm_os_memory_nodes; 
 
IF CONVERT(VARCHAR(30), @pversion) LIKE '10.5%' 
  BEGIN 
     -- Display the Cache Store 
EXECUTE ( 'SELECT name,  type, single_pages_kb,  multi_pages_kb, entries_count FROM   sys.dm_os_memory_cache_counters 
WHERE  type IN ( ''CACHESTORE_SQLCP'', ''CACHESTORE_OBJCP'', ''CACHESTORE_PHDR'',''CACHESTORE_XPROC'')')   
     -- View the various memory clerks and their current memory allocations by name and memory node
      EXECUTE ('SELECT type,        name,        memory_node_id,        Sum(multi_pages_kb + single_pages_kb +            + virtual_memory_reserved_kb            + virtual_memory_committed_kb            + awe_allocated_kb            + shared_memory_reserved_kb            + shared_memory_committed_kb) AS TotalKB FROM   sys.dm_os_memory_clerks GROUP  BY type,           name,           memory_node_id ORDER  BY totalkb DESC') 
 
      -- View the current state of the memory brokers 
      -- Note the current memory, the predicted future memory, the target memory and whether the memory is growing, shrinking or stable
      SELECT p.name                   AS resource_governor_pool_name, 
             b.memory_broker_type, 
             b.allocations_kb         AS current_memory_allocated_kb, 
             b.allocations_kb_per_sec AS aallocation_rate_in_kb_per_sec, 
             b.future_allocations_kb  AS near_future_allocations_kb, 
             b.target_allocations_kb, 
             b.last_notification      AS last_memory_notification 
      FROM   sys.dm_os_memory_brokers b 
             INNER JOIN sys.resource_governor_resource_pools p 
                     ON p.pool_id = b.pool_id; 
  END 
ELSE IF CONVERT(VARCHAR(30), @pversion) LIKE '11%' 
  BEGIN 
       -- Display the Cache Store 
EXECUTE ( 'SELECT name,  type, pages_kb, entries_count FROM   sys.dm_os_memory_cache_counters 
WHERE  type IN ( ''CACHESTORE_SQLCP'', ''CACHESTORE_OBJCP'', ''CACHESTORE_PHDR'',''CACHESTORE_XPROC'')')   
 
      -- View the various memory clerks and their current memory allocations by name and memory node
      EXECUTE ('SELECT type,        name,        memory_node_id,        Sum(pages_kb + virtual_memory_reserved_kb            + virtual_memory_committed_kb            + awe_allocated_kb            + shared_memory_reserved_kb            + shared_memory_committed_kb) AS TotalKB FROM   sys.dm_os_memory_clerks GROUP  BY type,           name,           memory_node_id ORDER  BY totalkb DESC') 
 
      -- View the current state of the memory brokers 
      -- Note the current memory, the predicted future memory, the target memory and whether the memory is growing, shrinking or stable
      SELECT p.name                   AS resource_governor_pool_name, 
             b.memory_broker_type, 
             b.allocations_kb         AS current_memory_allocated_kb, 
             b.allocations_kb_per_sec AS allocation_rate_in_kb_per_sec, 
             b.future_allocations_kb  AS near_future_allocations_kb, 
             b.target_allocations_kb, 
             b.last_notification      AS last_memory_notification 
      FROM   sys.dm_os_memory_brokers b 
             INNER JOIN sys.resource_governor_resource_pools p 
                     ON p.pool_id = b.pool_id; 
  END 
ELSE 
  PRINT 'Not test yat'; 
 
 
print 'Display 3rdparty DLL '
select * from sys.dm_os_loaded_modules where company <> 'Microsoft Corporation' 

Reference

2 comments:

  1. Good article thank you.
    sys.dm_os_memory_broker -> sys.dm_os_memory_brokers change I'm begging you ^ ^

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates