RAM usage

Dobromir Dimitrov

Hello. What I'd like to ask is if there is a way to configure SQL Server 2005 to use only a certain amount of RAM. I have a machine with 4GB RAM, and there are a lot of apps running on it. It is when SQL Server starts executing a query over a relatively large database (e.g. 20 - 30 GB)that things go bad for all other apps - SQL Server uses almost all of the available RAM, leaving only a very limited amount for all other applications to use. Any suggestions?

GuyThiebaut

Those are fairly large databases and depending on your queries you could be hitting the processor heavily - as you are discovering. The only solution I can think of is to remove all those extra applications. Good practise dictates that your SQL Server is a machine that is primarily a dedicated server i.e you have no other applications on it (other than an email client, backup application etc) If you are using databases of the size you mention I suggest you get another machine on which to run the other applications. Some more tips: Keep your databases and transaction logs on separate disks. Make sure that your applications are not on either of these disks. I know this doesn't answer you question directly...

You always pass failure on the way to success.

Ali Rashid

You cannot explicitly set the amount of RAM that the system should use (as far as my little knowledge serves me). You can however, set the priority of the SQL Server to low as compared to other applications (Press ALT+CTRL+DEL > Click task Manager > Applications (TAB) > Select Image Name sqlmangr.exe > Right click on it and select option: set priority). That would mean that you would have more processing preference for other applications. But remember, with the size of the databases you are talking about, you would still have issues with the processor getting hit hard. If you cannot redice the DB size, then increase the RAM, much better get dual processors and set one processor to solely work on SQL Server. This is what a Hardware guy can set for you. Regards.

SimulationofSai

Open up Management Studio, connect to your server. In Object Explorer, right click on the Server Root and select "Properties". Under the "Memory" page, enter a value in the "Maximum Server Memory" textbox. The change will take effect when SQL Server is under lesser load and can shed some memory or when you restart the service.

SG Cause is effect concealed. Effect is cause revealed.