Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Limit RAM usage

Author  Topic 

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-01 : 10:59:32
Hi

I have a SQL Server 2005 SP3 that runs on a Win 2008 with 8GB RAM, The server use up almost all RAM there is on the server, can I limit the amount of RAM being used, and how do I do that. Are there any downsides of doing so, and what happends when it reach the limit?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-03-01 : 11:36:45
go to the box where the server is installed.
start management studio and connect to the server.
in object explorer right click the server.
choose properties and then you will find the way to change the ram settings.


Too old to Rock'n'Roll too young to die.
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-01 : 11:36:55
You can limit the amount of memory SQL Server would use (for the most part). Right-click on the server name in SSMS, properties, and memory tab. Change the maximum server memory in MB. It is in megabytes, so if you want to allow SQL Server to use 6GB, insert 6000 in there.

If you restart the server, it will take effect immediately. If not, the memory would still come down, but might take a while.

Some guidelines on how much to allocate is Rob's reply in this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=183360
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-03-01 : 12:50:27
You can also check for the memory settings using the command : sp_configure
Change with :
RECONFIGURE
GO
sp_configure 'max server memory', 64000
RECONFIGURE
GO


Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-02 : 02:30:25
Thanks for the information, is it also possible to limit a specific database ram usage, the server has only 8GB RAM and ther is one database that is 7GB, I suspect that database cause the server to act slugish
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-03-02 : 05:00:46
No , you can't limit it explicitly , but if you spend some time sizing the database , then you can decide on configurations which are suitable.
When you say the server is sluggish - how are you experiencing the sluggishness , is it a slow query?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-02 : 05:43:47
quote:
Originally posted by jackv

No , you can't limit it explicitly , but if you spend some time sizing the database , then you can decide on configurations which are suitable.
When you say the server is sluggish - how are you experiencing the sluggishness , is it a slow query?

Jack Vamvas
--------------------
http://www.sqlserver-dba.com




With slugish I mean the overall performance of the server. For example yesterday I looked at the resource monitor in windows, SQL server used up almost all of the servers total amount of RAM, query the server was not as fast as it should be, Sql Server manager didn't respond as quick as it should. And since there is one database which is 7GB large (it stores images) I figured that database is causing this and therefore also affect the other databases that is in the server (which doesn't store images and is just around up to 200Mb large)


What do you think?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-02 : 08:57:15
I don't think it is the size of the database that is causing the problem. A database, even if it is large, just sits there on the disk minding its own business, not bothering anyone. It is the queries that you run against it (or any database) that would cause the sluggish performance. How much impact it has depends on the size of the tables, size of the data involved in the DML operations etc.

Put another way, there are 3 types of resources for which there can be contention - I/O, CPU and RAM. The sluggish performance is because of contention for one or more of those. Contention for one can trigger another or manifest as contention for another.

All theory aside, I think what is happening to you is that SQL Server is grabbing all the RAM available, thus starving the Windows OS of memory. The way to fix it is to limit the amount of memory that SQL Server is allowed to use as I had described in my previous post.

Once you fix that, the performance may still be sluggish. You will need to investigate what else is causing the poor performance at that point.
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2013-03-02 : 10:34:38
How does the sql server operate, does it use the database in memory (RAM) or to filesystem? I'm just thinking the fact that the database store images, and a query to that database involving images in the database should cause an impact on the server, or..?
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2013-03-03 : 03:01:57
Do you have some specific figures indicating sluggish response? For example, do you have a benchmark figure from before and after?
When you set up the SQL Server Instance on the server , did you spend time profiling requirements and compariing to what is available?
One of the tasks , mentioned ny @James K - is ensure you quantify the resource requirments of the OS and any other apps on the server , such as AntiVirus , monitoring etc

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -