Database Performance
When you design a database, you must make sure that the database performs all the important functions correctly and quickly. Some performance issues can be resolved after the database is in production. However, other performance issues may be the result of a poor database design and can be addressed only by changing the structure and design of the database.
When you design and implement a database, you should identify the large tables in the database and the more complex processes that the database will perform. You should also give special consideration to performance when you design these tables. Additionally, you should consider the effect on performance by increasing the number of users who can access the database.
Examples of design changes that improve performance include the following:
- If a table that contains hundreds of thousands of rows must be summarized for a daily report, you can add a column or columns to the table that contains previously aggregated data to be used only for the report.
- Databases can be over-normalized. This means the database is defined with several, small, interrelated tables. When the database is processing the data in these tables, the database must perform far more work to combine the related data. This additional processing can reduce the performance of the database. In these situations, denormalizing the database slightly to simplify complex processes can improve performance.
Hardware Considerations
Generally, the larger the database, the more hardware requirements there will be. However, other determining factors include the number of concurrent users and sessions, transaction throughput, and the types of operations within the database. For example, a database that contains infrequently updated data for a school library would usually have less hardware requirements than a 1-terabyte data warehouse that contains frequently analyzed sales, product, and customer information for a large corporation. Besides the disk storage requirements, the data warehouse would require more memory and faster processors so more data could be cached in memory and so queries referencing lots of data could be processed quickly.
The I/O subsystem, or storage engine, is a key component of any relational database and requires most of the planning. A successful database implementation typically requires careful planning at the early stages of a project. This planning should include consideration of the following issues:
- What type of disk hardware to use, such as RAID (redundant array of independent disks) devices. For more information, see About Hardware-based Solutions.
- How to put data onto the disks. For more information, see Using Files and Filegroups.
- Which index design to use to improve query performance in accessing the data. For more information, see Designing Indexes.
- How to appropriately set all configuration parameters so the database can perform well. For more information, see Optimizing Server Performance.
See Also
Concepts
Other Resources
Designing Databases
Optimizing Databases
Implementing Databases
Maintaining Databases (Database Engine)