Best Practices for Designing for Performance from the Start - Professional SQL Server 2005 Performance Tuning - page 506

SimpleSchemas
Keep it simple. We have a rule in our shop that if looking at a model requires you to slow down and
study it the model probably needs to be rethought. This is not to say that you can always simplify every
business problem into a handful of patterns, but it does mean that you can go overboard trying to come
up with something original. Put new models to a sniff test with your team or user group and see how
much you have to defend the model. If everyone understands it and it meets the business requirements,
then you're probably okay.

Run the profiler to capture TSQL statements that are being submitted during the heavy usage periods.
If you see TSQL statements that use too many case statements or look overly complicated, then the
statement may be bad, or it may be an indication that the underlying structure or design is just not right.
Developers are problem solvers who will rise to a challenge to find a solution — even though the solution
may be detrimental to database performance. If the statement starts to create blocking or long-running
situations, you should be picking this up during your benchmarking processes. We'll go over how to
start some of these metrics later in the benchmarking section.

Single-Use Tables
Design tables that have single uses. This comes from an object-oriented concept called the single-
responsibility principle . The idea is that each table in the system should have a single responsibility and
that's it. You can see violations of this in places like the type tables. Instead of creating a unique type table
for each type, you'll see a logical design for one type table that has a type within itself. Undoubtedly,
someone looked at the type table structure (that is typically an identifier and a description) and thought
how convenient it would be from a software perspective to create only one object capable of performing
maintenance on one structure instead of a proliferation of type tables. An example of the two methods is
shown in Figure 14-1.

For Convenience

ForPerformance

StatusType

TypeTableType

100001 - Status Type
100002 - Product Type

PK StatusTypeld

PK

TypeTableTypeld

StatusTypeDesc
CreationDateTime

100003 - Contract Type

TypeTableTypeDesc
CreationDateTime

ProductType

PK ProductTypeld

ProductTypeDesc
CreationDateTime

TypeTable

PK Typeld

TypeDesc
CreationDateTime
TypeTableTypeld

ContractType

FK1

PK ContractTypeld

ContractTypeDesc
CreationDateTime

Figure 14-1