SQL Server Performance Forum – Threads Archive

Virtual memory/page file

I have question about Virtual memory/page file where my C: Drive is 4GB & my D

rive is 40GB where my Database(SQL 2000) is. my Virtual memory/page file location is on C: Drive it is ok to move to D: drive & if i can move then will it have any kind of performance issue as my database operation reside on D; Drive. any feedback will be appreciated.

The OSs try to keep everything in RAM, but when everything doesn’t fit, the OSs swap the least recently used memory pages to the hard disk, storing them in the paging file. When a user requires the swapped pages, the OSs load the swapped pages back into RAM and swap other pages to make room for the pages the user needs. Therefore, where you put the paging file is important and affects performance. To tune your paging file, check the Control Panel System applet’s virtual memory settings. the current size of your paging file is larger than its initial size, it’s probably fragmented. Win2K’s built-in defragmenter won’t defragment the paging file but will defragment free space. To use Win2K’s defragmenter to defragment your paging file, find a partition with enough space to hold the paging file, defragment the partition, and move the paging file to the defragmented partition. You can then defragment the original partition. Alternatively, you can use Executive Software’s Diskeeper to defragment your paging files. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya : thanks for your reply, but in my sql server i have only C & D drive so to have another partition is not possible & we don’t wan’t to install addidtional hard disk. but still i didn’t get to understand that as my Database Data is on D;Drive & as i moved page file/Virtual to D: drive would like to know the impact & performance issue.

quote:Originally posted by satya The OSs try to keep everything in RAM, but when everything doesn’t fit, the OSs swap the least recently used memory pages to the hard disk, storing them in the paging file. When a user requires the swapped pages, the OSs load the swapped pages back into RAM and swap other pages to make room for the pages the user needs. Therefore, where you put the paging file is important and affects performance. To tune your paging file, check the Control Panel System applet’s virtual memory settings. the current size of your paging file is larger than its initial size, it’s probably fragmented. Win2K’s built-in defragmenter won’t defragment the paging file but will defragment free space. To use Win2K’s defragmenter to defragment your paging file, find a partition with enough space to hold the paging file, defragment the partition, and move the paging file to the defragmented partition. You can then defragment the original partition. Alternatively, you can use Executive Software’s Diskeeper to defragment your paging files. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Pull up performance monitor and look at the memory counters. Monitor them for a couple days. See if the page file is even being used. If not, then it really doesn’t matter where you put it. If you are using it though, it could really badley affect the database performance by having it on D:. Also, look at what the disk queue length and IO counters are like on the server. This is one of those "well, it depends" answers. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

True, until unless you monitor the performance counters it is hard to believe or see if any performance is affected. There are only two Paging File counters that you can track: % usage and % usage peak. Both counters indicate how heavily the page file is being used. These counters can help you optimize the size of your page file. One performance enhancement idea is to set the page file initial size as close to its average size as possible. For example, if your page file is set to a minimum of 50 MB and a maximum of 200 MB, but it normally grows to 100 MB, the optimal initial size of the page file should be 100 MB. The Process object and Page File Bytes counter can tell you how large the page file is at a given time.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thank you very much for all answers & feedback.

]]>