Virtual memory configuration | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Virtual memory configuration

Hi !
I plan to increase virtual memory. Currently on dedicated to sql server 2000 box:
min virtual memory =2gb
max virtual memory =4gb free space=6.7gb I plan to increase max virtual memory to 6 gb But read a few article saying that instead of improving performance in some case it
can make things worse. Did any body had a problem after increasing virtual memory?
What exactly problem were those?
I think part of this is covered under your old thread and until unless you monitor the counters using PERFMON for Process: Virtual Bytes its not suggested to increated the VM.
Satya SKJ

i just checked the performence monitor:
process: virtual bytes total=7 198 000 000 bytes what is threshold?
The virtual memory size to at least 3 times the physical memory installed in the computer.
So what is the total memory available and how about setting to SQL Server? Where did you see the threshold? _________
Satya SKJ

I have 3 drives and paging file size for each of them is: 1.c: min=2 gb
max=4 gb
free space=6.7 gb
2.e(keeps mdf files)
initial and max is not set
free space=93 889 mb
3.d(keeps ldf files)
initial and max is not set
free space=16 155 mb
Any performance issues with current VM settings? _________
Satya SKJ

well after installing thirt party software for snapshoot bakups
i keep getting error messages like : Insufficient memory available SQL Server could not spawn process_loginread thread Failed to reserve contiguous memory module (Uknown 00000000)
I believe its old pending issue at your end, and I believe SQL is with no SP.
Either add-up more memory or avoid using third party backup tool.
One day this would cause server crash due to mismatch of memory module. _________
Satya SKJ

yes and i don’t know what to do whith that. if let’s say i stil decide to increase virtual memory.
should i increase it just for drive c
why rest drives do not have specified initial and max memory for rest of the drives
I think you should remove the page file from rest of the drives as they are going to kill the I/O for MDF and LDF files. Keep the page file on C: only and keep it 1.5-3 times the memory size. One more thing, SQL Server does not actively used Virtual Mamory unless it is running short of memory. See where the memory is being consumed. It may be the case of some other application running on the same box consuming all the memory. Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

Maybe you right but this article suggest the opposite:
create few pagefile.sys

I’m with Gaurav on this… A dedicated SQL box will not use a substantial amount of virtual memory since SQL configures itself to leave some physical memory free. If the C: drive is the only drive not used for SQL data/logs then I’d leave the paging file there… although I do sometimes put it on the data drive since this is hardly ever written to or read from anyway, due to caching and sufficient physical RAM… However there can always be memory leaks… In this case however it is not a dedicated SQL box since you’ve said that you have third party software running on it. I’d use perfmon, to track the memory usage of every process on the box over the period of say a week or two (from a clean boot). Look for any steadily climbing values in memory consumption, and target that process as the offending process Cheers
Thank you .I started monitoring .
Task Manager shows virtual memory usage by SQL Server 1 888 500 K

So you have 2GB of physical memory in the box then? Virtual Memory usage reported by Task Manager is the amount of memory used by each application in the application’s virtual memory address space. It is not to say that all of that is in the page file (the OS virtual memory store) Cheers
Regarding 1rst post: I find, in one customer, the following situation. Virtual Memory on 1 physical drive RAID 5: 1Gbyte.
I monitor Paging file: %Use, and show about 95%, constant.
Next I made another page file in 2 Physical Drive, RAID 10, 1Gbyte more. Monitor show: %use in Drive 1: 95%, in Drive 2: 2%. Next I reduce virtual memory on drive 1, from 1Gbyte to 2Mby, minimum posible. (I supose more perfomance on RAID 10 than RAID 5)
So configuration was: 2Mb in Drive 1 and 1Gbyte in Drive 2. Monitor Show: %use in Drive 1: 90% in Drive 2: 2%
So I don’t understand nothing. (Of course reboot each time, etc) Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell