Best Hardware setup for this DB | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Best Hardware setup for this DB

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

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.
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 />[email protected]<br /><br />When life gives you a lemon, fire the DBA.
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.
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

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