SQL Server using up a lot of memory

Hi Experts,

While logging into the server today I realized the SQL server was using a lot of server memory
See attached.

Is there a way I can get it back to normal (6GB) without restarting it, as currently its using double..?

Also perhaps I can get to see what exactly caused that sudden change?
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael MachieFull-time technical multi-taskerCommented:
Sql will by design consume as much memory as it can to have a reserve available at all times -this its normal. When your server requires use of the reserved memory Sql will release it and then reclaim it when available. I cannot see your picture to review that.

Sql should not consume more than 85-90% of the total memory though. Again, this is by design. There are ways to limit the amount of memory Sql will consume and although an option it its best to let Sql do what it does. I do not know how you would check to see why it jumped up but I am not surprised at all that it consumed a lot of memory.
0
arnoldCommented:
As was stated, SQL will consume as much me,Roy as is available to it.  Usually, SQL server is set to use 2Tb or what the system has whichever is less. The size of the DB is what raises the sql's memory consumption.

you can restrict the amount SQL server will use via the properties of tge server using ssms, memory here you can set the starting/min and Max memory that SQL can use.

Adding more memory might be better if possible.

Are you seeing issues with the system?
Restricting/reducing could manifest in reduced performance.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
No attachments were provided.
While logging into the server today I realized the SQL server was using a lot of server memory
That's good, specially if the server is dedicated to SQL Server. Just check if some memory is free and available to Windows OS (something around 1,5GB - 2GB at minimum).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bfuchsAuthor Commented:
Hi Experts,

Not sure why it didn't save the attachment, here it is..

My concern is the following

Since the server is used for remote users to access our system as well, in other words its not dedicated only for the SQL process, therefore I would not prefer it should be using more memory than necessary, as you can see on attached, currently its up to 73% of the servers memory used..

As mentioned, while monitoring the system, I see only in rare cases does it jump so high (12GB), usually its stays by 6/7 GB, therefore I suspect that one none responding process probably eat up the big portion of it...and if I can only kill that process in order to release some memory I would definitely do so.

Thanks,
Ben
Untitled.png
0
arnoldCommented:
The number of remote users logged in will raise the consumed memory.

You can restrict the maximum amount of ram SQL server is permitted to consume.
Defining that threshold deals with what your remote users need to do in that server.
Memory consumption at or near 70,80,90% is not in itself a reflection of load on the system.

A jump in memory could be a result of a remote user using ssms running a complex query with many records. As soon as that session ends, the memory resources and others will be released.
0
bfuchsAuthor Commented:
@arnold,

At this point I am not looking to restrict SQL of consuming the necessary memory.

A jump in memory could be a result of a remote user using ssms running a complex query with many records. As soon as that session ends, the memory resources and others will be released.

This is exactly what I am suspecting...and therefore looking to find out which user is that..?

FYI- No user has access to ssms, the only connection they have to the database is thru an Access app linked to SQL tables.

Thanks,
Ben
0
arnoldCommented:
the restriction of max memory use is the only way to be sure it was not it.
As to your need to identify the user who might ......

You have to have perfmon or other monitoring tools that collect that information from the server.
Or you have to when the issue arises, sort the process by memory consumption and see which processes are atop said list and to which users those processes belong.
0
bfuchsAuthor Commented:
@Arnold,
As soon as that session ends, the memory resources and others will be released.
I don't see that's happening, all users had already logged off the server and the memory usage didn't dropped yet..

sort the process by memory consumption and see which processes are atop
This is what I did as you can see in attachment, however I only get the SQLservr.exe, but no idea how to get within the SQL itself which SQL command is causing it?

Also I checked now the SQL profiler (2008) included all columns, and don't see a column for memory consumption?

Thanks,
Ben
0
arnoldCommented:
Restricting the maximum amount of memory an SQL server can consume will be the only way to keep sqlserver from using 12Gb of memory.
SQL server once obtaining an allocation of memory, it does not release it.

