database settings

  • 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.

  • Oracle_91 (6/21/2010)


    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 ???

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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?

  • 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.

  • 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?

  • 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

  • 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

  • oh Thanks for the correction.

  • 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

  • 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

  • Thank You!

  • 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.

  • 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.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 21 total)

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