SQL Server Performance

Best Hardware setup for this DB

Discussion in 'Performance Tuning for Hardware Configurations' started by SeanP, Sep 30, 2004.

  1. SeanP New Member

    First let me state, I am in no way a expert DBA, I can handle the basics, but don't know enough about best Hardware, except for what I have read on this site, and forums. We have a problem with the performance of our Server. Details are below.

    Server
    Compaq Proliant ML570
    Proc: Quad PIII 700mhz XEON 1MB cache.
    RAM: 4GB.
    SCSI Controller: SmartArray 431 (2MB read *ONLY* cache).
    Drives: 6x 36.4GB ULTRA320.
    RAID Config: Single Array in RAID 5 for all 6 Drives.
    OS: Microsoft Windows Server 2003 Standard Edition.
    Microsoft SQL Server 2000 SP3
    Database: Currently 25GB. Balance Read and Write.

    My assumption is the SCSI controller creating a bottleneck. It is only an entry level card, single channel.

    Our problems is occational slow queries and Database maintenance jobs. With problems of PAGEIOLATCH_SH at end of month, and beginning or month, basically when processing is high.

    I have no problem changing the config, if needed. Would anyone suggest better hardware? Specifically the SCSI Controller, does it seem "skimpy" to anyone else?

    Thanks for any help in advance!
    -Sean

  2. SeanP New Member

    UPDATE:
    Ran perfmon:
    Logical Disk:
    1. Avg. Disk Queue length: Averaging at about 15 (per 100.000 scale).
    2. % Idle Time: About 50.

    Document on this site suggest this should never top 2.
  3. Luis Martin Moderator

    Ok. Some questions first:

    1) Did you work in performance? (Profiler, Performance Monitor)
    2) Did you have manteinance plan, including Index reorganization?
    3) Did you update statistics from time to time?.

    If you you do all work to tune, then Avg Disk queue lenght 15 is to hight.
    With 25Gby database, I prefer to have 4 mirror disk RAID 10 with 72Gbytes in total.
    Server write faster in RAID 10 than RAID 5, and you have a balance database (read and write).



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  4. SeanP New Member

    Thanks Luis for the quick reply.

    Answers:
    1. I checked all counter with Performance Monitor (perfmon) as suggest by this site. The two counters I stated above are what caught my eye, specifically the % idle.
    2. Yes maintenance plan is run nightly to Optimize nightly (Almost takes too long now for given time window).
    3. Job to rebuild Index, and update stats Weekely (weekend, takes long time to complete).

    The presents of PAGEIOLATCH_SH wait_types is concerning. Only references I've found on these is indicating Disk IO bottlenecks.

    Results of Wait State Check:

    WAIT TYPE | WAIT TIME | PERECENTAGE
    ***total***1191336.0100.0
    CXPACKET615151.051.6
    PAGEIOLATCH_SH490658.041.2
    NETWORKIO77337.06.5
    WRITELOG6414.0.5
    PAGEIOLATCH_EX1759.0.1
    PAGEIOLATCH_DT.0.0
    TRAN_MARK_NL.0.0
    TRAN_MARK_KP.0.0
    TRAN_MARK_SH.0.0
    TRAN_MARK_UP.0.0
    TRAN_MARK_EX.0.0
    TRAN_MARK_DT.0.0
    PSS_CHILD.0.0
    EXCHANGE.0.0
    XCB.0.0
    DBTABLE.0.0
    EC.0.0
    TEMPOBJ.0.0
    XACTLOCKINFO.0.0
    LOGMGR.0.0
    CMEMTHREAD.0.0
    PAGESUPP.0.0
    SHUTDOWN.0.0
    CURSOR.0.0
    EXECSYNC.0.0
    LATCH_NL.0.0
    LATCH_KP.0.0
    LATCH_SH.0.0
    LATCH_UP.0.0
    LATCH_EX.0.0
    LATCH_DT.0.0
    PAGELATCH_NL.0.0
    PAGELATCH_KP.0.0
    PAGELATCH_SH.0.0
    PAGELATCH_UP.0.0
    PAGELATCH_EX.0.0
    PAGELATCH_DT.0.0
    PAGEIOLATCH_NL.0.0
    PAGEIOLATCH_KP.0.0
    MISCELLANEOUS.0.0
    LCK_M_SCH_S.0.0
    LCK_M_SCH_M.0.0
    LCK_M_S.0.0
    LCK_M_U.0.0
    LCK_M_X.0.0
    LCK_M_IS.0.0
    LCK_M_IU.0.0
    LCK_M_IX.0.0
    LCK_M_SIU.0.0
    LCK_M_SIX.0.0
    LCK_M_UIX.0.0
    LCK_M_BU.0.0
    LCK_M_RS_S.0.0
    LCK_M_RS_U.0.0
    LCK_M_RIn_NL.0.0
    LCK_M_RIn_S.0.0
    LCK_M_RIn_U.0.0
    LCK_M_RIn_X.0.0
    LCK_M_RX_S.0.0
    LCK_M_RX_U.0.0
    LCK_M_RX_X.0.0
    IO_COMPLETION.0.0
    ASYNC_IO_COMPLETION.0.0
    RESOURCE_SEMAPHORE.0.0
    DTC.0.0
    OLEDB.0.0
    FAILPOINT.0.0
    ASYNC_DISKPOOL_LOCK.0.0
    UMS_THREAD.0.0
    PIPELINE_INDEX_STAT.0.0
    PIPELINE_LOG.0.0
    PIPELINE_VLM.0.0
    PAGEIOLATCH_UP16.0.0


  5. Luis Martin Moderator

    Ok, now is time to run profiler, find long queries, analyze execution plan (or run index tuning wizard)and add index if neccesary.

    If still Avg. Disk queue is 15 (normal is below 2) you have a serius disk bottlenecks and may be is time to go to RAID 10.

    But, RAID matter is oppinable, so I suggest before change RAID, wait others members oppinions.



    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  6. derrickleggett New Member

    Also, you need to break that disk queue down. Run the write queue and the read queue perfmon counters. If your read queue is fine, but your write queue is horrible (which is what I'm guessing), you might be able to use RAID 10 and alieve the situation. RAID 5 is horrible for writes. Having said that, the RAID controller card you're using sucks. <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  7. SeanP New Member

    My focus has been on the card itself from the beginning. I just was looking for other opinions. Any suggestions on a replacement SCSI (RAID) controller. Compaq/HP brand please.

    Thanks again in advance.
  8. simas New Member

    it depends on how much money you are willing/able to spend on it.http://h18004.www1.hp.com/products/servers/proliantstorage/arraycontrollers/index.html

    I have used MSA1000 for the clusters built 6 months ago and am pretty happy with it
    http://h18006.www1.hp.com/products/storageworks/msa1000/index.html


    Also, what is the expected growth rate of data in the database and do you plan for any maitenance that would keep the database size constant (on-going purges of old archive data,etc). Yes if do not expect a lot of growth or plan to have automatic purge procedures, consider putting your drives into 100Gb RAID 10 - ability to potentially sustain multiple drive failures (up to 3 in your case) + much better performance on writes is well worth the amount of disk space you "loose" by going form RAID 5 to RAID 10. Unless your reads greatly outnumber your writes, RAID 5 is not the best solution due to a N times physical writes for each logical write where N is the number of disks...

    simas
  9. SeanP New Member

    Thanks to everyone for their help and suggestions. I greatly appriciate it all. So as it stands now this is my purposed solution. <br /><br />Buy an additional Array Card to add to the server. We have the original 18GB (6) drives that shipped with the server for use. We will then build a mirrored array for OS (logical Drive C<img src='/community/emoticons/emotion-1.gif' alt=':)' /> and SQL Server 2000 alone using existing SmartArray 431 card using 2 of the 18GB drives. Install Windows Server 2003 Standard. Build two new arrays one at RAID 5 for database storage (logical Drive D<img src='/community/emoticons/emotion-1.gif' alt=':)' /> using 6 36.4GB drives. Finally a 3rd array (Logical Drive E<img src='/community/emoticons/emotion-1.gif' alt=':)' /> in RAID 10 using 4 18GB drives for the transaction log DB, since its is 99% Write. <br /><br />Does this sound reasonable? Would it be best to make the Database storage array RAID 10? Databse is currently seems balanced for both read write, but it is our ERP for the company, so there is lots of writes too.<br /><br />as for the Array Card, its looks like a Dual channel is needed sincewe are using ML570 contains only 2 cages of 6 drives each, and the should proabble not be use?
  10. joechang New Member

    i seem to recall that cxpacket was a parallel execution wait,
    i would look for a big query with a parallel execution plan and try
    OPTION (MAXDOP 1) and (MAXDOP 2)
    assuming that it may currently be running on all 4 CPUs

Share This Page