One of the most time-consuming aspects of being a DBA is assuring the performance of database systems and applications. A lot of time will be spent on tuning application code and SQL statements to boost efficiency and optimize access. But SQL is only one aspect of database systems performance.
It is also important for DBAs to spend time tuning and optimizing the design, parameters, and physical construction of database objects, specifically tables and indexes, and the files in which their data is stored. The actual composition and structure of database objects must be monitored continually and changed accordingly if the database becomes inefficient. No amount of SQL tweaking or system tuning can optimize the performance of queries run against a poorly designed or disorganized database.
Techniques for Optimizing Databases
The DBA must be cognizant of the features of their specific DBMS in order to apply proper techniques for optimizing the performance of database structures. Most of the major database management systems support the following techniques although perhaps by different names and using different methods:
- Partitioning—breaking a single database table into sections stored in multiple files; you can partition a single file on the same system, use shared disk clustering to partition or use shared nothing partitioning (each DBMS supports different types of partitioning in different ways)
- Raw partition versus file system—choosing whether to store database data in an OS-controlled file or not; not as common as in earlier days, but raw partitions can bypass operating system inefficiencies in some cases
- Indexing—choosing the proper indexes and options to enable efficient queries; creating useful indexes for optimal performance is one of the most important aspects of performance tuning for SQL databases
- Denormalization—varying from the logical design to achieve better query performance; denormalization should only be attempted as a last resort when optimal performance cannot be obtained with a fully normalized implementation
- Clustering—enforcing the physical sequence of data on disk; when data is accessed in a specific order having it clustered on the same page (or range of pages) can improve performance
- Interleaving data—combining data from multiple tables into a single, sequenced file; although this is less popular than in earlier days, interleaved data can improve join performance
- Free space—leaving room for data growth; when free space is available data can be added to a table without causing it to become disorganized
- Compression—algorithmically reducing storage requirements; more compressed data can be stored in the same amount of space thereby reducing storage costs (and possibly improving access if more rows per page can be stored)
- File placement and allocation—putting the right files in the right place; this includes both database data and database system files, such as logs
- Page size—using the proper page size (or block size) for efficient data storage and I/O; smaller page sizes cause fewer rows per page which can increase I/O requirements for sequential data access
- Reorganization—removing inefficiencies from the database by realigning and restructuring database objects; performance gains that can be accrued with reorganization are tremendous when data is unclustered, fragmented, or disorganized in any way.
All of these things must be taken into consideration and a plan must be built for monitoring and tuning each type of physical database issue outlined in this list. Not every aspect will apply to each database object, but each aspect should be analyzed for its applicability to each database object. Furthermore, aspects that do not apply upon initial implementation may become viable as the application needs change and the volume of data, database characteristics, and usage patterns change over time.
Automation
If possible, the DBA should look into using features of the DBMS or third-party tools to automate database maintenance and optimization techniques. For example, automating reorganization can be a worthwhile endeavor. An automation tool can query database statistics and trigger reorganization only for those database objects that have surpassed the high-water mark for a combination of statistics. For example, the DBA may want to automatically reorganize all table spaces where the cluster ratio is below 85% and all indexes where the leaf distance has a value greater than 100.
The Bottom Line
Applications and data are constantly changing. Users require instant response time and 24/7 availability. The database structures that support your applications must be properly maintained to ensure optimal application performance. Proper database design, appropriate choice of clustering, and reorganizing databases based on statistics help to deliver efficient databases. Furthermore, DBAs can ensure database performance by automating these processes to reduce the risk and errors associated with manual database maintenance.