December 22, 2016 at 7:22 am
SQL Server 2014 - sqlservr.exe using an extreme amount of memory over the maximum server allocated
I have a SQL Server 2014 Enterprise instance 64 bit version (12.0.5203) on Server 2012 R2 Standard
This server has 256 GB of RAM.
Yesterday I noticed in task manager that the sqlservr.exe was using approximately 250,000,000 KB
My SQL Server memory settings were as follows:
Minimum Server memory 100GB
Maximum Server memory 200GB
Has any one experienced this behavior before? I know SQL Server uses memory outside of the min and max for certain operations so the total used can go above max but my server was dangerously close to running out of memory.
Thanks for any advice or help! -Mark G.
Partial Output from DBCC MEMORYSTATUS
Process/System Counts Value
---------------------------------------- --------------------
Available Physical Memory 4788408320
Available Virtual Memory 140093180801024
Available Paging File 40552689664
Working Set 261885042688
Percent of Committed Memory in WS 100
Page Faults 207722370
System physical memory high 0
System physical memory low 0
Process physical memory low 0
Process virtual memory low 0
Memory Manager KB
---------------------------------------- -----------
VM Reserved 430069016
VM Committed 103963488
Locked Pages Allocated 0
Large Pages Allocated 0
Emergency Memory 1024
Emergency Memory In Use 16
Target Committed 103963488
Current Committed 103963488
Pages Allocated 90312872
Pages Reserved 6760
Pages Free 6864568
Pages In Use 9407656
Page Alloc Potential 126921272
NUMA Growth Phase 2
Last OOM Factor 0
Last OS Error 0
Memory node Id = 0 KB
---------------------------------------- -----------
VM Reserved 429969992
VM Committed 51981736
Locked Pages Allocated 0
Pages Allocated 40979608
Pages Free 6864544
Target Committed 51981736
Current Committed 51981736
Foreign Committed 1195696
Away Committed 0
Taken Away Committed 0
Memory node Id = 1 KB
---------------------------------------- -----------
VM Reserved 98960
VM Committed 51981732
Locked Pages Allocated 0
Pages Allocated 49333264
Pages Free 24
Target Committed 51981736
Current Committed 51981736
Foreign Committed 0
Away Committed 0
Taken Away Committed 0
MEMORYCLERK_SQLBUFFERPOOL (node 0) KB
---------------------------------------- -----------
VM Reserved 208068756
VM Committed 5431324
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 39691256
MEMORYCLERK_SQLBUFFERPOOL (node 1) KB
---------------------------------------- -----------
VM Reserved 0
VM Committed 0
Locked Pages Allocated 0
SM Reserved 0
SM Committed 0
Pages Allocated 48009944
December 22, 2016 at 9:23 am
The max and min memory settings only affects the instance itself and not Services running outside of the instance.
So the question is:
Are there alot of Long running SQL Server Agent Jobs?
Are you auditing alot?
Is there a lot of index operations such as rebuilds, reorgs etc?
Something else to remember is that if you give SQL Server a Maximum Setting of 200GB, that is what it will eat.....
It is very likely that what you are seeing is normal behaviour with the Setup you have.
December 22, 2016 at 10:18 am
Thanks for your response!
Of all my sql agent jobs that run only one takes a while. My DBCC CHECKDB runs about 3-4 hours, however it was not running during this period when I noticed SQL Server was using 250GB.
I do not do any auditing other than the default trace.
There were no rebuilds or reorgs occurring.
I was expecting the SQL Server to use the full 200GB and maybe even a little more but not as much as I witnessed.
If it is something like the an agent job using the extra memory, should it release it when it is done?
Thanks
Mark
December 22, 2016 at 10:41 am
To be honest I think you have absolutely nothing to worry about. I reckon when you examine PLE for the instance that it will be very high, a good sign. Because you have so much memory allocated SQL Server makes use of it. That means the LRU algorithm will run less because everything fits inside the space you allocated.
I doubt very much that you are experiencing performance problems, in fact exactly the opposite.
My advice to you is to relax and simply let it run. I don't think anything is wrong at all.
The only thing I can think of is that because only 6GB is left over on the server that if you aren't careful you could starve the operating system and software that runs outside of the SQL Server Installation. I would in this case reduce the instance max memory to 190GB just to be on the safe side.
December 22, 2016 at 11:10 am
One thing I didn't mention is that users use Link Server heavily on this machine.
The Link is using Sybase OLEDB.
This could very well be the culprit.
I have lowered my max server to 160GB for now to see what happens.
I am not confident that out of the 250GB used up that 200GB of that was from the Objects inside the max server memory area. I thought this to be the case because I started to lower the max server memory in increments and did not see the 250GB go down at all.
Also because of the following values taken at the time of the pressure:
(It looks like something did not allow memory manager to use more than its minimum.)
Bytes
Working Set 261885042688
-------------------------------------------------------------
Memory Manager KB
---------------------------------------- -----------
VM Committed 103963488
Target Committed 103963488
Current Committed 103963488
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply