Sql-server – SQL Server 2012 Standard using too much memory

memorysql serversql-server-2012

For the past few months I have had a SQL Server 2012 Standard edition installation using up all the RAM in the server, up to 190GB (I'm not kidding). About every 3 to 4 days I end up having to restart the SQL Server service as it and the server starts becoming unresponsive. Here's a screen capture of what I am talking about:

RAM01

As you can see, SQL Server is set within SSMS to us a maximum of 64000 MB, even though being standard edition it should be license limited to 64GB.

Some details on the server:

This is a physical box with 8 cores (16 with hyper-threading), 192GB of RAM installed, and local SSD storage. We went with so much RAM and standard edition as there was a likely install of multiple instances of SQL Server 2012 as well as a MySQL installation for a custom app. This is running on Windows 2012 R1, fully up to date.

Right now we have one instance of SQL Server 2012 (version 11.5.5548, so it's SP2 and CU2) installed and running, SSRS installed and being used to hit the local DBs, and MySQL installed but not being used. The databases are multiple Sharepoint Foundation sites, a small 12GB data warehouse from our old ERP system, and finally an 80GB copy of our main ERP Dynamics AX database for reporting uses. The largest Sharepoint DB is a 29GB document repository.

I opened a ticket with Microsoft, and the two options they recommended are either to upgrade to Enterprise edition ($100,000 US), or open a Premier ticket about tuning the default out of the box Sharepoint queries.

Would anyone have any ideas?

Edit 1 Here is the results of the max server memory per request below:

RunningRAM2

Edit 2 Here are the results of exec sp_configure 'max server memory (MB)':

name                    minimum  maximum        config_value    run_value
max server memory (MB)  128      2147483647     64000           64000

Edit 3 SQL Server edition and version info

ServerEdition               ProductVersion                              O.S.
Standard Edition (64-bit)   SQL Server 2012 + SP2 + (Build11.0.5548.0)  Windows NT 6.2 <X64> (Build 9200: )

Edit 4 Linked server and perfmon info

  • Linked servers

We do have 3 linked servers, two of which use the "Microsoft OLE DB Provider for SQL Server". The other uses the "Microsoft OLE DB Provider for ODBC Drivers", and that ODBC connection is using the MySQL 64bit 5.2a driver.

  • Perfmon
    Target Server Info is at 65,539,008KB.
    Total Server Memory is currently at 39,017,864KB (the service was restarted this morning).

Edit 4 DBCC memory text dump for request from Thomas Stringer.

The DB is responding great right now. I'm posting the DBCC now and will add another once it grows over 180GB, which should be within 4 days. It's like clockwork.

DBCCMemory Output 10-31-2014

Edit 5 Request from Shanky

Memory_usedby_Sqlserver_MB Locked_pages_used_Sqlserver_MB Total_VAS_in_MB      process_physical_memory_low process_virtual_memory_low
-------------------------- ------------------------------ -------------------- --------------------------- --------------------------
78636                      0                              8388607              0                           0

Edit 6 This morning, SQL is using gobs of RAM and is very slow to respond (I can't even log in to SSMS remotely). Fortunately SSMS is still open on the server and I can run some requested queries:

Here is the result from the query "

select (physical_memory_in_use_kb/1024)Memory_usedby_Sqlserver_MB, 

(locked_page_allocations_kb/1024 )Locked_pages_used_Sqlserver_MB, 

(total_virtual_address_space_kb/1024 )Total_VAS_in_MB, 

process_physical_memory_low, 

process_virtual_memory_low from sys. dm_os_process_memory

:

Memory_usedby_Sqlserver_MB  Locked_pages_used_Sqlserver_MB  Total_VAS_in_MB process_physical_memory_low process_virtual_memory_low
175901                      0                               8388607                   1                         0

Located here (pastebin) are the results of 

select type, sum(pages_in_bytes)/1024.0/1024.00 'Mem in MB',

 count (*) 'row count' from sys.dm_os_memory_objects group by type

Results (pastebin) are here for the query 

select type, sum(pages_kb)/1024 pages_in_mb, 
sum(virtual_memory_reserved_kb)/1024 as virtual_memory_reserved_MB,
Sum(virtual_memory_committed_kb)/1024 as virtual_memory_committed_MB from `sys.dm_os_memory_clerks group by type order by pages_in_mb desc`

Updated dbcc memorystatus is located here. This has been running for over 20 minutes and has yet to complete. It seems stuck on "OBJECTSTORE_XACT_CACHE".

Edit 7 Just ran a DBCC MemoryStatus, came back in less than a second.
It can be read here

Best Answer

I don't think DBCC MEMORYSTATUS (or the other queries) are being that revealing at the moment (don't know what the others think); so I am going to ask you to run just one more:

dbcc traceon(3654, -1)
go
waitfor delay '00:05:00'
go
select top 20 memory_object_address, source_file, sum(size_in_bytes) as total_bytes
from sys.dm_os_memory_allocations
group by memory_object_address, source_file
order by total_bytes desc
go
dbcc traceoff(3654, -1)
go

You can see I've built in a delay there to allow it time to collect some information. Depending on what that reveals, I think you are faced with a choice, continue to investigate or speculate:

  1. continue to drill further into the memory leak using more advanced debugging techniques, eg using sysinternals tool Process Explorer; find process sqlservr.exe and look for evidence of memory leak, or:
  2. use our instincts and what we know about your SQL Server and make and educated guess this is most likely to do with the MySQL linked server. Next time you restart your service, temporarily disable this bit of functionality, eg drop the linked server and/or disable reports which depend upon it. Monitor for your issue. If the issue does not occur, you have a good candidate for your culprit. I think there are newer drivers available (5.3.4?) or you could consider refactoring this part of the application to an SSIS job which creates a local cache once a night for example. If the problem does not occur you at least rule it out.

As this is a live server I think your debugging options are a bit limited so it might be time to try some options, see how the server responds.

Just in reference to using DBCC MEMORYSTATUS, I have had some success with this technique in the past, for example identifying run-away full-text queries or use of OPENXML without the compulsory sp_xml_removedocument.

Related Question