As to identifying with the SQL server and what leads to the need for memory triggering sqlserver request for additional memory is all together different.

One thing is to limit tge SQL server growth.
Second deals with identifying what queries users using access might run against the database that would have explained why SQL server needed additional memory.
0
bfuchsAuthor Commented:
OK I agree with you on the concept that these are two different things.

However I would not limit the SQL usage of memory unless I know that this will not affect the application in general.

And this should be another reason to try identify the process(es) using up that qty of memory, So I can determine if it is a legitimate reason and then I do need to reserve this amount of memory for SQL, or something that should have not been done by the user/application and in that case I should consider the limitation of memory.

Thanks,
Ben
0
arnoldCommented:
Here is the dilema. The rise in memory need of sql server is unpredictable. It will when needed request more from the system as needed. So upon service start it cycles through memory allocation requests as needed to ramp up. without a minimum setting there are some processing to ramp up based on the existing DBs and queries it sees. as time goes on and more queries, sp, tasks are performed it expands the memory it consumes.

Days, weeks, months pass with the utilization by SQL server at 6-7GB out of, and then the sql server allocation is at 12Gb.

The following link describes what is known and referenced early on, the build up of memory is over time, but there is no mechanism that sql releases the memory when not used over time.

https://social.technet.microsoft.com/Forums/sqlserver/en-US/9f23284f-8c9c-4595-9d02-0aab9d8cc763/sql-server-does-not-release-physical-memory?forum=sqldatabaseengine

 A discussion along the same line as you have, you could try using the sp_configure to set a max memory that is lower than the current utilization.

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/bbd2cce3-59f2-4d51-a5bb-49c5f41ea9b2/how-to-release-memory-from-sql?forum=sqlgetstarted

The second part of your dilema is to identify the reason when and why the memory ustilization spikes.
Imposing the max limit will do that presumably the impact will be that when the user/process causing/requiring more memory is performed, the system will labor without the additional memory allocation meaning it could take longer to process the query.
The user in question will then complain.
The other option without the restriction is to use a monitoring tool that will monitor the memory utilization of the server and the sql server process in particular while at the same time you monitor who logs into the system. perfmon to monitor the memory usage of sqlservice and creating an alert when the memory allocation to the process rises above 8Gb,12Gb........

With the data on when the spike takes place and the information on who was logged in at the time, you could check with those individuals to see what queries, tasks they ran.
Using the same logic, make sure the rise in the memory consumption does not correspond to a weekly, monthly, sql job rebuilding or reorganizing indexes........


Does the high utilization of memory by the sql server have an adverse performance impact on the system/users?
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thanks very much Arnold!

I was mainly concerned about the 70% of the overall memory of the server, as this server is also being used as terminal server, and each additional user connected remotely adds to the memory usage, therefore I need to make sure we dont run out of memory.

Just wondering why exactly did it jump 100% from 6 to 12 at once, is it like the binary settings that it always doubles (like 16 bit, 32, 64 etc..) and next jump of memory usage I can expect to be using 24 GB? in that case we are in big trouble..
0
arnoldCommented:
It is hard to say. If not impossible  it could be that a user needed tote berate a report performing a complex query to build a temp table that is then combined with several other temp tables that have different data ........

In your capacity presumably sysadmin, knowing what tasks users commonly run and what other less frequent tasks users run is the only way to narrow down the scope I.e. Running the complex process if used. Checking SQL jobs to see if the spike in memory corresponds to scheduled tasks.
0
bfuchsAuthor Commented:
I see.

Thanks again,
Ben
0
Michael MachieFull-time technical multi-taskerCommented:
In regards to the memory usage per RDP profile I generally go by the rule of 256mb memory for each User profile specifically. Obviously more memory will be used when launching programs.
0
bfuchsAuthor Commented:
@Machienet,

How do you set up a limit of memory usage per user?

