Solved

SQL Server memory Issue

Posted on 2016-11-18
7
168 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…

739 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