The general answer is positively, if only because the database server is relieved of some of its storage load by the network and storage array. But different applications, such as online transaction processing (OLTP), business intelligence, data warehousing and sequential data, require different approaches to performance tuning. In addition, the type of storage and the type of disk array influence database performance.
In general, Fibre Channel SANs provide the best performance due to their use of high-speed FC disks, large caches and fast, 2Gb/sec connections. Typical performance-tuning practices inclue cache tuning and optimizing the layout of the data files on the disks. Other options include ensuring that OS partitions are sector-aligned, utilizing 15K rpm disks and spreading data across as many spindles as possible.
In OLTP, log performance is key. Microsoft adivces tuning the cache for write activity, ensuring that the log files are placed on separate physical spindles, and using RAID1+0 for write-intensive applications. You can also opt for RAID 5, which relies on parity checking because most of the I/O will take the form of sequential reads.
For decision support system workloads with complex reporting queries, more thought may need to be given to tempdb data file placement," suggests Mike Ruthruff, program manager for Microsoft SQL Server. "Cache could be
"Performance revolves around the number of drives, the size of the cache ahd the size of the stripes," says Ed Whalen, founder and CTO of Performance Tuning Corp., Austin, TX. For example, "Forty gigabytes of cache makes a lot of performance problems go away," he says. "With 2GB of cache, I'd be worrked about RAID 5 performance."
The problem with RAID is that each write requires four physical transactions. To determine the number of drives, plan on mo more than 150 IO/sec per disk, says Whalen. So if you expect 1,500 IO/sec, you'll need at least ten drives. When the database is moved to a SAN, there's one common sticking point for DBAs: All high-end and some midrange SAN arrays have their own hotspot management capabilities. For some DBAs, it's tough to let go of laying out the data themselves.
About the author: Alan Radding is a frequent contributor to Windows- and storage-related publications.
More information from SearchWinSystems.com
- Tip: Performance and memory tuning
- Topics: SANs
- RSS: Sign up for our RSS feed to receive expert advice every day.
This was first published in May 2006