Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
irxn
Starting Member
13 Posts |
 Posted - 2011-11-09 : 08:26:50
|
| Hi,I want to know what are the best settings for my SQL server.The Server has 4 Intex XEON CPU with 2,66 GHZ and 32 GB RAM.OS: Windows Server 2003 R2 x64Database: SQL Server 2005 (9.00.4053.00)Memory settings:I think I will use 30 GB for maximum server memory, to reserver 2GB for the OS, is this okay?Other memory options I leave to defaultProcessors:Can I use the default settings (Automatically set I/O affinity and processer affinity)? Or should I change something here?Advanced:Actual the setting for Max Degree of Parallelism is set to 1. Should this value be changed?Are there any other settings I should make?Thanks for your help,Best regards,Peter |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-11-09 : 11:01:45
|
| I'll set the max memory to 28 GB, leaving 4 for the OS.Don't touch the I/O affinity or processor affinity. Don't touch max degree of parallelism either.Keep all the defaults until such time you find a real reason to change them, which is pretty rare. |
 |
|
|
Sachin.Nand
2937 Posts |
Posted - 2011-11-09 : 12:16:08
|
| I think 2 GB for OS is enough if you are not running anything else on the box.Since it is a 64 bit make sure you have set lock pages in memory.Set minimum and maximum memory settings.Are you using CLR or linked servers ?If yes then your memory setting will change.Set the MAXDOP value to the no of physical CPU cores.Processor affinity leave it with default setting which is 0.PBUH |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-11-09 : 14:30:35
|
| I seem to remember reading X-GB per CPU ... is that now "old hat" then?What about separate TEMPDB's "per CPU" ?Anything else still have a "per CPU" recommendation?@irxn: I presume this is a dedicated SQL box, with no other processes running? otherwise memory reservations is going to be difficult to advise on !! |
|
|
|
Sachin.Nand
2937 Posts |
|
|
irxn
Starting Member
13 Posts |
Posted - 2011-11-10 : 01:34:40
|
| Hi,thanks for your answers.I will leave 2GB for OS, because nothing else runs on the server.MAXDOP value I will set to 4.Is there any danger when I change the MAXDOP value? |
|
|
|
|
|
|
Best SQL Server properties