October 2, 2003 at 12:58 pm
We have a sql server 7.0 which has 2GB memory. But it uses up over 1.8 GB memory and leaves about 100 to 108 MB memory available. It is set up as dynamic.
I ran trace and it shows if a query is run it uses up memory. Looks like everytime a query is run it adds to the memory and does not release the memory.
I am thinking to set sql memory as fixed where allocating 1.5 GB to sql server and 500MB to OS.
The question I have that what will happen if sql server uses up all 1.5GB?
In dynamic set up it can adjust the memory by taking from OS or giving back to OS.
October 2, 2003 at 1:07 pm
quote:
The question I have that what will happen if sql server uses up all 1.5GB?
If SQL Server needs more memory, System will start paging and SQL Server performance will be decreased.
October 2, 2003 at 1:34 pm
SQL Server will sequester memory as needed and not relinquish it unless the OS attempts to allocate memory to other applications, so you best use the dynamic setting.
If you fix the maximum size to less than it is dynamically allocating, you will leave memory unutilized and SQL Server will have less cache and therefore less performance. If other applications do need more memory, SQL Server will not relinquish memory below the maximum size and those applications will be forced to use virtual memory.
--Jonathan
--Jonathan
October 3, 2003 at 4:53 am
I had posted a question on this a short while ago ( http://www.sqlservercentral.com/forum/topic.asp?TOPIC_ID=16009&FORUM_ID=65&CAT_ID=1&Topic_Title=Max+Memory+recommendation%3F&Forum_Title=Performance+Tuning ). I had seen a reference to an "official Microsoft recommendation" in Great Plains Installation eCourse to reduce the max memory by 10-15% for servers dedicated to running SQL Server. Someone else had posted another similar recommendation from the Microsoft Technet site itself.
Mark
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply