Solved

SQL Server memory Issue

Posted on 2016-11-18
7
172 Views
Last Modified: 2017-01-17
Hi Experts,

As per the below screenshots SQL Server service has occupied the 28 GB space out of 32 GB.

Task1.PNG
Task2.PNG
How could I fix it to as less as possible?

Requesting your help...

Regards,
D Patel
0
Comment
Question by:D Patel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 21

Expert Comment

by:Radhakrishnan R
ID: 41893974
Hi,
Which process is consuming more memory?  Open process tab and find out,  if it's sqldb then you can limit the memory using Sql management studio.
0
 
LVL 13

Expert Comment

by:Nakul Vachhrajani
ID: 41893977
In your case, you have two instances on the same machine which makes troubleshooting a challenge.

Under normal circumstances, it is quite okay for SQL Server to occupy memory so that it can cache as many execution plans and as it can into the memory and so for the buffer pool as well. The more SQL Server has to swap to disk, the slower your server will perform. What is the problem you are facing? In other words, why do you think high memory consumption is a problem on your server?

One more thing - do you have Max. memory configuration set on this server? Typically if the available physical RAM is > 8GB, you should reserve 8GB RAM for the OS and the rest should be available to SQL Server. In this case, the server with heavier load should get the larger share of the remaining 24GB (maybe a 14GB/10GB split).
0
 
LVL 7

Author Comment

by:D Patel
ID: 41893982
@Radhakrishnan Rajayyan
sqlservr.exe taking more space (its 18 GB).

@Nakul Vachhrajani
I have 32 GB installed on my server.

How could I reserve 8 GB space for the OS?

Please let me know the setting up process...

Regards,
D Patel
0
Free NetCrunch network monitor licenses!

Only on Experts-Exchange: Sign-up for a free-trial and we'll send you your permanent license!

Here is what you get: 30 Nodes | Unlimited Sensors | No Time Restrictions | Absolutely FREE!

Act now. This offer ends July 14, 2017.

 
LVL 13

Accepted Solution

by:
Nakul Vachhrajani earned 250 total points
ID: 41893984
Yes. Not only should you leave 8 GB for the OS, you should also set the max. memory setting on your SQL Servers (both instances) such that they do not compete with each other (you can use a split of the remaining memory as I suggested - 14GB to one instance and 10GB to another). Here's a TechNet page that will show you how to set the memory configuration: https://technet.microsoft.com/en-us/library/ms191144(v=sql.105).aspx

The more important questions are - why do you think this is too much memory being consumed? Are you facing a performance problem? Why are you running 2 SQL Server instances on the same hardware?
0
 
LVL 23

Expert Comment

by:yo_bee
ID: 41894161
The info that Nakul Vachhrajani supplied is the way to go.  Just an FYI on MS SQL.  If will use almost all the spare memory if not set to a max limit like 6 GB.  So if you have 6 to 32 GB worth of memory it will use it.  So if you where thinking if adding memory would solve this, it will not give you more free memory space, but give SQL more buffer.
1
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points
ID: 41894920
Just few additions to the information shared by other experts-

1. Quick fix will be restart the server.

2. Consuming all memory is the default behavior of SQL SERVER. If you dont specify min and max server memory configuration then SQL SERVER will consume all the memory available. It is by DESIGN.

<<For below settings you must consult your Infra Team.>>
3. Setting MIN and MAX Server memory. <<Use article for configuration settings>> https://support.microsoft.com/en-us/kb/319942

4. Max memory limits only Buffer Pool size. It does not limit unreserved memory.

Read how much memory your SQL needs- http://www.sqlskills.com/blogs/jonathan/how-much-memory-does-my-sql-server-actually-need/

Hope it helps !!
0
 
LVL 7

Author Closing Comment

by:D Patel
ID: 41894978
Thank you for your help
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

OfficeMate Freezes on login or does not load after login credentials are input.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 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