Professional SQL Server® 2005 Performance Tuning

14.2. Evaluating Schemas for Performance Unknowns

It would be ideal if you can be involved early on in an application design process to gather the performance requirements prior to an implementation. This is not always possible, and sometimes you are handed logical models and are expected to implement them. Without this inside knowledge, designing for performance is a challenge for sure, but surprisingly you can get close if you follow a few basic common-sense guidelines.

• Design for simplicity: Anything you can think of that is considered well-designed is reliable, easy-to-use, easy-to-maintain, and amazingly simple. When you experience a well-designed thing, you think to yourself, "Why didn't I think of that?" This same idea should be applied when building applications. If a table design is too complicated to explain and is clearly not aligned to the business model, then no matter how "neat" it is, it is poorly designed and is bound to create performance issues.

• Design with third normal form in mind: Research shows that third normal form is generally the place where performance maximums are met. Go back to Chapter 8 if you are unsure how to determine whether a database is in this form.

• Design using the single-responsibility principle: This is an object-oriented concept that is extremely relevant to database design. Each data structure should have a single responsibility. Each data column should have a single purpose. Violating this principle is asking for trouble. Not only is the ...