Solved

SQL Server ate a lot of memory

Posted on 2009-05-07
6
211 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql calculate reminders 11 70
How to replace WHITESPACE (1 or more spaces) in a field with a SINGLE DASH? 4 30
SQL Agent Timeout 5 48
CROSS APPLY 4 45
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

895 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now