April 13, 2008 at 10:56 am
Hi All
Can someone confirm that I have the right information for correctly setting up my new SQL 2005 server. OS is Windows 2003 Enterprise edition with SP4. We are installing 64GB RAM on the box and from various sources these are the settings I was planning on making:
Boot.ini
remove the /3GB
ensure the /PAE is there (should be with SP4)
Network Connections
Maximize data throughput for network applications - OFF*
Database engine service account
Lock Pages in Memory enabled
AWE enabled - ON
Min memory=0
Max memory = 62GB (leaving 2GB for kernel)
Is this right and are there any other settings I should be aware of for this amount of RAM?
*With the "Maximize data throughput for network applications" option, what is the best of the other 3 options to select BOL doesn't make any suggestions, just says choose one of the other options!
Thanks
April 14, 2008 at 2:36 am
If the only thing running on your box is the OS and SQL server, than setting max memory to 62 GB might just work, sometimes.
If you run anything else (anti-virus, MOSS or other monitoring tools, SSIS, backups, etc) then you need to restrict SQL further. If you have AWE set on, then at startup SQL tries once only to get the max memory value. If that memory is not available in a single fragment, the AWE request fails and SQL runs without any AWE memory, using a maximum of 1.7 GB.
The obviouis question to ask as you are using a 64-GB box, is why are you not using 64-bit OS and 64-bit SQL. Memory management would be far more efficient and SQL would perform better if it was running 64-bit.
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
April 14, 2008 at 3:11 am
EdVassie (4/14/2008)
The obviouis question to ask as you are using a 64-GB box, is why are you not using 64-bit OS and 64-bit SQL. Memory management would be far more efficient and SQL would perform better if it was running 64-bit.
Funny you should mention this, I was only discussing this with my network/purchasing manager (AKA "Man who spends money like a woman") this morning. The design is very much still on paper at the moment so we are able to make changes to the architecture.
Are their any peculiarities or "special features" I need to be aware of with the 64-bit SQL version or OS architecture?
April 14, 2008 at 3:40 am
There are a few threads on this forum that have focussed on the differences between 32-bit and 64-bit SQL Server 2005. Most people would not hit any issues, but some differences are very important for some sites. It is best to look at the relevant threads, and if you have queries on anything raised then add a post to the relevant topic.
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
April 14, 2008 at 8:00 am
1) I wasn't aware there was a SP4 for Win2k3
2) You should ABSOLUTELY, WITHOUT QUESTION purchase a 64 bit machine and install 64bit OS and SQL Server. There are many reasons both obvious and esoteric. Just push the "I Believe You" button on this one.
3) 64bit code would mean no AWE and no PAE and no /3GB. Lock Pages in Memory should be on and you should be using Enterprise Editition of SQL Server and OS.
4) As, if not more, important to the server config is your I/O subsystem. You best put some serious effort into purchase/configuration here or you will be pissing money away on your spiffy server. 🙂 I see this ALL the time, and it is a damn shame.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 14, 2008 at 8:15 am
Now I have a question about the 64 bit server. Is there not a affinity mask and affinity mask i/o setting you must adjust?
Thanks
Rudy
April 14, 2008 at 8:22 am
They exist, but you do not have to adjust them. Changes to these are only needed if you have specific requirements that could be met by using affinity masks.
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
April 14, 2008 at 8:24 am
Thanks for the info!
**** This has got to be the best place for any DBA !!! ****
Rudy
April 14, 2008 at 9:23 am
A few things I missed:
1) 2GB is definitely not enough ram for all things non-sql server-buffer memory. I would leave at least 6GB, and watch for paging to see if that is not enough.
2) This server could well be numa-enabled. There is a LOT of mess that comes into play with that. Deep stuff.
3) Avoid affinity settings unless you REALLY know what you are doing. And then you are as likely as not to shoot yourself in the butt. 😀
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 14, 2008 at 10:22 am
TheSQLGuru (4/14/2008)1) I wasn't aware there was a SP4 for Win2k3
... I was time travelling. Make that SP2!!
3) 64bit code would mean no AWE and no PAE and no /3GB. Lock Pages in Memory should be on and you should be using Enterprise Editition of SQL Server and OS.
No problem on the W2K3 Enterprise, but I don't have the budget for Enterprise Edition SQL. We are running websites against this db so we need CPU licensing, £8200 vs £32,000 for 2-CPU server. Is there a problem running Standard Edition on 64bit architecture?
4) As, if not more, important to the server config is your I/O subsystem. You best put some serious effort into purchase/configuration here or you will be pissing money away on your spiffy server. 🙂 I see this ALL the time, and it is a damn shame.
Absolutely. We have a stack of fast SAS drives that will be configured into multiple RAID arrays so the data files, trans logs, distribution db, temp db etc. etc. can be split out for maximum performance.
April 14, 2008 at 10:48 am
TheSQLGuru (4/14/2008)
2) This server could well be numa-enabled. There is a LOT of mess that comes into play with that. Deep stuff.
Geeeez.
Can you recommend any white papers or other further reading on the hardware setup so we make sure we don't have the server built wrong. Our hosting provider have their own security-hardened build for the server and OS so we need to make them aware of config requirements prior to the server going into build their end.
April 14, 2008 at 11:24 am
1) 2 cpus (unless they are dual or quad core (NOT Hyperthreaded)) is a bit light to process that much RAM.
2) For I/O, there are some documents I recommend, but note that NOTHING will take the place of experience with high-end I/O configuration.
SQL2005_PhysicalDBStorageDesign.doc, SQL2005_WorkingWithTempDB.doc, SQLIOBasicsCh2_SQL2005.doc, Troubleshooting Performance Problems in SQL Server 2005.htm, Troubleshooting Storage Area Network.doc, Predeployment I-O Best Practices SQL Server Best Practices Article.htm
All of those documents are to be found on microsoft.com.
Enjoy the new box!! Sounds like fun. 😎
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 14, 2008 at 1:03 pm
TheSQLGuru (4/14/2008)
1) 2 cpus (unless they are dual or quad core (NOT Hyperthreaded)) is a bit light to process that much RAM.
Yep, we are going with 2.4Ghz quad core Xeon's
2) For I/O, there are some documents I recommend, but note that NOTHING will take the place of experience with high-end I/O configuration.
SQL2005_PhysicalDBStorageDesign.doc, SQL2005_WorkingWithTempDB.doc, SQLIOBasicsCh2_SQL2005.doc, Troubleshooting Performance Problems in SQL Server 2005.htm, Troubleshooting Storage Area Network.doc, Predeployment I-O Best Practices SQL Server Best Practices Article.htm
All of those documents are to be found on microsoft.com.
Thanks, I'll look these out.
April 17, 2008 at 2:59 pm
Also, make sure you get a RAID Controller with Write Cache.
April 17, 2008 at 3:02 pm
Do they even make RAID controllers WITHOUT write cache?? :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply