SQL Server Performance

Placement of Windows Paging File

Discussion in 'Performance Tuning for Hardware Configurations' started by Chappy, Nov 18, 2002.

  1. Chappy New Member

    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 />
  2. Chappy New Member

    > 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.
  3. satya Moderator

    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
  4. bradmcgehee New Member

    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
  5. joechang New Member

    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

Share This Page