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
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
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.
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;
Procedure Cache
usually the 2nd biggest consumer beside buffer in the memory .
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 ( '
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.
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
-
Memory Manager surface area changes in SQL Server 2012: http://blogs.msdn.com/b/sqlosteam/archive/2012/07/11/memory-manager-surface-area-changes-in-sql-server-2012.aspx
-
Memory Manager Configuration changes in SQL Server 2012: http://blogs.msdn.com/b/sqlosteam/archive/2012/07/12/memory-manager-configuration-changes-in-sql-server-2012.aspx
-
An in-depth look at SQL Server Memory–Part 1: http://blogs.msdn.com/b/sqljourney/archive/2012/12/17/an-in-depth-look-at-sql-server-memory-part-1.aspx
-
An in-depth look at SQL Server Memory–Part 2: http://blogs.msdn.com/b/sqljourney/archive/2013/02/13/an-in-depth-look-at-sql-server-memory-part-2.aspx
-
An in-depth look at SQL Server Memory–Part 3: http://blogs.msdn.com/b/sqljourney/archive/2013/03/16/an-in-depth-look-at-sql-server-memory-part-3.aspx
Good article thank you.
ReplyDeletesys.dm_os_memory_broker -> sys.dm_os_memory_brokers change I'm begging you ^ ^
Thank you
Delete