3

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. Buffer Cache Size Over Time

I don't know how useful this next picture is but it is the reason I am so confused over the memory use.SQL Memory Usage

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. Services that are running

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 Linked Server Drivers

  • Is anything else running on the same server as the instance? – LowlyDBA May 3 '18 at 15:26
  • @LowlyDBA I have updated the post to include what is running. There is only one instance on the server – Gage May 3 '18 at 15:35
  • dbcc memorystatus will give you the memory usage details. Are you, perhaps, using any third-party linked server providers? – David Browne - Microsoft May 3 '18 at 16:07
  • 2
    You are using RTM version of SQL Server 2014. There is no point in troubleshooting any memory related issue on RTM version of SQL Server considering SP2 has been released and there has been lot of bug fixes related to memory. Please upgrade ASAP and revert if you still see the issue – Shanky May 3 '18 at 16:15
  • @Shanky Good catch on RTM. @ Gage shouldn't 'revert if you still see the issue'. SQL 2014 RTM and SP1 are both out of support. The SP2 upgrade required to have a supported installation. – David Browne - Microsoft May 3 '18 at 16:29
4

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.

  • I have edited the post and added the drivers used for the linked servers. There are 14 linked servers using that driver. I was able to find a report that does show it dropping down around 5am every morning so I could look there. What would cause a memory leak like this? A query erroring out on the other end of the linked server? I am just wondering what I should be looking for. Also is there any other way to free that memory besides bounces the process? – Gage May 3 '18 at 20:24
  • "What would cause a memory leak like this?" Lots of things, mostly bugs in the 3rd party drivers. "Is there any other way to free that memory besides bounces?" Nope. It's bounces for you. – David Browne - Microsoft May 3 '18 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.