[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Server ate a lot of memory

Posted on 2009-05-07
6
Medium Priority
?
217 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
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.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

650 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