February 19, 2021 at 5:33 am
Hello everyone,
Our company is upgrading the hardware for a contract that we won to renew. The new contract allows us to upgrade all servers. Our current SQL cluster is a 2 nodes model HP DL380 G9 with 512 GB RAM, 3 local RAID 1 drives: OS (136 GB SAS drives), Paging (279 GB SRS drives), and tempDB (448 GB SAS drives). The SQL data files and logs files are on a Nimble SAN.
The new servers are DL360 G10 with 512 GB RAM and only 1 pair of 480 GB SSD drives for RAID 1. They are still in the boxes so probably the usable space of the SSD drives is only 448 GB after the OS is installed.
I plan to create 3 partitions on the 448 GB SS drive on the new servers with 120 GB for the OS, 120 GB for paging, and 208 GB for tempDB. However the rule for sizing the paging file = RAM x 1.5 is no longer correct with newer OS as I researched. On the current SQL servers the page file set at 250 GB.
Thanks in advance.
February 19, 2021 at 9:42 am
Also curious for other opinions
Since you only have one pair of drives I would keep it on a single partition with you can extend when needed.
For the pagefile I would go with the size Windows recommends when it started up. Got a recommendation of 10 GB on a 64 GB server
February 19, 2021 at 6:46 pm
Jo,
As I researched, SQL locks pages in memory so with SQL max memory set at 480 GB, it does not make sense to create a 480 x 1.5 = 720 GB page file. Our current server has a dedicated local SAS drive for paging. The page file is set fixed at 250 GB but the "recommended" size is 69 GB. I am not sure how the OS recommended this number.
I think I will start 1 partition for all and set a page file fixed at 50 GB. I still want to hear more about sizing the page file for SQL server because there are still opinions supporting large page file = RAM x 1.5 and others against it.
Thanks,
February 19, 2021 at 7:25 pm
Jo,
As I researched, SQL locks pages in memory so with SQL max memory set at 480 GB,
I thought SQL only locked SQL pages in memory if the max and min specified the same amount of RAM. That is, if the min was, say, 120 and the max 480, I would not expect SQL to lock the pages.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 19, 2021 at 7:44 pm
Oops, seems not to be the case. You just need to set max mem. Maybe I'm remembering from an earlier version of SQL?!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 22, 2021 at 4:08 pm
I wouldn't activate lock pages in memory SQL Server Max Memory Setting with Lock Pages in Memory (mssqltips.com)
The pagefile is rather for unexpected events, otherwise it has too few memory. My server has 10 GB in case soms SSIS job / other program would spike some memory. The 1.5 rules are from the old times when you had 2 GB of ram
Our servers are virtualized and have the OS-partition in a seperate file (hyper-v vhdx) so the data/tempdb volume can be expanded online. The reason to start small is if the server/file has to move, it is still small. It is easier to enlarge a file than shrink it
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply