SQL Server Performance Forum – Threads Archive
Placement of Windows Paging File
Hi. Thanks for this great forum. Im learning a lot here!<br /><br />My server with 1GB RAM is configured with a paging file of C: (500M), F: (400MB), G: (1200MB). F: and G: are both part of a striped RAID array, and also host the databases and logs.<br /><br />Performance monitor shows heavy paging file usage (yet buffer cache hit ratio is at ~100%). I wonder what this suggests ?<br /><br />Also, Im thinking as the disk IO queue length is typically relatively high, a performance gain could be had by moving the entire swap file onto C: drive, under the impression that the swap file on F: and G: are competing for IO time.<br /><br />Would this be a sensible idea to try out?<br /><br />Why is there lots of paging going on when SQL Server has a high buffer cache hit ratio? If SQL server has 1GB RAM to play with, how much of that is used to service requests, and how much is used for the actual cache that ‘buffer cache hit ratio’ is reporting on? <br />I was told earlier that if buffer hit ratio is at 100%, the server would not benefit from more physical memory, yet instinct tells me that due to the amount of paging, it possibly could.<br /><br />So many questions…… <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />Thanks!<br /><br /><br /><br />
> under the impression that the swap file on F: and G: are competing for IO time. Just to clarify, I mean competing for IO time with the actual database and logs.
If you find that the paging file#%92s usage approaches 100 percent at any given time (anything over 85 percent), increase the file#%92s size to better accommodate the memory usage on that system. This is done using the Change button located in the Virtual Memory section of the System Control Panel#%92s Performance tab. Another tool you can use to monitor the paging file is the Windows NT Diagnostics utility (WINMSD.EXE). In addition to its other useful statistics, NT Diagnostics contains a Memory section with statistics on the paging file. Satya SKJ
Generally speaking, a properly tuned dedicated, SQL Server wil page very little. If paging does occur at a high rate (ove 20 pages a second on a consistent basis), this could be because the operating system doesn’t have enough available RAM, or other applications on the server are using RAM. Sure, there will be spikes, but that is normal. Ideally, a page file should be located on a drive of its own. Personally, I place the page file on the save drive as ths OS and SQL code, and generally speaking, these files don’t experience a huge amount o I/O activity, as compared to database or log files.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
if your buffer cache hit ratio is essentially 100%, then all the data being accessed is residing in memory
SQL Server should not be causing paging, as SQL will release memory to avoid paging
What indicator are you referring to by paging ?
Physical Diskxx or Memorypages/sec If i had to guess, i would say that you are doing large write operations, which would cause high disk queue depth, but this is not paging
]]>