Solved

SQL Server ate a lot of memory

Posted on 2009-05-07
6
213 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to check data in sql table 11 51
T-SQL to Update Table Dynamically 2 56
How to simplify my SQL statement? 14 55
Sql Server group by 10 44
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

837 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