How to Optimize Your Database's Design

Like application design, database design is very critical to the scalability and performance of your SQL Server applications. And also like application design, if you don't do a good job in the first place, it is very hard and expensive to make changes after your application has gone into production. Here are some key things to keep in mind when designing SQL Server databases for scalability and performance.

As always, you will want to test your design as early as possible using realistic data. This means you will need to develop prototype databases with sample data, and test the design using the type of activity you expect to see in the database once production starts.

One of the first design decisions you must make is whether the database will be used for OLTP or OLAP. Notice that I said "or". One of the biggest mistakes you can make when designing a database is to try to meet the needs of both OLTP and OLAP. These two types of applications are mutually exclusive in you are interested in any sense of high performance and scalability.

OLTP databases are generally highly normalized, helping to reduce the amount of data that has to be stored. The less data you store, the less I/O SQL Server will have to perform, and the faster database access will be. Transactions are also kept as short as possible in order to reduce locking conflicts. And last of all, indexing is generally minimized to reduce the overhead of high levels of INSERTs, UPDATEs, and DELETEs.

OLAP databases, on the other hand, are highly de-normalized. In addition, transactions are not used, and because the database is read-only, record locking is not an issue. And of course, heavy indexing is used in order to meet the wide variety of reporting needs.

As you can see, OLTP and OLAP databases serve two completely different purposes, and it is virtually impossible to design a database to handle both needs. While OLAP database design is out of this book's scope, I do want to mention a couple of performance-related suggestions in regard to OLTP database design.

When you go through the normalization process when designing your OLTP databases, your initial goal should be to fully normalize it according to the three general principles of normalization. The next step is to perform some preliminary performance testing, especially if you foresee having to perform joins on four or more tables at a time. Be sure to test using realistic sample data.

If performance is acceptable, then don't worry about having to join four or more tables in a query. But if performance is not acceptable, then you may want to do some selective de-normalization of the tables involved in order to reduce the number of joins used in the query, and to speed performance.

It is much easier to catch a problem in the early database design stage, rather than after the finished application has been rolled out. De-normalization of tables after the application is complete is nearly impossible. One word of warning. Don't be tempted to de-normalize your database without thorough testing. It is very hard to deduce logically what de-normalization will do to performance. Only through realistic testing can you know for sure if de-normalization will gain you anything in regards to performance.