?
Solved

Insufficent Virtual Memory errors

Posted on 2005-03-17
3
Medium Priority
?
165 Views
Last Modified: 2010-08-05
We have a Windows 2000 server with 6 GB of RAM functioning solely as a SQL server for a Sharepoint Portal (2003). As the number of users of the Sharepoint site rose over 500, we began to get insufficient virtual memory errors. I find this hard to believe with 6GB of physical RAM at the system's disposal. The total number of users for the Sharepoint site is now at about 900, but only about 50 to 75 percent of those actively use the site. The pagefile was only set to 2 GB, but still, with 6 GB of physical RAM, that shouldn't be much of an issue, should it? I've upped the pagefile size by 3 GB and placed the extra on a second drive. We are beginning to get similar errors on the Sharepoint server itself, but it only has 2GB of physical RAM and a 2GB pagefile. I plan on increasing that to 3 GB and spreading it across the system's two logical drives. Is there anything else I should do or can check especially on the SQL server?
0
Comment
Question by:cjones_mcse
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 8

Accepted Solution

by:
Leandro Iacono earned 400 total points
ID: 13571233
Well by default microsoft recomends setting your Pagefile to 1.5 time the size of your total Memory ...

This would mean that if you have a total of 6GB Ram you should set your pagefile to 9GB.

However as stated by microsoft, when using SQL, and depending on how your SQL server is set up, you might even have to set your pagefile to another amount.

For example when using Full-Text Search service you should set it to the page file to equal 3 times the amount of physical RAM in the server.

"The virtual memory (PAGEFILE.SYS file) setting for your operating system should be set to an amount equal to 3 times the amount of physical RAM in the server. If you have a non-dedicated SQL Server (a server running applications in addition to SQL Server) then you will want to add the virtual memory needs of these other applications to the amount calculated above."
 
Yet another setting for Full-Text Search service:

"The SQL Server MAX SERVER MEMORY setting should be set manually (dynamic memory allocation is turned off) so that enough virtual memory is left for the Full-Text Search service to run. To achieve this, select a MAX SERVER MEMORY setting that once set, leaves enough virtual memory so that the Full-Text Search service is able to access an amount of virtual memory equal to 1.5 times the amount of physical RAM in the server. This will take some trial and error to achieve this setting. "


Here is a SQL Server "How to Perform a SQL Server Performance Audit":
http://www.devarticles.com/c/a/SQL-Server/How-to-Perform-a-SQL-Server-Performance-Audit/3/

To optimize SQL performance you could try setting your pagefile on a seperate drive from where your OS is running. That way I/O speed is much greater becuase you dispose of to peices of hardware that can look for 2 things at the same time instead of just one looking for those same 2 things at a time ...

A Preformance Checklist can be found here: http://www.windowsdevcenter.com/pub/a/windows/2004/04/27/pagefile.html

What can I say mate. It really depends on your SQL Installation. What for sure is that you should modify your current Pagefile Size. You could see greater preformance improvments...

Sorry for posting so many link on this. But you got alot of configurations you can use as to optimize SQL. It's a whole other world mate.
0
 
LVL 10

Author Comment

by:cjones_mcse
ID: 13597069
Sorry this took so long. Thanks for the all the useful information! Adding 50 points for making you wait and for you giving me so much.
0
 
LVL 8

Expert Comment

by:Leandro Iacono
ID: 13597619
I am glad I could help out, and I hope the information I provided speeded up your SQL database ...

Cheers
0

Featured Post

Introducing Priority Question

Increase expert visibility of your issues by participating in Priority Question, our latest feature for Premium and Team Account holders. Adjust the priority of your question to get emergent issues in front of subject-matter experts for help when you need it most.

Question has a verified solution.

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

NTFS file system has been developed by Microsoft that is widely used by Windows NT operating system and its advanced versions. It is the mostly used over FAT file system as it provides superior features like reliability, security, storage, efficienc…
The Summer 2017 Scholarship Winners have been announced!
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

771 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