Objective: Assess the memory foot print of MSSQL using microsoft sysinternal tools
MSSQL version: 15.0.4
Windows version: Windows 2022 Standard Evaluation
Current Setting: No LPIM (Lock Pages In Memory)
Reference: https://learn.microsoft.com/en-us/sql/relational-databases/memory-management-architecture-guide?view=sql-server-ver16
Connect to sql server:
sqlcmd -S <servername> -s "," -y 30 -Y 30
Query to use to get the information on current allocated memory for a MSSQL server:
SELECT
physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
page_fault_count AS sql_page_fault_count,
memory_utilization_percentage AS sql_memory_utilization_percentage,
process_physical_memory_low AS sql_process_physical_memory_low,
process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;
Query Output:
sql_physical_memory_in_use_MB,sql_large_page_allocations_MB,sql_locked_page_allocations_MB,sql_VAS_reserved_MB ,sql_VAS_committed_MB,sql_VAS_available_MB,sql_page_fault_count,sql_memory_utilization_percentage,sql_process_physical_memory_low,sql_process_virtual_memory_low
-----------------------------,-----------------------------,------------------------------,--------------------,--------------------,--------------------,--------------------,---------------------------------,-------------------------------,------------------------------
286, 0, 0, 11829, 420, 134205898, 192417, 100, 1, 0
(1 rows affected)
1>
Microsoft says...
1. When the MSSQL server is initialized it starts to consume the memory and it doesnt drops any memory until min server memory value is reached. The server never consumes memory more than max server memory.
2. When we specify same value for both min and max server memory, the SQL server doesnt either acquires or releases the memory dynamically
Query to get the min server and max server memory config:
select a.name,a.value,a.value_in_use,b.name,b.value,b.value_in_use
from sys.configurations a,sys.configurations b
where a.name='max server memory (MB)'
and b.name='min server memory (MB)'
order by 1;
query Output:
name ,value ,value_in_use ,name ,value ,value_in_use
------------------------------,------------------------------,------------------------------,------------------------------,------------------------------,------------------------------
max server memory (MB) ,2147483647 ,2147483647 ,min server memory (MB) ,0 ,16
(1 rows affected)
1>
Compared this result against SSMS -> Instance -> Properties -> Memory, we saw the max server memory is set to 2TB, which is the default value as per the doc.
Let us ensure we fix it on next start as per Microsoft recommendation.
Now before we post the picture of the process explorer, let us understand few basic terms...
Reference: https://stackoverflow.com/a/1986486
Private Bytes - are what your app has actually allocated, but include pagefile usage; <<<<<<<<<<<<< Physical memory + Paged file (- shared dll space*)
Working Set - is the non-paged Private Bytes plus memory-mapped files; <<<<<<<<<<<< fully on physical memory
Virtual Bytes - are the Working Set plus paged Private Bytes and standby list. <<<<<<<<<<<<<<<<<<<<<<<< Physical memory + Paged file + shared dll space
sqlceip.exe - Telemetry data (error and reporting data collected from our server sent to Microsoft)
sqlwriter.exe - A process helps VSS (Volume Shadow copy Service) to backup the sqlserver, remember MSSQL when keeps a datafile open, you cant read the file externally from another utility unless it is sqlwriter.exe which facilitates it.
sql_physical_memory_in_use_MB: 286MB
tasklist command:
sqlservr.exe 2516 Services 0 294,368 K
It reports we are using 287MB (is ~ matching with
sql_physical_memory_in_use_MB)
sql_VAS_committed_MB: 420MB
We see this value in procexp.exe under private bytes as we see below...
sqlservr.exe -> 431,612K (422MB).
As per the Microsoft documentation, the SQLServer will adjust its occupancy based on OS memory stress.
As soon as I spined up more tools, the SQL server as per the document reduced its memory demand... see below.
sql_physical_memory_in_use_MB,sql_large_page_allocations_MB,sql_locked_page_allocations_MB,sql_VAS_reserved_MB ,sql_VAS_committed_MB,sql_VAS_available_MB,sql_page_fault_count,sql_memory_utilization_percentage,sql_process_physical_memory_low,sql_process_virtual_memory_low
-----------------------------,-----------------------------,------------------------------,--------------------,--------------------,--------------------,--------------------,---------------------------------,-------------------------------,------------------------------
116, 0, 0, 11827, 420, 134205900, 269007, 53, 1, 0
(1 rows affected)
If we observe above, the physical memory (working set) occupancy came down to 116MB from 286MB. though the private bytes stayed around same value.
Another userful view to get a lot of information on the os & sql server config info is sys.dm_os_sys_info (as the name os_sys_info implies, it is the view for os specific info providing details about mssql as well).
cpu_ticks ,ms_ticks ,cpu_count ,hyperthread_ratio,physical_memory_kb ,virtual_memory_kb ,committed_kb ,committed_target_kb ,visible_target_kb ,stack_size_in_bytes,os_quantum ,os_error_mode,os_priority_class,max_workers_count,scheduler_count,scheduler_total_count,deadlock_monitor_serial_number,sqlserver_start_time_ms_ticks,sqlserver_start_time ,affinity_type,affinity_type_desc ,process_kernel_time_ms,process_user_time_ms,time_source,time_source_desc ,virtual_machine_type,virtual_machine_type_desc ,softnuma_configuration,softnuma_configuration_desc ,process_physical_affinity ,sql_memory_model,sql_memory_model_desc ,socket_count,cores_per_socket,numa_node_count,container_type,container_type_desc
--------------------,--------------------,-----------,-----------------,--------------------,--------------------,--------------------,--------------------,--------------------,-------------------,--------------------,-------------,-----------------,-----------------,---------------,---------------------,------------------------------,-----------------------------,-----------------------,-------------,------------------------------,----------------------,--------------------,-----------,------------------------------,--------------------,------------------------------,----------------------,------------------------------,------------------------------,----------------,------------------------------,------------,----------------,---------------,--------------,------------------------------
28035808001320, 11679129, 2, 2, 2080452, 137438953344, 224568, 380912, 380912, 2093056, 4, 5, 32, 256, 2, 11, 2, 16621,2023-04-30 11:49:14.687, 2,AUTO , 1135453, 4545375, 0,QUERY_PERFORMANCE_COUNTER , 1,HYPERVISOR , 0,OFF ,{{0,3}} , 1,CONVENTIONAL , 1, 2, 1, 0,NONE
(1 rows affected)
1>
description of the view is: https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-info-transact-sql?view=sql-server-ver16
notice commited_kb and committed_target_kb; if commited_kb < commited_target_kb, MSSQL might try growing the memory foot print and vice versa also applies.
You get to see the SQL server startup time from here "sqlserver_start_time"
You can get physical memory in the server "physical_memory_kb"
You can check "sql_memory_model_desc" to see if we are using conventional mode of memory or LPIM or large memory.
In a situation where there is memory pressure:
Like SGA in oracle, in MSSQL the memory management is called dynamic memory management. The framework includes a background task called Resource monitor.
The resource monitor tasks monitors external and internal memory indicators. Resource moitor task broadcasts the calculated messages to ring buffers.
2 ring buffers - 1. resouce monitor ring buffer, which tracks overall memory pressure, 2. memory broker ring buffer, which tracks notification for each resouce governer resource pool.
Memory broker monitor and calculates the optimal memory value for each of the component.
There are a set of memory brokers per resouce governer resource pool.
Query to review under memory pressure situation:
select * from sys.dm_os_memory_brokers;
Output:
1> select * from sys.dm_os_memory_brokers;
2> go
pool_id ,memory_broker_type ,allocations_kb ,allocations_kb_per_sec,predicted_allocations_kb,target_allocations_kb,future_allocations_kb,overall_limit_kb ,last_notification
-----------,------------------------------,--------------------,----------------------,------------------------,---------------------,---------------------,--------------------,------------------------------
1,MEMORYBROKER_FOR_CACHE , 9656, 0, 9656, 6792, 0, 180088,SHRINK
1,MEMORYBROKER_FOR_STEAL , 82048, 0, 82048, 57736, 0, 180088,SHRINK
1,MEMORYBROKER_FOR_RESERVE , 0, 0, 0, 5624, 0, 180088,STABLE
1,MEMORYBROKER_FOR_COMMITTED , 65056, 16, 65120, 45824, 0, 180088,SHRINK
1,MEMORYBROKER_FOR_HASHED_DATA_P, 0, 0, 0, 5624, 0, 180088,STABLE
1,MEMORYBROKER_FOR_XTP , 6272, 0, 6272, 5624, 0, 180088,SHRINK
2,MEMORYBROKER_FOR_CACHE , 2048, 2, 2056, 5624, 0, 180088,STABLE
2,MEMORYBROKER_FOR_STEAL , 1224, 52, 1432, 5624, 0, 180088,STABLE
2,MEMORYBROKER_FOR_RESERVE , 0, 0, 42176, 5624, 42176, 180088,STABLE
2,MEMORYBROKER_FOR_HASHED_DATA_P, 0, 0, 0, 5624, 0, 180088,STABLE
2,MEMORYBROKER_FOR_XTP , 47160, 0, 47160, 33184, 0, 180088,SHRINK
(11 rows affected)
Notice pool_id 1 & 2 - 2 pools (resource governer resouce pools - 2 brokers for each pool for instance MEMORYBROKER_FOR_CACHE is there twice, I guess the pool is resource pool is cache and there 2 brokers for it to manage the meory resize operations).
If you notice, in oracle the same resize operation happens between buffer_cache and all other pools of SGA (AWR will show you the memory resize operation).
Same in MSSQL is shown here.
Thanks
