SQL Server Performance Forum – Threads Archive
Yet Another SQL Server SAN question
I am trying to plan a system upgrade for our Data Warehouse server. During the upgrade the database will be redesigned to take advantage of some of the new BI features in SQL2k5. And, the system will be moving from 32-bit to 64-bit. I have not been given much choice when it comes to hardware. The server and san are as follows: SAN = IBM DS4300 w/ TurboServer = IBM eServer LS20 (2x AMD Opteron 275, 4GB RAM), Windows Server 2003 x64, SQL Server 2005 64-bit Enterprise Edition About the DB: The database is just under 600GB in size. Several of the tables have over a billion rows. However, most are less than 500 million rows. The data is loaded from source systems nightly and is growing at a rate of about 15GB per month. Beyond the nightly processing, there is no ‘write#%92 load. And, currently, over 99% of the queries are related to the last 25 months of data. It is planned to move all data partitions that are older than 25 months to cheaper SATA drives. While I am not able to make any changes to the hardware that has been chosen for the project, I do have control over how the SAN will be configured. All of the storage for the Data Warehouse will be dedicated (i.e. no other systems will be sharing the drives). I was planning on setting storage up as follows: Data/Indexes < 25mo. RAID 5 on 15k Fiber Drives
Data/Indexes > 25mo. RAID 5 on 7.2k SATA Drives
Transaction Logs RAID 10 on 15k Fiber Drives
Local Disk Backup RAID 0 on 7.2k SATA Drives Now for the questions: Does the setup look good considering what I have to work with? Should I consider separating the data from the indexes? And, should I consider separating the TempDB? The TempDB is pretty heavily used on the current system. However, most of this is due to poor design and the system redesign should eliminate a good portion of this. TIA!
too bad on the hardware,
most SAN’s are seriously under powered for DW, especially with SQL 2005,
which can easily hit 1.5GByte/sec on disk,
it will take 8 2Gbit/sec FC ports and over 100 drives to hit 1.5GB/sec on most mid-range SANs,
while 30 SCSI drives spread across 6 U320 channels can do the same. I would be very careful of a SAN with SATA drives, the one i have seen have absolutely horrible write performance. I really don’t see the benefit on separating data and indexes on DW, it might be helpful in OLTP if you cannot defrag the tables for whatever reason.
place the tempdb on the same drives as the new data (15K).
it is probably ok to have the logs on RAID 5, which should be able to do sequential write
be sure to lookup diskpart when using RAID 5.
and test the storage system performance before "accepting"
I prefer RAID 10 for the log files personally. I agree though that putting the tempdb on seperate drives probably won’t help a lot. I have some x365 servers that have six local drives. Getting good controller cards on the local drives, such as these, with a RAID 10 configuration for tempdb where it’s used heavily is an idea you might want to consider. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Thanks for the advice.
]]>