SQL Server is using a lot more RAM than it should

Question

I have a SQL Server 2012 instance using more RAM that it should.

The SQL Server process is using about 22.5GB RAM:

The instance is configured to use a maximum of 10GB:

Which is way more then expected. (It will lead to a server crash and we will have to reboot to get it back).

I checked the memory usage (clerks) with this query:

select type, name, pages_kb/1024.0/1024.0 "size Gb" from sys.dm_os_memory_clerks
order by pages_kb desc 

SQL Server only seem to see about 7GB RAM being used:

I know it's an old version of SQL Server (and it's not patched to the latest sadly) but I wasn't able to find any clear documentation regarding a memory leak in SQL Server 2012 SP2.

Where should I look to find why SQL Server is using about 200% what it should?

There is a linked server on this instance. A lot using SQL drivers (SQLNCLI and SQLNCLI11) but there is also some using a "PC SOFT OLE DB provider for HFSQL" which I've never seen before.

Is there any way I could 'prove' this driver is the problem? The client will probably not agree to changing the setup based on an assumption, so if there is any way (other then disabling) to clearly show how much RAM is being used by the linked server, that would be priceless.

@Aleksey: This is what the prod returns

Answer 1

The fact that dm_os_memory_clerks doesn't show the memory strongly suggests that the memory was allocated outside of the SQL Server engine.

"PC SOFT OLE DB provider for HFSQL"

That'll be the culprit. Many 3rd party OleDb providers are not hardened for use in long-lived processes. A small memory leak in a desktop application is rarely even noticeable, so they get through testing without finding the issue.

The best solution here is to move the OleDB driver to a short-lived process like an SSIS package, or PowerShell job or similar. If that's not possible you can try to push the OleDb Provider out-of-process while still using Linked Server, either clearing the "Allow InProcess" flag and doing a bunch of DCOM configuration (see eg Setting up linked servers with an out-of-process OLEDB provider ), or by using the SSIS Data Streaming Destination and replacing the Linked Server with

SELECT * FROM OPENQUERY([Default Linked Server for Integration Services], N'Folder=Power BI;Project=SSISPackagePublishing;Package=Package.dtsx')  
  

The other solution is to bounce SQL Server on a schedule.

Is there any way I could 'prove' this driver is the problem?

There's nothing that tracks native code memory allocations outside of SQL Server's control. The only way I can think to prove it is to hit the linked server repeatedly and see if you can trigger the memory leak.

If you escalate a Support case up far enough, they could probably find the leak definitively.

Answer 2

Expanding on a comment I originally left on David's answer:

A few key points. First, not all memory objects and brokers are surfaced in the DMVs, so it is technically possible to be a SQL Server allocation. Second, what you want to do is collect an xperf/wpr for memory allocations and then cut the data up by AIFO (allocated inside, freed outside) as well as peaks. That should give you the module and callstack of what is actually allocation virtual memory. Third, it's possible to turn heap tracing on via GFLAGs but that generally doesn't give as good results as xperf/wpr.

Though it won't give a direct answer, it should be a guide in how to get the data to figure out the root of the issue (though that may not be the same thing as the root cause).

I wrote a small repro to simulate your situation...

1. I set max server memory very low (it's a small VM anyway), number of threads very low, and made sure it had nothing in memory. Do keep in mind that thread stacks, modules, etc., are not counted in max server memory, and thus with a full buffer pool you'll always show higher than the max server memory value.

   

   

2. I created a quick repro which will eat up a bunch of memory. Now, I'm doing a few things the OP is missing which is that since I'm looking at types of memory allocations (although they all end up going through virtual alloc [except some physicalpages calls which I'm not using here]) so that I can tell what type of allocation is being made which can help in troubleshooting.

   Now we have much more memory usage (from 470 MB to 2.5 GB):

   Since this is a trivial repro, I setup a ETW capture and captured time before, during, and after the memory usage. Depending on the pace at which the memory allocations occur, whether it takes 1 day, 1 hour, or 1 month, your setup for collecting the data may change.

3. I can see this is specifically heap data being allocated which rules out many different items inside of SQL Server (though the heap is still used for various items), it does help in where to look.

   Since I specifically configured the ETW capture to get memory allocations (VirtualAlloc, HeapAlloc, etc.) it can be used to check what is called Allocated Inside Freed Outside, or AIFO memory, which means the memory was allocated during the capture and continued to live past the end of the capture. If I setup my environment with items such as stack, commit type, symbols, etc., it's possible to see where the majority of the memory is being used. Below is an example cut up by commit type (AIFO) and stack. Note that there are various ways this could happen, thus it might be death by 1,000 allocations, or one giant allocation, maybe somewhere in-between.

   

   You'll notice that for marker #1 there is a single 2 GB allocation outstanding (it lived on). Marker #2 shows the stack that created it. I'd like to draw your attention to the process which is still PID 756 and this is still SQL Server, however you'll see that by using the public symbols there is the stack for this 2 GB allocation which shows a call to RtlpAllocateHeapInternal which as the name suggest allocates space on a heap (in this case the default process heap), which coincides with our data above in the screen shots which show heap data being allocated. If we look at the stack in the ETW data, it shows that this was allocated through a module called CSSDet.dll of which there are no symbols on the Microsoft Public Symbols Server, which indicates this isn't a Microsoft product.

Again, this is a simple repro and I wrote the DLL, however if this were the case in an actual environment, going to check the DLL manufacturer and contacting them would be the next step, plus you'd have the callstack(s) doing the allocating and they can use that to help track down the issue in their code.

---

Can you add some detail about the ETW setup to capture and analyze this data?

The easiest way for the OP to do this would be wpr to capture a built-in profile (this makes it easier though it does collect more data). wpr -start VirtualAllocation

[...] how did you get your custom .dll to load and run [..]

I used the typical anti-virus/malware route and wrote my own module injector for user-mode processes. I did not write it for this specific purpose but it works for a quick repro. Note that I've debugged quite a few memory leaks from EKM modules as well, which is another common module loading point for 3rd party providers.