SQL Server Performance

Question from SQL noob

Discussion in 'Performance Tuning for Hardware Configurations' started by spinelli, Apr 14, 2004.

  1. spinelli New Member

    Hi all,

    First of all, I have not touched sql until 2 weeks ago and I appreciate all the info I have found on this site...it saved my bacon a few times. I would like to clarify a few things though and I can not seem to find a answer.

    1)I tried installing 6gb ram on one server just for testing purposes using a /5gb /pae switch in boot.ini.......but can sql dynamically configure ram when using awe? If not what should I do to figure out what amount of memory these tests actually want from the servers?

    2) My users complain their workstations can complete the sql back tests we run as fast as our servers..sometimes even faster. Hardware SHOULD not be an issue here. This company spent a fortune on hardware. Here are the specs...

    DISCLAIMER: I was not responsible for these purchases....just for making them work

    Workstation
    Supermicro X5DAE Mobo
    4GB RAM
    (2) 3.0 Xeons 1mb cache
    2 Seagate Cheetahs 15k 73gb HD NO RAID

    Server
    Supermicro X5DAE Mobo
    (2) Xeon 3.2 1mb cache
    4gb RAM.........trying now to set up test platform with 6gb
    (2) Adaptec 2200s 128mb RAID cards
    (5) Cheetahs 15k 73gb in 2 arrays...10 disks total each server

    Keep in mind I am NOT familiar with sql. I ran perfmon and used the recommended add ins to see what the server is up to when we run back tests. To me it seems our hardware is fine...I never show a disk queue more then 5 and that would be a spike, rarely a queue on my processors and I never sit at 100% cpu utilization. It will spike up to 100 for a moment and drop down to 80% or 50% rinse repeat. page split hit a max of 18 but again...that was a spike, it was usually at 0-3.

    Everything looks good to me...what am I missing here. Why are the workstations whopping the servers?

    I am really confused on the performance issue...hope you all can help.




  2. Luis Martin Moderator

    1) What Whindows, SQL and SP do you have?.
    2) Never compare performance WS with Server, simple not fair. One user instead how much?.
    3) Is SQL server dedicated?.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  3. spinelli New Member


    Thanks for the reply.....

    1)2003 Server Standard on most servers with 2003 Enterprise on test bed. All with sql 2000 sp3

    2)No users on these servers..we just run tests on them. Again I am not familiar with sql but to give you an idea..we are a financial firm and we try to predict the market based on variables which they run on the servers.

    3)yup dedicated.

    Hope that helps
  4. joechang New Member

    what is the sql back test you referred to?
    can you post the script as well as the SHOWPLAN_ALL and STATISTICS IO output
  5. joechang New Member

    on q1. the boot.ini switches are /3GB and /PAE
    /3GB tells the OS to split the 4GB 32-bit address space at 3GB for user apps, 1GB for OS kernel, instead of the normal 2GB/2GB split.
    this is helpful in most cases because the OS almost never needs more than 1GB for the kernel, one exception being when there is more than 16GB of memory.
    the /PAE switch is for using memory above 4GB

    unless your active data size is more than 3GB, /PAE may actually degrade performance somewhat, because of the higher overhead of using PAE/AWE memory.

    try running in just /3GB mode and check for disk reads after the intial warmup. if there rarely any disk reads, don't bother with AWE.
    if you do have significant disk read, use the /PAE switch, set SQL Server memory to 1GB less than total system memory, and note if disk reads drop. if it does drop, then the higher overhead of AWE memory is worth it, otherwise go back and skip the PAE mode
  6. Luis Martin Moderator

    May be I'm missing something, but Windows Standard can manage more than 2 Gbytes?


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  7. derrickleggett New Member

    Need you to be more specific here:

    1)2003 Server Standard on most servers with 2003 Enterprise on test bed. All with sql 2000 sp3

    On the SQL Server you are putting 6gb of RAM on, since that's the one the thread is related to:

    1. You have Windows 2003 Enterprise?
    2. You have SQL Server 2000 Standard Edition?

    IF you have the above combination, you need to know the following:

    1. SQL Server 2000 Standard Edition can only use 4gb of RAM. The most you probably want on the server is 5gb if all you are using it for is SQL Server. The operating system can use the other 1gb. Any more than that is just overkill.

    2. You could really be killing yourself if you actually have /5gb instead of /3gb for the switch.

    3. If you are using AWE, you should set SQL Server to just use the maximum 4gb it can use. You can do this with sp_configure. Also, run:

    USE master
    EXEC sp_configure 'show advanced option', '1'
    GO
    RECONFIGURE
    GO

    Send us the results of that, so we can see how you have the server set up.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  8. spinelli New Member

    LuisMartin..
    with 2003 server standard and sql enterprise when I select properties -> memory I can allocate up to 3455 ram to sql on the systems w/ 4gb installed. If thats right or not I dont know..again I dont know sql well but that is what it says.


    derrickleggett...
    On the test bed 2003 server enterprise sql 2000 enterprise sp3
    I ran the script you posted..nothing happened.

    joechang..
    I wish I knew what you were talking about.

    I am just doing some part time work building these servers. I am not a programmer,dba, or really anything much other then a wrench monkey here to build some boxes. Problem is the boxes(servers) are not giving them an increase in performance they expected.

    Also when I had the /5gb /pae switches on and awe enabled the server would reboot in the middle of the tests.

    Thanks for the help guys, I realize this is above my head but I am trying best I can to figure this out.



  9. Luis Martin Moderator

    I was wrong, Windows 2003 Standard support up to 4 Gb, so I suggest what Joe said, use /3G.
    Try this one and see if SQL server can reach dinamically memory, using Enterprise Manager.

    Anyway, if all is Ok., this is the first step to get more performance on server.
    The problem is: How we can help you after hardware problem?. At least we need from you some common words ie: Execution Plan, Profiler, Performance Monitor, Tuning.
    Hope you understand what I mean.




    Luis Martin
    Moderator
    SQL-Server-Performance.com

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  10. spinelli New Member

    I am currently running the same test over and over with different configs and logging the following from perfmon (got the info from this site)

    mem-pages/sec
    phys disk-%disk time
    proc time
    proc queue
    page splits/sec
    buffer cache hit ratio
    mem both target and total server memory


    Will report back when have more info after first test completes.

    For the first test i removed all references to any mem other then 4 gb. I did leave the /3gb in boot.ini and ran it dynamically to see what sql wants. Test should complete in about another hour.

    Once I can prove I am getting the best performance I can from the servers, my job is done here. Hopefully I can convince them to hire a dba at that point but until i can prove the hardware is not at fault then the blame rests on my shoulders.

  11. derrickleggett New Member

    Also when I had the /5gb /pae switches on and awe enabled the server would reboot in the middle of the tests.

    There is no /5gb switch. You need to change it to /3gb. There will not near as big of a performance increase if you don't use this and have 6gb of RAM. You need to set this correctly or you can cause all kinds of problems.

    You then need to go into Enterprise Manager and set your memory to have a max of 5gb of RAM.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page