June 21, 2010 at 8:34 am
Hi,
I need some clarification w.r.t. server settings.
I have 15 processors running on my new machine.
12 GB RAM.
And the new server is completely dedicated to database server.
The SQL Server Standard Edition is installed on the new server along with SQL Service pack 3.
1)
I have gone through the instance properties of the SQL instance and found the below by default after installation.
Under Processor Tab, i can see all the CPU's are checked by default.
Max worker threads set as 255 (default)
Boost SQL Server priority on windows is unchecked.
USe Windows NT fibres unchecked.
In the same tab, For parallelism , use all processors radio button is selected.
Here, i have a question. On what basis we can set this affinity mask or number of processors for parallelism is decided???
Is that a advantageous to use all processors for parallelism??? or we can configure only some processors saying
-- use only 4 processors out of 15 or so.... On what basis we can reconfigure this option or else shall i leave it as default option i.e. use all processors.
2) Similary, i have gone through the Memory tab.
Total Avaiable memory on the machine = 12 GB
OS is Windows 2003 Server Enterprise Edition , Service pack 2.
PAE is enabled.
SQL Server 2000 standard edition with SP3 is sitting on the machine.
Memory tab contains the following values
Dynamically managed memory is selected
Min : 0MB
Max is set to 12 GB i.e total available memory.
Now my doubt is whether is SQL server can utilize this additional memory ????
Before that, can we assign all the memory to sql server ???
I have checked the AWE option but it is not enabled
sp_configure
name min max config_value run_value
awe enabled0100
So, can i enable the AWE option and take advantage of additional memory ??
Also, to my knowledge since sql server is 32-bit,we have a limit upto maximum upto 4 GB it can address. right??? remaining it is of wastage. i beleive!!! correct me if am wrong.
How much memory can i utilize from the given OS (Windows Server 2003 Enterprise Edition) for SQL Server 2000 Standard Edition??
boot.ini looks as follows
---------------------------
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=optout /fastdetect
What all modifications can i make for optimal utilization for this server???????
Thanks in Advance.
June 21, 2010 at 8:37 am
Oracle_91 (6/21/2010)
Min : 0MBMax is set to 12 GB i.e total available memory.
Now my doubt is whether is SQL server can utilize this additional memory ????
Before that, can we assign all the memory to sql server ???
Not a good idea. Leave some memory for the OS (2 or 3 GB)
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
June 21, 2010 at 8:59 am
Orc-91 (Sorry, just can't say that O word;-)
In order to set the AWE on, you will need to restart your SQL Server. You did say that you have the /PAE switch in the boot.ini file on but I do not see it in your printout. You need to put this in the boot.ini file.
With Windows 2003 Enterprise Edition, the server is happy with 3GB of memory for the OS. I would give it 4 gb, just to be safe. The ultimate goal is to reduce/eleminate paging on the server as this would become a major bottleneck. Give all of the rest to SQL Sever. So your SQL Server should be set for 8GB of memory. You may find that you will need more memory, but on all of my servers, I give the OS, which also runs Veritas and Symantec Endpoint, 4 GB and they are happy. I have the SQL Server priority checked, as I do not care about anything else on these boxes. SQL Server is most important.
As for the maximum amount of memory for 32 bit boxes, I believe that you can go up to a max of either 16 or 32 GB of memory but need the /PAE switch turned on (in the boot.ini).
Finally, you should know your environment and whether you have a lot of parallelism that goes on. I have my larger boxes set to 4 cpu's for parallelism so that they do not take all of the CPU's, thus creating a major bottleneck by having SQL Server waiting on CPU's. It has worked quite well. As for my environment is currently 75 servers with well over 250 databases. Several of the databases will become terrabyte within the month.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
June 21, 2010 at 12:25 pm
Hi,
Thanks for the suggestions.
You did say that you have the /PAE switch in the boot.ini file on but I do not see it in your printout. You need to put this in the boot.ini file.
I agree with you, but when i right cclick on MyComputer--> Properties, i can see right at the bottom of the General tab pane as Physical Address Extension. Then i went to Advanced tab-->Startup and Recover--> Click on Edit buttion which opens me the boot.ini file content and that is was being pasted in the forum.
Any idea's why it is showing in General Tab?
June 21, 2010 at 12:35 pm
Finally, you should know your environment and whether you have a lot of parallelism that goes on.
Jim , how can we know or determine about parallelism is being used ?
This, would give me a better understanding of setting the affinity mask value.
Thanks in Advance.
June 21, 2010 at 1:03 pm
What all are the pre-cautionary things we need to take before we edit the boot.ini for adding the switches.
[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(2)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(2)\WINDOWS="Windows Server 2003, Enterprise" /fastdetect /PAE
Is there any side effects if any additional white space characters given while mentioning the switches? if anything, how to bring my system up?
June 21, 2010 at 2:15 pm
It won't matter what you do or how you set this up. SQL Server 2000 Standard Edition is limited to 2GB of memory. Enable PAE, set AWE - won't change a thing.
If SQL Server needs more memory, you have 2 options:
1) Upgrade to SQL Server 2000 Enterprise Edition
2) Upgrade to SQL Server 2005/2008 Standard Edition
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 21, 2010 at 2:36 pm
If memory serves me on Windows 2000, you can try to set the /3GB switch as well in order to pass the 3GB limitation. It would have to be on the Enterprise edition. Misread, and thought that you were already on Windows 2003 Enterprise.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
June 21, 2010 at 11:50 pm
oh Thanks for the correction.
June 22, 2010 at 12:23 am
I right click the properties of the sql 2000 instance and tried to make changes in the DATABASE SETTINGS properties tab,
and Processor tab but the changes are not being into effect. Do we need to change these from command line only??
Even After pressing OK button also, it didnt prompt me for a restart of sql server.
For memory tab ichanged , it asked me for restart of sql service
June 22, 2010 at 6:51 am
Orc,
Maybe this will help you:
Memory Settings:
http://support.microsoft.com/kb/274750
You may also get some more information from http://msdn.microsoft.com/en-us/library/aa933149(SQL.80).aspx
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
June 22, 2010 at 7:27 am
Thank You!
June 24, 2010 at 7:17 am
I Right Click on the Instance --> Properties -->
goto Connections tab and under Remote server connections
i checked the Allow other SQL Servers to connect remotely to this SQL Server using RPC
and below that am trying to set Query time-out (sec,0=unlimited) = 600 sec (i.e 10 min)
I clciked ok and tried to see the properties again, changes are not getting effected..
Also, under Processor tab, i have 15 processors but i want to give only use 2 processors
and specify the Minimum query plan threshold for parallel execution (cost estimate) as : 5 sec.
but it is not changing.
Why is that so??
For Database settings tab, Server settings, Memory setting tab worked well and whenevr it asked for
sql server restart, i have done it.
But why it is stopping me to set the processors and qUERY time -out time for Remote connections ???
Any thoughts
Thanks in Advance.
June 24, 2010 at 7:25 am
How many CPU's can we set to get good performance faster from SQL Server 2000 Standard Edition if i have 15 CPU's?
Is there any bench marking for such scenarios saying that for given SQL Server Edition , setting n-CPU's would yeild good performance???
Any suggestions are greatly appreciated.
Thank You.
June 24, 2010 at 8:28 am
15 CPUs? That's an odd number.
Unless the server is shared between SQL and other apps, there's no reason to change the affinity setting from the default of all.
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
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply