We have been having an issue with SQL Server where the buffer cache size decreases over time to the point that we have to restart the service to free up the memory. It gets low enough that is affecting performance.
The server itself has 32GB of RAM. We set the max memory on the SQL Server to 28GB. At the time of writing this the buffer cache is 5.5GB.
The version of the SQL Server is
Microsoft SQL Server 2014 – 12.0.2269.0 (X64)
Jun 10 2015 03:35:45
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: )
The output of sys.dm_os_process_memory is:
- total_physical_memory_kb 33539548
- available_physical_memory_kb 926132
- total_page_file_kb 41427432
- available_page_file_kb 5588408
- system_cache_kb 977004
- kernel_paged_pool_kb 171908
- kernel_nonpaged_pool_kb 166596
- system_high_memory_signal_state 1
- system_low_memory_signal_state 0
- system_memory_state_desc Available physical memory is high
We have a tool called Idera and it is able to provide a visual representation of the memory over time. Below is the buffer cache size over 3 weeks.
I don't know how useful this next picture is but it is the reason I am so confused over the memory use.
After a restart, the yellow area is completely gone and its just SQL allocated and SQL used. I have looked through numerous views but I have not been able to determine what is using this extra memory. If you have any suggestions or require more information please let me know.
Thanks in advance!
The services running on the server are below.
This server is used only for SQL Server. Looking at the task manager right now sqlservr.exe is using 30,264,240 K and the next highest is explorer.exe using 64,704 K
dbcc memorystatus:
Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 959946752
Available Virtual Memory 140610025795584
Available Paging File 5776338944
Working Set 31031177216
Percent of Committed Memory in WS 100
Page Faults 3832630734
System physical memory high 1
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
(10 row(s) affected)
Memory Manager KB
---------------------------------------- -----------
VM Reserved 96048512
VM Committed 5687320
Locked Pages Allocated 0
Large Pages Allocated 0
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 6149464
Current Committed 5687320
Pages Allocated 4418952
Pages Reserved 1016
Pages Free 59096
Pages In Use 2680688
Page Alloc Potential 8025776
NUMA Growth Phase 2
Last OOM Factor 0
Last OS Error 0
(16 row(s) affected)
Memory node Id = 0 KB
---------------------------------------- -----------
VM Reserved 96007536
VM Committed 2857816
Locked Pages Allocated 0
Pages Allocated 2052424
Pages Free 39536
Target Committed 3074720
Current Committed 2857816
Foreign Committed 4180
Away Committed 0
Taken Away Committed 0
(10 row(s) affected)
Memory node Id = 1 KB
---------------------------------------- -----------
VM Reserved 40912
VM Committed 2829484
Locked Pages Allocated 0
Pages Allocated 2366528
Pages Free 19560
Target Committed 3074720
Current Committed 2829488
Foreign Committed 0
Away Committed 0
Taken Away Committed 0
(10 row(s) affected)
Memory node Id = 64 KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 20
Locked Pages Allocated 0
(3 row(s) affected)
MEMORYCLERK_SQLGENERAL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 21128
(6 row(s) affected)
MEMORYCLERK_SQLGENERAL (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1024
(6 row(s) affected)
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 22152
(6 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 3171872
VM Committed 683300
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1178824
(6 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1773520
(6 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------- -----------
VM Reserved 3171872
VM Committed 683300
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2952344
(6 row(s) affected)
MEMORYCLERK_SQLQUERYEXEC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 256
(6 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2904
(6 row(s) affected)
MEMORYCLERK_QUERYDISKSTORE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2616
(6 row(s) affected)
MEMORYCLERK_SQLUTILITIES (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 205120
(6 row(s) affected)
MEMORYCLERK_SQLUTILITIES (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 128520
(6 row(s) affected)
MEMORYCLERK_SQLUTILITIES (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 333640
(6 row(s) affected)
MEMORYCLERK_SQLSTORENG (node 0) KB
---------------------------------------- -----------
VM Reserved 50048
VM Committed 50048
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16024
(6 row(s) affected)
MEMORYCLERK_SQLSTORENG (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 15184
(6 row(s) affected)
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------- -----------
VM Reserved 50048
VM Committed 50048
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 31208
(6 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2720
(6 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1576
(6 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4296
(6 row(s) affected)
MEMORYCLERK_SQLCLR (node 0) KB
---------------------------------------- -----------
VM Reserved 6304320
VM Committed 57688
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 43312
(6 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1288
(6 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1304
(6 row(s) affected)
MEMORYCLERK_SQLHTTP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 8
(6 row(s) affected)
MEMORYCLERK_SNI (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 144
(6 row(s) affected)
MEMORYCLERK_SNI (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 136
(6 row(s) affected)
MEMORYCLERK_SNI (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
MEMORYCLERK_SNI (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 296
(6 row(s) affected)
MEMORYCLERK_FULLTEXT (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 416
(6 row(s) affected)
MEMORYCLERK_SQLXP (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
MEMORYCLERK_BHF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 280
(6 row(s) affected)
MEMORYCLERK_BHF (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 208
(6 row(s) affected)
MEMORYCLERK_BHF (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 488
(6 row(s) affected)
MEMORYCLERK_SQLQERESERVATIONS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1024
(6 row(s) affected)
MEMORYCLERK_XE_BUFFER (node 0) KB
---------------------------------------- -----------
VM Reserved 4416
VM Committed 4416
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
(6 row(s) affected)
MEMORYCLERK_XE_BUFFER (node 1) KB
---------------------------------------- -----------
VM Reserved 2496
VM Committed 2496
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
(6 row(s) affected)
MEMORYCLERK_XE_BUFFER (Total) KB
---------------------------------------- -----------
VM Reserved 6912
VM Committed 6912
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
(6 row(s) affected)
MEMORYCLERK_TRACE_EVTNOTIF (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 128
(6 row(s) affected)
MEMORYCLERK_TRACE_EVTNOTIF (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 128
(6 row(s) affected)
MEMORYCLERK_TRACE_EVTNOTIF (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 256
(6 row(s) affected)
MEMORYCLERK_HOST (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 1008
(6 row(s) affected)
MEMORYCLERK_SOSNODE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 153912
(6 row(s) affected)
MEMORYCLERK_SOSNODE (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 131352
(6 row(s) affected)
MEMORYCLERK_SOSNODE (node 64) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 2496
(6 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 287760
(6 row(s) affected)
MEMORYCLERK_SOSOS (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 192
(6 row(s) affected)
MEMORYCLERK_SOSMEMMANAGER (node 0) KB
---------------------------------------- -----------
VM Reserved 277648
VM Committed 277184
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 0
(6 row(s) affected)
MEMORYCLERK_FULLTEXT_SHMEM (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 384
SM Committed 384
Pages Allocated 0
(6 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (node 0) KB
---------------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 352
(6 row(s) affected)
MEMORYCLERK_FILETABLE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 16
(6 row(s) affected)
MEMORYCLERK_XE (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 3576
(6 row(s) affected)
MEMORYCLERK_SQLLOGPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 4224
(6 row(s) affected)
MEMORYCLERK_LWC (node 0) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 128
(6 row(s) affected)
The drivers used for linked servers are below
Best Answer
Process Working Set 31GB, which is the portion of the process committed virtual memory currently in RAM. So the SQL Process is using 31GB of RAM.
SQLOS VM Committed is only 5,687MB, and Locked Pages Allocated is 0. SO SQLOS can only account for 6GB of the memory usage.
So something in the process is using 25GB of memory, and it's not SQL Server. The typical culprit here a linked server driver, which allocates memory in the process which is not tracked by SQL Servers memory clerks and pools.
You mentioned linked server to Oracle. What OleDB driver and version are you using? Any other linked server drivers used?
Eventually you need to figure out what's leaking memory in the SQL Server process and fix or eliminate it. In the short term you can bounce the SQL Server process periodically.
A good way to eliminate loading linked server drivers in SQL Server is to use SSIS. For reading data from remote sources the SSIS Data Streaming Destination enables you to isolate the 3rd party data access components in a short-lived process and still query from them like a linked server.