Optimizing the performance of operational databases and the applications that access them is a constant battle for DBAs. Of course, writing efficient SQL is the most important aspect of ensuring optimal database performance but no amount of SQL-tweaking or system-tuning can optimize the performance of queries run against a poorly designed or disorganized database. Therefore, time must be spent on 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.
There are many aspects of the DBMS that the DBA must be cognizant of in order to apply the proper database optimization techniques. Most of the major DBMSs support the following practices, although perhaps by different names. Each of the following techniques can be used to tune database performance:
- Partitioning—breaking a single database table into sections stored in multiple files
- Raw partition versus file system—choosing whether to store database data in an OS-controlled file or not
- Indexing—choosing the proper indexes and options to enable efficient queries
- Denormalization—varying from the logical design to achieve better query performance
- Clustering—enforcing the physical sequence of data on disk
- Interleaving data—combining data from multiple tables into a single, sequenced file
- Free space—leaving room for data growth
- Compression—algorithmically reducing storage requirements
- File placement and allocation—putting the right files in the right place
- Page size—using the proper page size (or block size) for efficient data storage and I/O
- Reorganization—removing inefficiencies from the database by realigning and restructuring database objects
Whereas, undoubtedly, all of these techniques are useful and appropriate at various times, the two most important database performance methods are certainly building proper indexes and reorganizing data structures.
Indexing
Creating the correct indexes on tables in the database is perhaps the single greatest performance tuning technique that a DBA can perform. Indexes can enhance performance by:
- Locating rows by value(s) in column(s)
- Making joins more efficient
- Correlating data across tables
- Aggregating data
- Sorting data to satisfy a query
Without indexes, all access to data in the database would have to be performed by scanning all available rows, which is very inefficient especially for large tables. Before tuning by creating new indexes, be sure to understand the impact of adding an index. This means understanding the access patterns of the table on which the index will be built. Useful information includes the percentage of queries that access rather than update the table, the performance thresholds set within any service-level agreements for queries on the table, and the impact of adding a new index on running database utilities such as loads, reorganizations, and recovery.
Occasionally some well-meaning soul will ask, “How many indexes should be created for a single table?” There is no set answer to this question. DBAs will need to use their expertise to determine the proper number of indexes for each table such that database queries are optimized, and the performance of database inserts, updates, and deletes does not degrade. Determining the proper number of indexes for each table requires in-depth analysis of the database and the applications that access the database.
The general goal of index analysis is to use less I/O to the database to satisfy the queries made against the table. Of course, an index can help some queries and hinder others. Therefore, the DBA must assess the impact of adding an index to all applications and not just tune single queries in a vacuum. This can be an arduous but rewarding task.
There are many aspects of proper index design that skilled DBAs and performance analysts need to know, but for now, let's move to the next big topic.