Page File location and Size

Question

Page File location and Size

Viewing 10 posts - 16 through 24 (of 24 total)

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

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

I never meant to set Max Memory to be equal to the total memory on the box. You need to set something like Max Memory = (Total Server Memory - (2to 4 GB))

Thank You,

Best Regards,

SQLBuddy

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

Jeff Moden (9/12/2011)
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, I know you did not say it. I did say that I always though that setting max memory for SQL Server was bad practice (I did not mention any sources 🙂 ). Here are a few links from where I got the idea that leaving the default value for max memory was bad practice on 64bit systems:

http://www.simple-talk.com/sql/database-administration/brads-sure-dba-checklist/

http://qa.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx

http://www.sqlbadpractices.com/keeping-maximum-server-memory-default-value/

So don't worry Jeff! 🙂

My blog

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

sqlbuddy123 (9/12/2011)
I never meant to set Max Memory to be equal to the total memory on the box. You need to set something like Max Memory = (Total Server Memory - (2to 4 GB))
Thank You,
Best Regards,
SQLBuddy

Thanks for clarifying!

My blog

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

sqlbuddy123 (9/12/2011)
I never meant to set Max Memory to be equal to the total memory on the box. You need to set something like Max Memory = (Total Server Memory - (2to 4 )

That's fine for smaller servers, it's not a safe recommendation for larger ones (16GB+)

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

prithvi.m Mr or Mrs. 500 Points: 561 More actions · Answer 5

That's fine for smaller servers, it's not a safe recommendation for larger ones (16GB+)

Exactly. Incase if you have VM's on your box, I would suggest atleast 6-7 GB buffer for OS to utilize. Had a very bad experience recently where SQL gobbled up all space in memory during a complex job operation that we had to manually reboot the box. Have heard suggestions to utilize parallelism in this case. Havent got a chance to validate it though.

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

SQL Server will try to use all the memory (64 bit) if you don't limit its usage by using Max Memory.

Good practice on dedicated SQL Servers is to set Max Memory and Lock it after leaving enough Memory for the OS.

Thank You,

Best Regards,

SQLBuddy

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

sqlbuddy123 (9/13/2011)
SQL Server will try to use all the memory (64 bit) if you don't limit its usage by using Max Memory.
Good practice on dedicated SQL Servers is to set Max Memory and Lock it after leaving enough Memory for the OS.
Thank You,
Best Regards,
SQLBuddy

I though you were advising to leave the default vaue for max memory, that I consider bad practice for 64bit systems and 32 bit systems with more than 4 GB.

My blog

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

It's not a good practice to leave the default value for Maximum memory.

I think the term Maximum Memory has to be understood clearly. There's a lot of ambiguity.

Thank You,

Best Regards,

SQLBuddy

charcol SSC Veteran Points: 296 More actions · Answer 9

Re-sizing the page file always depends on the version of SQL Server and other services that may be running on the box.

Example:
If you're running SSIS packages on the SQL Server, Lookup Transformation Tasks have been known to utilize pagefile.sys for caching lookup data. This does not include the Cached Transformation Lookup task which will use available RAM, obviously the more favorable of the two tasks but could still end up thrashing / using pagefile.sys under obvious circumstances (if you run out of RAM outside of max mem setting for sqlserver.exe).

We downsized the page file on one of our 2008R2 instances and SSIS jobs started failing. Packages continued to fail, even after implementing the recommended fix, which is setting the alternate cache paths via package variables "BufferTempStoragePath" and "BLOBTempStoragePath". After the change was implemented, packages continued to fail even after confirming the new paths set in vars were used for cache points. We had to set pagefile.sys back to a substantial size, in our case 200 GB, in order for packages to move past Lookup Transformations.

Long story short.
It always depends. proceed w/ caution and always test.

Perry Whittle SSC Guru Points: 233792 More actions · Answer 10

The page file is used for so much, but primarily is the vehicle for kernel dumps during a Windows Server issue.
The page file should be on separate volume to boot volume and any application data volumes.
Separate volume is recommended, this link details page file required for versions of Windows Server

https://support.microsoft.com/en-us/help/2860880/how-to-determine-the-appropriate-page-file-size-for-64-bit-versions-of-windows

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