Thanks,
Ben
0
Michael MachieFull-time technical multi-taskerCommented:
There is no way that I am aware of to limit the memory usage by RDP session.
0
arnoldCommented:
Your issue is not the user consumed more memory, your issue is that your sql server memory consumption exceeded your normal operating range for some reason.

While you can not limit how much memory each user session would consume, you could limit which applications each users could run limiting their memory consumption on that basis.

See earlier part.
0
bfuchsAuthor Commented:
you could limit which applications each users could run limiting their memory consumption on that basis.
The only thing they use is a custom designed access system linked partially to SQL tables, and while a few users are connected to the server thru RDP, the majority of them has just access to the application.

As mentioned above, it looks like SQL reserves a certain amount of memory it expect to be used, and once reached that point it will automatically move on to reserve addotional memory, however instead of just increasing by small amount (let say one GB) it doubles the previous setting, If I could just change that behaviour..would be a great help!

Thanks,
Ben
0
arnoldCommented:
It reserves as much memory as it "determines" us needed to perform the query.
If you are tasked for a complex response, you will not simply grab a piece of paper (single sheet) as you need it, you will grab a bunch.
There is a cost of intermittently request ion additional snippet of memory given there is a time limit on queries.....

How about you try the sp_configure and bind the SQL server with Max memory it can use and see what the remafications if any there are. Unless you want to try the other meaning let SQL consume/reserve as much memory as it needs while you analyze whether the larger memory use adversely impacts both SQL responsiveness to all users as well as whether the RDP users are impacted adversely.
0
bfuchsAuthor Commented:
@Arnold,

Sorry for bothering you, I just have one more question before setting up limitations, how about if its possible to get the qty of memory user per session ID seen on activity monitor, if that can be saved in something like a trace file, so I can then do some reports (perhaps a group by hour/min) and see the total memory being used in general at once, and accordingly decide what is the appropriate amount of memory to set the limit for?

Thanks,
Ben
0
arnoldCommented:
You would need to pool/poll that information using monitoring tools and scripts
I.e. Monitoring tools would monitor the aggregate memory use. Including perfmon
Scripts will need to look at logged in users, identify each user's processies and sum the memory/virtual allocated to them......

Note that none of these will answer your main question here which is why the memory reserved/used by ms SQL has gone to 12GB?
0
bfuchsAuthor Commented:
OK Arnold,

If these info would be accessible thru ssms would be great, however In order to invest in other tools I would need approval from the manager,

Again Thanks very much for your help.
Ben
0
arnoldCommented:
Ssms? You want to poll the SQL server to see how it is using the memory allocated to it and determine the per user breakouts?

Does the system performance degrades when the SQL server is allocated all the remaining memory not used by the OS?
If it does, restrict the amount of memory that SQL server can use. Then determine whether that restriction manifests as slower responses to  application.
SQL back end data(database) continuously increases in size which would necessarily grow the amount of memory SQL server continues to request. Commonly
0
bfuchsAuthor Commented:
@Arnold,

The main problem here is that besides of the fact that SQL once it allocates memory it will not release it, additionally it does not have a way of showing how much memory is currently in use, we have to do our guessing and hope for the best..

Perhaps can it be based on the size of the DB and the amount of simultanious users?

P.S. you really did your share here..I may open a new post & discuss my remaining concerns re this matter.

Thanks,
Ben
0
arnoldCommented:
Sql is akin to a truck.
When you have to haul items daily, you get one size truck. As the amount of load to haul grows, you do not get different sized trucks, you replace one with a larger one.

SQL can perform with the amount of memory available smaller/lower than the size of the databases the instance holds. The performance will be impacted because the SQL has to rotate unneeded data out, while performing/responding .....

IMHO, allow SQL consume as much memory as it needs. Given the SQL server is also serves RDs/ts role for a few users. And instead of concentrating on the amount of memory used, look at the performance responsiveness SQL and users.

How big are your DBS and how active are they?
Users using similar app have responses cached.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.