• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

Insufficent Virtual Memory errors

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
cjones_mcse
Asked:
cjones_mcse
  • 2
1 Solution
 
Leandro IaconoSenior Premier Field EngineerCommented:
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
 
cjones_mcseAuthor Commented:
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
 
Leandro IaconoSenior Premier Field EngineerCommented:
I am glad I could help out, and I hope the information I provided speeded up your SQL database ...

Cheers
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now