?
Solved

SQL Server ate a lot of memory

Posted on 2009-05-07
6
Medium Priority
?
216 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
[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
6 Comments
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 150 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 450 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 150 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 450 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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 Backup & Restore 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.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

764 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