Solved

SQL Server memory Issue

Posted on 2016-11-18
7
121 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:DPatel
7 Comments
 
LVL 21

Expert Comment

by:RK
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 12

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 6

Author Comment

by:DPatel
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
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 12

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 22

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 6

Author Closing Comment

by:DPatel
ID: 41894978
Thank you for your help
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

803 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