SQL Server Performance

High Average Read Queue on 4 Disk RAID 10

Discussion in 'Performance Tuning for Hardware Configurations' started by kennyunger, May 3, 2010.

  1. kennyunger New Member

    Hello,In order to improve performance on our database, we've moved the data files from a 3 SAS disk RAID 5 to a 4 SAS disk RAID 10. My IT Manager read somewhere that SQL 2000 performs better on a strip size of 128KB, so that's what we set the RAID 10 up as. During this process we also installed a new SAS RAID controller. Today is the first day that the new array is under a full load. It is performing very badly, with average read queues of 40 - 50. Any suggestions?Thanks!Kenny
  2. satya Moderator

    Welcome to the forums.
    I believe as you are using SQL 2000, it is obvious to check whether its on latest (last) service pack 4 in order to ensure all the software side of files are arranged.
    Physical Size and Performance are at opposite ends of the configuration matrix for hardware.
    hard drives are always the slowest part of the system. More hard drives, to a point, equals faster SAN performance. You can mitigate the slowness of the hard drives by installing more RAM and using it for disk/SQL cache.

  3. marrow New Member

    As SQL server use 8K per page size, please try to format the HD/Volumn using 8K, I never see 128K is better and you can see that your result is not good.
    DBA100
  4. kennyunger New Member

    I'm afraid that adding memory to the machine will not give us any benefit. SQL 2000 only uses 2GB of memory at a maximum. Our ERP application is not compatible with SQL 2005, or else we would've upgraded long ago.
    Some more information:
    While under a load, the Avg. Disk Sec./Read counter is averaging 40ms with spikes up to 230ms.
    We are going to stay late tonight and set up the array and partition as is described here.
    This recommends creating the array with a 64K stripe and creating the partition with a 64K cluster size and 64K offset.
  5. Luis Martin Moderator

    "SQL 2000 only uses 2GB of memory at a maximum"
    That is not true (except for standard version). Any other version (enterprise, development) can use more. If OS is 32 then you have /3GB, etc. If OS is 64 then, SQL Enterprise take almost all RAM server have.
  6. marrow New Member

    /3G is the PAE, has to specify in the boot.ini, SQL 2000 user has to enable AWE API also, this make sure SQL can talk to PAE memory space using this API.
  7. marrow New Member

    Yeah. Update us once you tried that, I also want to know the result.
    One thing, how can the author of the link create so many test and gives result? Amazing, isn't it ?
    Is he/she test using the same hareware?
    BTW, how to set the offset anyway ?
  8. kennyunger New Member

    Here's an update. We put the new array in place last night. I will know in a few hours here if it made any difference or not.
    We are only licensed for SQL 2000 standard, that's why I said we could only use 2GB. We are currently testing a Win 2003 x64 installation with SQL 2000 enterprise. I am hoping that will get us past the 2GB barrier. We found instructions here. I don't have to set the /3GB in the boot.ini if I'm using Win x64 correct?
    This explains how to align/offset your partitions.
  9. Luis Martin Moderator

    " I don't have to set the /3GB in the boot.ini if I'm using Win x64 correct?"
    No, you don't.
  10. kennyunger New Member

    [quote user="Luis Martin"]
    " I don't have to set the /3GB in the boot.ini if I'm using Win x64 correct?"
    No, you don't.
    [/quote]
    Thanks for the confirmation.
    I can now say that setting up my Stripe size, Offset, and Cluster size to 64K made no difference. I am beginning to suspect the new RAID controller. It is an Adaptec 5805. The old one is a 3805. We are going to try recreating the Raid 10 array on the 3805 in hopes that it will help.
    In regards to the SQL 2000 Enterprise testing, I have set up a XenServer guest with Win 2003 x64 and 16GB of memory. I have specified in SQL to use 14GB. When I boot the machine, the page file shows as being 14.5GB. After running some large reports and very vague queries, Task Manager shows the sqlservr process as only using 170MB. I don't see any noticeable improvements in speed. I can repeatedly run a query requesting the same 1,000,000 rows and it takes the same amount of time to return the results on every run.
    For some reason my SQL performance counters are not available on this new machine so I'm at a loss on how to determine if SQL is really using more than the 2GB.
  11. marrow New Member

    hi, For the performance montor, you have to be the administrators.
    For the memory issue, please try to enalbe the AWE switch, it seems can't see the large memory pool.
    But did you see form task manager that the Windows server can see 16 GB of RAM you have? if the page file is 14.5GB, it seems doesn't make sense as it whould be more.
  12. kennyunger New Member

    "For the performance montor, you have to be the administrators"
    I am an administrator, but thanks. It appears as though the counters have failed to install properly.
    "For the memory issue, please try to enalbe the AWE switch, it seems can't see the large memory pool."
    The AWE switch is enabled, and the Max memory is set to 14GB.
    "But did you see form task manager that the Windows server can see 16 GB of RAM you have? if the page file is 14.5GB, it seems doesn't make sense as it whould be more."
    I'm sorry, I should have stated that differently. The server has 16GB of memory total. The amount of page file being used upon boot is 14.5GB, but the sqlservr process is only using 160MB. So it is like the 14GB is being reserved, but not actually used. I found a few websites stating that when the AWE switch is enabled, sqlservr doesn't always report the correct memory information to the Task Manager.

Share This Page