Page File Usage

• Glenn Owens

  SSCrazy

  Points: 2099

  More actions

  March 13, 2009 at 8:09 am

  #129703

  I've got a scenario where ,during the normal business day, the 3 SQL instances on one of my VMWare SQL Servers all behave nicely. I've enabled Lock Pages in Memory on the 64-bit server and adjusted the max memory sizes (everything's good). Overnight, during our so-called maintenance window, a job runs which is expected to utilize the page file (which has been sized appropriately). The job lasts for approximately 6 minutes during which time the page file use shoots up (also OK).

  The "problem" is: the page file usage never goes back down on any of the 3 SQL instances until the SQL Server service has been restarted. Once the service(s) have been restarted page file usage goes back down to a nominal (below 1 page/sec ave). The odd thing is that the performance on the server remains good after the 3:30AM maintenance job completes (while the page file is still monitoring high use - we're using Idera's DM monitoring tool).

  My assumption is that the page file should decrease dynamically once it's not being utilized... so, why does the page file continue to monitor high usage until the SQL services are restarted?

  Thanks

  Glenn

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736150

  More actions

  March 13, 2009 at 9:16 am

  #959224

  It should go down. That sounds weird, like SQL Server is trying to hold most stuff in memory constantly, and it's exceeding limits so you're paging.

  Strange.