When you design a database, you have to ensure that the database performs all the important functions correctly and quickly. Some performance issues can be resolved after the database is in production, but 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, and give special consideration to performance when designing these tables. Also consider the effect on performance of increasing the numbers of users accessing the database.
Examples of design changes that improve performance include:
In conjunction with correct database design, correct use of indexes, RAID (redundant array of independent disks), and filegroups is important for achieving good performance.
Generally, the larger the database, the greater the hardware requirements, although the number of concurrent users/sessions, transaction throughput, and type of operations within the database determine hardware requirements as well. For example, a database containing infrequently updated data for a school library would generally have lower hardware requirements than a 1-terabyte (TB) data warehouse containing frequently analyzed sales, product, and customer information of a large corporation. Aside from the disk storage requirements, more memory and faster processors would be needed for the data warehouse to enable more of the data to be cached in memory and queries referencing large amounts of data to be processed quickly by Microsoft® SQL Server™.
| Database Design | Physical Database Files and Filegroups |
| Indexes | RAID |