Tip

How will networked storage affect application performance?

The first question most DBAs ask about networked performance is "How will it affect performance?"

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

    Requires Free Membership to View

tuned for read activity instead of write."

"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


This was first published in May 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.