4

We have a database server that we're upgrading to 64GB of RAM, but currently it only has small SCSI drives - totaling just over 400GB (192GB after mirroring). Our database is relatively small, but everyone tells us that the pagefile should be 1-1.5X RAM. We need to set some type of max size given our small disk size, but it seems silly to go buy $500 in SCSI disks just for page file storage.

Andrew
  • 238
  • 1
  • 2
  • 5

4 Answers4

7

The 1.5 times physical RAM is just a guideline. There are some general pointers about page file sizing in this Technet article which makes the point:

On server systems, a common objective is to have enough RAM so that there is never a shortage and the pagefile is essentially, not used. On these systems, having a really large pagefile may serve no useful purpose.

However for some systems (Domain Controllers, Exchange Servers) totally disabling page files is not a good idea. It's specifically contra-indicated for DC's and it's a very bad idea for Exchange Servers. I've seen the Exchange behaviour described in that article (extreme disk thrashing caused by paging) on an E2K7 Server that wasn't all that busy with 32G of physical RAM where someone set the pagefile size to 1G.

I've never found (or heard of) any specific statements that indicate a paging file is necessary for SQL, apart from the general argument that it helps if something else goes rogue and chews up all physical RAM.

Helvick
  • 19,579
  • 4
  • 37
  • 55
  • +1; that's what I was just going to say! – Maximus Minimus Feb 07 '10 at 23:44
  • Its worth remembering that almost any process on the box could have some of its memory flagged for writing to the page file, not just mssql. – Sam Feb 09 '10 at 00:15
  • That is true - I'd generally avoid getting rid of the page file entirely but creating a 48GB (or 32.01G)page file on a machine with 32G of RAM seems to be like using a sledghammer to crack a nut (unless you have a good reason for it). – Helvick Feb 09 '10 at 00:33
2

I wouldn't worry about it myself, on our physical MSSQL boxes we specifically turn paging off and some our ours run for quarters at a time without input. That said I'd still be tempted to let Windows manage its own page file, keep an eye on it and it'll tell you how big it needs to be.

Chopper3
  • 100,240
  • 9
  • 106
  • 238
2

The whole goal of properly spec'ing out a dbase server is to provide enough RAM for the entire database/s. The last thing you want are SQL queries causing disk I/O (swapping). Find the MSSQL database folders and check your database/s total disk usage. You should have at least double that amount of RAM in the system - in an ideal world. That leaves enough RAM for dbase growth as well as enough for query caching and the like.

So to answer your question, swap can be 1.5 times memory for systems with low memory and usually be 8GB or less for systems with large amounts of memory. If you start using paging don't increase swap..increase your RAM.

CarpeNoctem
  • 2,397
  • 4
  • 23
  • 32
0

I'd recommend starting with something of decent size, but small comparative to your physical memory. On a 32gb box, 8gb page file is a good start. Once you test for a while, you can move that number around if you like. Make sure to give SQLserver the right to "lock pages in memory" so that the database server itself cannot be paged.

For a great article on memory sizing, please see:

http://support.microsoft.com/kb/889654

Dave P
  • 396
  • 1
  • 4