0

I am trying to tune a SQL Server. Per Brent Ozar's Performance Tuning Video, he says the PerfMon's Paging File:%Usage should be zero or ridiculously close to it. The average metric on my box is around 1.341%

The box has 18 GB of RAM, the SQL Server is off, the Commit Charge Total is 1GB and yet the PerfMon metric is not 0. The Performance of the Task Manager states that PF Usage is 1.23GB.

What should I do to better tune the box?

2 Answers 2

3

You should really tune for PERFORMANCE, not for magic numbers.

Zero paging file (swap) utilization is a nice goal if you can attain it, but if you have sufficient RAM for all of SQL server's needs and you are not experiencing a performance problem you probably don't need to "tune" anything. Having some data sitting in the paging file which is never going to be asked for doesn't hurt performance.

If you want to concentrate on numbers though I would concentrate more on Memory\Available Bytes (make sure there's always a healthy amount available, where "healthy" is defined by your use case) and Memory\Pages/sec (which should be zero or close to it, indicating that what's sitting in swap is not being actively recalled to RAM).


Remember that modern operating systems will often evict data that has never been requested from RAM, placing it in the paging file (swap space) preemptively while the system is idle so that there is more RAM available and the system doesn't have to take the performance hit of shuffling stuff to disk if it needs that RAM when it's actually busy.

As an example, consider these stats from one of my (unix) DB servers:

CPU:  0.0% user,  0.0% nice,  0.0% system,  0.0% interrupt,  100% idle
Mem: 710M Active, 730M Inact, 554M Wired, 72M Cache, 315M Buf, 862M Free
Swap: 2048M Total, 910M Used, 1138M Free, 44% Inuse
  • I have "plenty" of free RAM (800MB, and nothing will ever need more than that).
  • I'm using a huge chunk of my swap space
    (because there's a program on this server with a memory leak that is mostly swapped out)
  • System performance is excellent, and all my users are happy.

So at this time I take no tuning actions (though I do kill and restart the program with the memory leak when swap utilization hits 50%).

1
  • +1 for "You should really tune for PERFORMANCE, not for magic numbers." - Baseline, baseline, baseline, tune, re-baseline. Jun 20, 2012 at 22:24
2

The Task Manager is wrong. It's actually displaying the system commit total. (Wording was changed in 2008 server to reflect that.)

As to 1.341% page file usage, that seems pretty ridiculously close to zero to me, but I'm not a DBA. It's "normal" for Windows to do some paging even if it has plenty of memory available - if that's something you're looking to rectify, maybe you should think about asking for pointers on DBA-orietned site/forum. Looks like there's some discussion around the issue on dba.stackexchange.com and sqlservercentral.com

I might recommend trying some benchmarking first to see if that 1.3% is actually impacting your server's performance, before putting a whole lot of effort into eliminating it. I'd be surprised if it makes a noticeable difference - I'd think your performance tuning efforts are probably better spent elsewhere, but like I said, I'm no DBA, so I might be wrong.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .