You didn't mention if you have Netflow?
Here are some great Tips that Josh "Head Geek" Commented on a few months ago:Tips and Tricks for Improving SQL Performance
Quick Summary:Head Geek's Top 5 Tips for Improving SQL Performance
#5 - Add more RAM. Doesn't really matter how much you have, adding more will almost always help. Be sure that your SQL instance and OS are capable of consuming the additional RAM and if not make it so.
#4 - Just say "no" to RAID 5. It's great for application servers but horrible for database servers where I/O performance is important.
#3 - Place the data and log files (.mdf and .ldf) on separate logical drives and separate channels or controllers.
#2 - Unless your SAN is optimized for high I/O vs. large I/O stick with a locally attached disk array.
#1 - Buy disk controllers with battery backed-up write-back cache. The more the better, but at least 256MB.
Thanks for the reply! I did see Josh's post. I have addressed as many of these items as I can within the confines I have been given. My question is kind of an addendum to his post, based on the SQL optimization that one can attain by separating the tempdb and/or logs from the data. I can install one of two ways, and was curious if someone might be able to give the nod to one of the two based on experience.
We are not using NetFlow at the time on this box.
I would say go with the RAID1 (sys/apps) and the RAID10 (SQL)
it will give you the best performance for what you have.
dont bother trying to separate the MDF from the LDF due to you only have one controller anyway.
Just put them both on the SQL RAID and you should be fine.
We just run a single RAID10 for both sys/apps and SQL (and then create a c: partition for sys/apps and d: for the sql data).
We do this for a couple of reasons..
- Our sys/app boot partition is normally 20Gig, so we might as well use the rest of the drive for the data space.
- The more read/write heads (i.e. drives) the better. If you only have six drives, better to use all of them rather than just four and spread the I/O a little.
On the HP Raid controllers, we also set the stripe size to 128K, and then format the data NTFS partition with a cluster size of 64k.
You should also align the volume cluster if you are using a RAID controller (http://support.microsoft.com/kb/929491) just to make sure.
Edit: If you do have I/O issues, you could also consider not logging (i.e. simple mode). We perform a full backup every two days. If we ever had a RAID or other hardware failure, then dropping a couple of days data wouldn't be the end of the earth for us.
Great responses everyone, thank you!