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:
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:
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.
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