May 28, 2012 at 3:37 pm
Hi,
Do we need separate drive for Page file for SQL Server 2008 R2?
What is the best practice for Page file configuration in SQL server 2008 R2?
Thanks
May 28, 2012 at 3:43 pm
The page file is a windows host configuration best practice. Ideally you should size it appropriately and place it on a separate disk
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 28, 2012 at 3:51 pm
we do not have a separate drive for Page file on any sql server box from years.
Do we really need to have a separate drive for page file? Having separate drive for page file depends on what sql servers facts?
thanks
May 28, 2012 at 6:49 pm
No, you won't need to do this. It is typical practice, that if you have SQL Server that is running on a box that may have memory pressure and you aren't configuring your instance to stay within the physical memory bounds of the system, you would have to make a calculation for how large you want your system drive page file to be. Typically that's 1 to 2 times the amount of physical memory installed on the server. So if you have 16 GB of memory on the server, and you expect the heavy use of the page file, you should be looking at a 16 - 32 GB page file. You can configure multiple page files on other disk volumes if too large of a system drive page file is needed (i.e. you have 32 GB of RAM, and a 64 GB page file just isn't feasible).
These are just some guidelines. But spend more time and energy in paging out SQL Server operations as little as possible. Page faults are expensive.
May 29, 2012 at 12:28 am
madhu-686862 (5/28/2012)
Do we need separate drive for Page file for SQL Server 2008 R2? What is the best practice for Page file configuration in SQL server 2008 R2?
It rather depends on whether you are running 32-bit or 64-bit SQL Server, whether the hardware is dedicated to SQL Server or not, whether locked pages are in use or not, and whether you have a large amount of memory or not.
A recent discussion on the MVP mailing list came to the unanimous conclusion that 4GB was fine for large-memory systems.
Some references:
http://blogs.technet.com/b/janelewis/archive/2009/04/30/page-file-how-big-is-yours.aspx
http://blogs.technet.com/markrussinovich/archive/2008/11/17/3155406.aspx
http://support.microsoft.com/kb/889654
http://blogs.technet.com/b/askperf/archive/2008/01/08/understanding-crash-dump-files.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 29, 2012 at 2:14 am
The reason being that a properly tuned SQL Server should never be using the page file at all, so unless there are other things on the server that might page (or SQL is not properly configured), the only real usage of the page file is when Windows crashes, to store a memory dump. However a complete memory dump is very unusual and not that useful (plus it takes a huge amount of time to dump 64+GB of memory to a file)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 29, 2012 at 4:10 am
SQL Kiwi (5/29/2012)
A recent discussion on the MVP mailing list came to the unanimous conclusion that 4GB was fine for large-memory systems.
Were they Windows server MVPs?
GilaMonster (5/29/2012)
The reason being that a properly tuned SQL Server should never be using the page file at all
While this is true, if you have SQL Server sucking up all the physical memory on your server, other apps and the kernel are going to have to hit possibly hit the page file. The important thing is, it's not just about SQL Server.
Page file is designed to allow multiple processes to run where there wouldn't normally be enough memory resources. As Paul said locked pages can have a big influence. All NOS's have some sort of page file (Windows), swap file (Unix, ESX server).
Mark Russinovich's link is fully detailed.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
May 29, 2012 at 4:13 am
Perry Whittle (5/29/2012)
Were they Windows server MVPs?
No, SQL Server MVPs. The discussion was limited to properly configured x64 SQL Server on large memory systems (where 'properly configured' includes accounting for non-SQL Server memory requirements). The feedback was that 4GB has been used very successfully on a large number of real-world deployments. Mileage may vary, but the details are well explained in the reference links I gave previously.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 29, 2012 at 4:38 am
Perry Whittle (5/29/2012)
GilaMonster (5/29/2012)
The reason being that a properly tuned SQL Server should never be using the page file at allWhile this is true, if you have SQL Server sucking up all the physical memory on your server, other apps and the kernel are going to have to hit possibly hit the page file.
Which is why I said 'Properly tuned', which takes into account other things on the server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 30, 2012 at 3:43 am
Another link with good advice is http://blogs.technet.com/b/clinth/archive/2009/09/03/the-case-of-the-enormous-page-file.aspx from Clint Huffman, a Microsoft PFE.
I run my machine at home without a page file and have had no problems, but probably I am wasting about 150MB memory for the host and each active guest that could have been put in the pagefile.
If you want to allow your 128GB machine to take a full memory dump then allocate pagefile space big enough for this. Be aware that writing this amount of data to a single pagefile could take maybe 20 minutes depending on your disk subsystem. You should take a view on how long you can allow the dump process to run for, and create multiple page files across enough disks to meet your dump time target.
If you want the machine to automatically reboot and get back into service asap then you need to question if a full dump or a snap dump best meets your needs. Microsoft reckon they can solve most OS problems with a snap dump, and this can easily be emailed to them.
If you reckon you need the ability to take a full dump for any OS failure, then also work out how you will get it to Microsoft. You are probably talking about copying it to external media and using snail mail.
Having taken all the advice I have seen into account, my view is to always use a pagefile, and set it at a fixed size. I would use 4GB for a machine of up to 64GB memory, and probably 8GB for a 256GB machine.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply