| Author |
Topic |
|
magmo
Aged Yak Warrior
558 Posts |
 Posted - 2013-03-01 : 10:59:32
|
| HiI 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. |
 |
|
|
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 |
|
|
|
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 :RECONFIGUREGOsp_configure 'max server memory', 64000RECONFIGUREGOJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
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 |
|
|
|
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 |
|
|
|
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? |
|
|
|
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. |
|
|
|
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..? |
|
|
|
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 etcJack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
Limit RAM usage