Database Development: Optimize the Database Environment

Hardware considerations and careful placement of database components is a vital part of database performance tuning. For maximum database performance you should always consider the following:

Start with an optimized disk partition. Computers are binary devices and operate best when using binary addressing (1, 2, 4, 8, 16, 32, 64, etc.) is considered (the reason you see 16, 32, and 64 bit operating systems and computer components. Unfortunately, when you depend upon operating system defaults for the creation of disk volumes the resulting volumes are often misaligned (created with a 63 sector offset), causing additional seeks to access data spanning sector boundaries. This effect is similar to what you experience with fragmented drives, the system needs to work harder just to perform the fundamental disk access requirements. There are a number of good articles on this subject including one by Kevin Kline:

How To Improve Application and Database Performance

More spindles usually equal more speed. Plan your database layout carefully. Log files should be on a different disk spindle than the database itself. Clustered databases can also spread the load over multiple spindles / systems. Spindle speed is also important. Pay close attention to the disk RPM (revolutions per minute – some SCSI drives run at 15,000 RPM), latency and seek time. Disk buffer size is also important, as are system motherboard capabilities such as NCQ (native command queuing – disk access requests are optimized to minimize read head travel over the disk surface). There are also performance considerations when selecting SATA vs. SCSI drives, some of which involve controller and motherboard capabilities.

Spread the processing load – use multiple servers. A basic configuration would include an application server and a database server. This is good for security as well as performance. Whenever possible the servers should be dedicated to the tasks assigned to them, application processing running on the application server and database requests processed by the database server. Watch ancillary functions such as printing to determine when they are impacting performance and offload them to their own servers if necessary. If you find your data maintenance functions impacted by heavy reporting requirements consider a data warehousesolution by ISG. It never hurts to add some time for performance testing into your database development project schedule. Small optimization adjustments during development can result in significant performance gains when the database is operating under full load.

An inefficient network inhibit database performance. Pay close attention to the network bandwidth and network components when configuring your database environment. As with the other physical components listed above, a restrictive network could throttle database performance. Whenever possible gigabit (or fiber) connections should be utilized between server components including network switches and attached storage devices. Managed switches can also increase throughput by eliminating extraneous traffic at the server NICS (network interface cards). Connections to remote databases need to consider firewall throughput capabilities as well as minimizing the number of hops (connections) encountered on the route to the database server.

Tune the Database: Tips for Database Performance Optimization

Optimize and Tune the Database: Once you have optimized the physical attributes of the network it is time to address optimization and tuning of the database itself. This tuning can be applied to several areas including:

Normalize your database. A properly designed database should be normalized to improve performance and data integrity. Database normalization should always be included in any database development project. Basic database normalization involves the removal of redundant data and includes structured lookup tables, both of which can help improve performance. Make sure to test your optimized database after normalization as there are times when a de-normalized structure can actually improve performance. Use de-normalization carefully though, as normalized databases are generally best for performance and data integrity.

Use database indexes to improve performance. You should consider creating indexes for table columns that are frequently used as important selection criteria, sort criteria, and/or used in joins. Look for tools to help you determine when to add indexes, such as the Index Tuning Wizard in SQL Server to gain recommendations for tuning. The tools usually evaluate queries you need to optimize by evaluating a representative sample (usually collected over several days) of your application’s database requests.

Let the database do the work. After all, the database is designed to handle the data requests and data manipulation and the server is most likely far more powerful than your desktop computer. Construct SQL queries, stored procedures, and triggers to utilize the power of the database engine. This will not only improve performance, but will also improve the integrity of your database by reducing the number of program routines modifying the data and by insuring data relationships through the use of database triggers.

Contact ISG today for a free telephone consultation to discuss your database performance tuning and database optimizationrequirements. Our database consultants will assist you in evaluating the requirements and creating a detailed plan designed to help you your overall database and software application performance. ISG database performance improvement programs can also be combined with our data warehouse solutions.