Page File location and Size

Question

Page File location and Size

BlueTiger

Ten Centuries

Points: 1396
More actions
September 8, 2011 at 3:23 pm

#243518

I found out there is a huge (33GB) pagefile on the database data drive on one SQL Server box.
The page file setting on this server is let OS itself to manage the size and location. I am trying to convince my manager to change the setting to configure a fixed size ( 2GB) and put it on C or other separate drive. My manager wants me to provide more information to support my suggestion.
So , could you tell me what is the pagefile settings in your environment and what is your suggestion?
Thank you.

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

http://sqlserverperformance.wordpress.com/2007/12/17/windows-page-file-explained/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

BlueTiger Ten Centuries Points: 1396 More actions · Answer 2

Thank you for the Link.

Our System Admin sent email to ask Microsoft, I can not believe their recommendation is still let OS manage the pagefile.

Our standard servers are 64bit, 32gb RAM, Windows 2008.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

General recommendation is to let the OS manage, but that's not necessary for SQL Server. SQL doesn't use the page file (well, if it does, you have problems already), so massive page files are not necessary.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 4

If the server is dedicated for SQL Server and if no other applications are running on it, then making the Page File too big won't help you much.

If you configure SQL Server to maximum memory and set Lock pages in Memory, SQL Server pages don't get paged out to the disk. In such scenarios, large page files don't help.

Generally the recommendation is to have a Page File of 2.5 times that of the Physical memory on the box. This makes sense only when you have other applications running in parallel to the SQL Server.

Thank You,

Best Regards,

SQLBuddy

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 5

sqlbuddy123 (9/11/2011)
If you configure SQL Server to maximum memory and set Lock pages in Memory...

I'm not sure if I'm reading that right so forgive me if I'm taking it the wrong way... The general recommendation is to never set SQL Server to use "maximum" memory... you always need to leave enough for the OS to operate.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 6

Jeff Moden (9/11/2011)
sqlbuddy123 (9/11/2011)
If you configure SQL Server to maximum memory and set Lock pages in Memory...
I'm not sure if I'm reading that right so forgive me if I'm taking it the wrong way... The general recommendation is to never set SQL Server to use "maximum" memory... you always need to leave enough for the OS to operate.

On dedicated SQL Server boxes, it is recommended to assign Max Memory to SQL Server Instance so that SQL Server can make maximum use of the memory. However, we need to leave 2-4 GB of memory to the Windows.

That would be enough for the Windows server as long as it doesn't run any applications other than the SQL Server.

Thank You,

Best Regards,

SQLBuddy

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 7

Again, I might be taking it the wrong way but I've never seen the recommendation to set SQL Server Max Memory to the same amount as there is RAM in the machine. I've always seen the recommendation to save a Gig or two (depending on the amount of RAM in the machine) for the OS even if the machine is dedicated to SQL Server.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 8

I agree with Jeff. I always though that setting max memory was bad practice! Could you please provide a link to a Microsoft article that supports what you said. I will be really interested in reading it.

My blog

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

You almost must set max server memory on a 64-bit server, with that left at default (2000 terabytes or so), SQL can and will take all the memory on the server and starve the OS. I've seen it happen, it's far from pretty.

What the max memory should be set to is a complex topic. There's no 'leave X for the OS' that works in all cases and anyone saying there is doesn't understand the complexity of the topic.

A good approach is to start cautiously, leave a relatively large amount of memory free (on larger servers I'll leave up to 16GB for the OS) and then monitor. The counter to watch is Available MBytes. That must not drop below 200-300 (rough figure). If your server consistently has a larger value for that, then it's probably safe to increase SQL's max memory a little. Repeat until you're happy.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 10

GilaMonster (9/12/2011)
You almost must set max server memory on a 64-bit server, with that left at default (2000 terabytes or so), SQL can and will take all the memory on the server and starve the OS. I've seen it happen, it's far from pretty.
What the max memory should be set to is a complex topic. There's no 'leave X for the OS' that works in all cases and anyone saying there is doesn't understand the complexity of the topic.
A good approach is to start cautiously, leave a relatively large amount of memory free (on larger servers I'll leave up to 16GB for the OS) and then monitor. The counter to watch is Available MBytes. That must not drop below 200-300 (rough figure). If your server consistently has a larger value for that, then it's probably safe to increase SQL's max memory a little. Repeat until you're happy.

Gail, Brent Ozar mentioned during his SQL Server Perfmon (Performance Monitor) Best Practices[/url] session that he would like to see 1GB on the Availble MBytes counter, due to the fact that some people may login to the SQL Server using RDP. What do you think about it. He said that if you are sure nobody uses RDP on the SQL Server then indeed yuo can give more memory to SQL.

My blog

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

As I said 'rough figure'

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 12

Ignacio A. Salom Rangel

SSC-Insane

Points: 20443

September 12, 2011 at 6:29 am

#1379930

Thanks for your reply!

My blog

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 13

Ignacio A. Salom Rangel (9/12/2011)
I agree with Jeff. I always though that setting max memory was bad practice! Could you please provide a link to a Microsoft article that supports what you said. I will be really interested in reading it.

Oh.. be careful, now... I didn't say that setting max memory was a bad practice... I said setting max memory to the max was a bad practice. You always have to leave some for the OS.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

BlueTiger Ten Centuries Points: 1396 More actions · Answer 14

I agreen that grant SQL SERVER Services account "Lock pages in memory" permission is a good practice.

They only work in some SQL Versions

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

Note For 64-bit editions of SQL Server, only SQL Server Enterprise Edition can use the Lock pages in memory user right. This is applicable for SQL Server 2005 [RTM, SP1, SP2, SP3] and for SQL Server 2008 [RTM and SP1]. SQL Server 2008 SP1 Cumulative Update 2 and SQL Server 2005 SP3 Cumulative Update 4 introduce support for SQL Server Standard editions to use the Lock pages in memory user right