June 22, 2006 at 9:38 am
I recently read a few articles recommending using a seprate disk to house unclusted indexes and sorting (via temp db). Would using raid (5) with separate logical disks be the equivalent? If I have a raid array, is this even necessary?
June 23, 2006 at 7:21 am
I have had skilled IT managers tell me that a properly configured RAID will give you the needed efficiency since multiple disks are used. My argument is from a hardware level which I profess because I started nearly 30 years ago as a mainframe technician with a hard disk access specialty. I've had experience with test equipment most people have never heard of. This point of view has stayed with me since the beginning.
Tempdb is most commonly used for writing information. RAID systems show advantage only when data is read, and a slight disadvantage when data is written. Even then, the DMA hardware hogs most of the time just transferring information between the disk and the local buffer RAM on the disk interface, not to mention the time required to transfer from the buffer to the CPU's RAM.
Add to this the argument of semi-parallel processing of queries... a query execution plan could gather data from production tables at the same time it is building cross-references in tempdb.
Put the production db RAID on separate hardware from the tempdb drive. If your manager can swing the extra expense of a small RAID just for tempdb, then your system will be so fast it will scream.
...Mel
June 23, 2006 at 8:27 am
Thank you Mel! I forwarded this post back my manager.
June 23, 2006 at 8:43 am
Mel,
Given your hardware experience, have you seen substantial write performance differences when using difference drive interfaces, such as SCSI vs. Fiber Channel, SATA, etc...
Any recommendation for the best performance/cost ratio?
Erik
June 23, 2006 at 12:43 pm
Nah, nothing that you haven't already heard. The information that I gave above is a general statement from when I paid attention to such things as RAID5 read/write speed comparisons. I spoke strictly from a conceptual point of view.
If you want me to research current hardware specifications for a more in-depth comparison, then contact me via email. Conceptual answers are opinion based and are always free. Answers that require my time for research will be fee based. Either way, I am always happy to help.
...Mel
June 23, 2006 at 12:49 pm
"Answers that require my time for research will be fee based. Either way, I am always happy to help."
Aren't we all?
Thanks again, Mel!
-KM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply