Solved

Sql server Memory Issue

Posted on 2016-08-14
5
69 Views
Last Modified: 2016-08-15
Hi,
When iam checking SQL Logs i found following message
is marked for unload due to memory pressure

Why this kind of issue happening?
0
Comment
Question by:ukerandi
5 Comments
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 83 total points
ID: 41755657
Another process has requested memory currently occupied by the SQL Server instance. SQL Server monitors memory usage of other processes for exactly that reason: to be able to deallocate own memory used for buffering data (to improve speed). Memory allocation and consumption is dynamic for MS SQL.

If this happens once a day e.g. because of a backup or other nightly processes temporarily requiring a lot of memory, it is no issue. If you get the message a lot while working hours, you should monitor memory consumption of all processes, and then decide whether it is correctable at that other process or only by adding more RAM to your machine.
0
 
LVL 7

Assisted Solution

by:Phil Davidson
Phil Davidson earned 83 total points
ID: 41755735
For further information, you can go to this link.  

The above link is useful if are using CLRs or if this is a problem.  If you know of no problems, then the information you are seeing is not something to cause you alarm.  You may not need to do anything about it.
0
 
LVL 80

Assisted Solution

by:David Johnson, CD, MVP
David Johnson, CD, MVP earned 83 total points
ID: 41755757
SQL server by default is set to use as much memory as available (the high limit is set at some unreachable number 1.6PB (I think)
A good guideline is: Gene Barrys Suggested Settings http://bit.ly/2bvTiBm
Physical RAM                        MaxServerMem Setting
2GB                                           1500
4GB                                           3200
6GB                                           4800
8GB                                           6400
12GB                                         10000
16GB                                         13500
24GB                                         21500
32GB                                         29000
48GB                                         44000
64GB                                         60000
72GB                                         68000
96GB                                         92000
128GB                                       124000

This greatly depends upon the sql server load, size of databases, memory used by other than Base Operating System. If your queries start taking a long time or the CPU load/Disk Performance spike then you have to give it more memory. The machine itself may require a lot more memory.
0
 
LVL 18

Accepted Solution

by:
Mal Osborne earned 251 total points
ID: 41755863
Basically, whenever SQL reads anything from disk, it keeps a copy in RAM, just in case that bit of data might need to be accessed again. Generally, this means that after some time running, total memory usage approaches the size of the data files, or nearly all physical memory.

Once other applications demand memory, and it gets low, SQL will log the "marked for unload due to memory pressure" and release some of the cached memory, so that other applications can use it. Idea is to keep stuff in RAM unless there is a reason to purge it.

Exchange does the same thing, it will also use whatever RAM is available. If a user opens a 10MB .PDF file on Monday, it will stay in RAM until either the Store service is restarted, or RAM is needed for something else.

Unless you want to leave some RAM unused for some reason, it is usually OK to just let SQL do its thing. On a dedicated QQL server, it usually works fine without any manual tuning.
0
 
LVL 10

Author Closing Comment

by:ukerandi
ID: 41756441
Thanks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Trying to figure out group policy inheritance and which settings apply where can be a chore.  Here's a very simple summary I've written which might help.  Keep in mind, this is just a high-level conceptual overview where I try to avoid getting bogge…
Resolve DNS query failed errors for Exchange
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question