Solved

SQL Server ate a lot of memory

Posted on 2009-05-07
6
212 Views
Last Modified: 2012-05-06
Hi ;

I have a SQL server with 3 instances inside . SQL2005Std,SQL2008 and SQL 2005 Dev Ed. The physical server which holds the instances is 12GB RAM. At all time , the task manager shows that the memory is heavily utilized as follows:-
Physical Memory (K)
 Total               12580524
 Available            208760
 System Cache    498440    

And the PF Usage shows 12.1GB is being used.
When I check , it shows SQL services is using most of it . And I do get an alert " Windows is low on virtual memory " frequently.

I did get to know about this script :-
EXEC sys.sp_configure N'max server memory (MB)', N'1024'
GO
RECONFIGURE WITH OVERRIDE
GO
but I'm worried if it will impact the performance of the SQL itself as it cannot get as much as and when needed.Because the script tend to limit the RAM used by SQl. Please advise on how to overcome  this problem.
0
Comment
Question by:chongbenkee
6 Comments
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 50 total points
ID: 24332791
well you are running three instances so obviously each instance going to eat your memory, if you decrease Max server memory, It may affect performance of your server, you should stop the services which you are not using like agent, browser etc.
0
 
LVL 17

Accepted Solution

by:
OriNetworks earned 150 total points
ID: 24332830
SQL by architecture will grab as much RAM as it can. If you limit the RAM available to it, you may or may not see a performance decrease depending upon how busy your server is and a few other factors of the databases themselves. If you have small or low volume databases, I wouldnt really worry about it.

In general, just try to distribute as much RAM to SQL as you can without effecting any other services that may be running on your server.
0
 

Author Comment

by:chongbenkee
ID: 24332944
If that is the case , how do I know the optimum RAM that I need to allocate for all those 3 ?  And why it uses so much of PF ?
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 24336271
>>how do I know the optimum RAM that I need to allocate for all those 3 ?<<
That comes from experience.

>>And why it uses so much of PF ?<<
Unless you limit the MAX memory then you allowed that to happen.
0
 
LVL 17

Assisted Solution

by:OriNetworks
OriNetworks earned 150 total points
ID: 24336898
adding to what aceperkins is saying
>>how do I know the optimum RAM that I need to allocate for all those 3 ?<<
It depends largely on knowing the performance of your database, types of queries, indexes, etc. However, that is a huge topic and you really only have to worry about it if you have large or high performance databases. Try setting a a few different ram limits and see what works best for you. You might be able to get away with 1024 or 2048 of ram for each instance(or maybe less). But again, that is a huge topic that entire books are written about.

>>And why it uses so much of PF ?<<
Pagefile usage is by default automatically adjusted in relation to how much RAM is being used. So if your ram is maxed out, of course its going to use that much.
0
 

Author Closing Comment

by:chongbenkee
ID: 31580036
thanks guys
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql Audit table 3 66
Truncate vs Delete 63 105
CREATE DATABASE ENCRYPTION KEY 1 63
Urgent help needed! Unable to to paste in query designer 29 43
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

786 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