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

Another component of uptime requirements is asking how much data is required to be stored. How long
does it need to be online, near-online and offline? You'll notice that highly transactional systems used
by banks to provide checking and savings transactions typically restrict certain expensive transactions
to a date and time limits. Retrieval of online check copies or statements is an example of this type of
restriction. This is optimal from a performance standpoint, but can be frustrating for a user if they are
running behind balancing the family checkbook and can't easily get to older data. American Express
handles this by showing only the last few months of statements online. However, you can get archived
copies moved online, by using a request process that takes only a few hours or a day at most. This is
an example of a smart compromise. The uptime requirement is met without incurring all the storage or
transactional costs. Look at this type of solution for your uptime requirements while asking the question
of how much is really needed to be online.

What Is theRequired Throughput of Business
Transactions?
One of the key responsibilities of a DBA or architect managing a database is to understand the throughput
of the core business transaction performed on a routine basis. With this understanding, you'll be able to
zero in on a large physical model and focus on the high-impact areas for performance improvement. To
get the full picture, you need to identify the core tables in the database by usage type and then apply
some benchmark numbers through a data-flow analysis.

IdentifyingCoreTablesbyUsageType
If you are lucky to be involved in the design decisions early enough, get a handle on the logical model
and provide input. If not, get caught up. It is easier to see normalization issues at this altitude, and this
should be the starting point for any performance analysis. What you are looking for here is a rate of
growth. After gaining some experience with different project logic models, you'll start to notice that
tables in the logical model can be separated and viewed in these categories.

Type tables: Type tables are simple representation of enumerations. These tables contain an ID
and a description field. Although these tables are small, they will be folded into many queries for
UI and reporting purposes to translate the ID that is typically stored in an entity or transactional
table type. If you are tempted to create one monolithic type table, see the section later in this
chapter on single-use schemas.

❑

Master data tables: These tables are the structures that usually represent master data concepts.
Examples from AdventureWorks database are the Department, Employee, and Shift. Master data
tables have many attributes that are physically represented by a few common models. Although
it is possible to have applications that have large numbers of these structures, entry requirements
are typically low volume and reflect a slow rate of growth.

❑

❑ Association tables: Association tables are structures that glue entities together. These tables hold

identifying keys from each entity and little else. The ProductDocument and StoreContact tables
are examples of this type in the AdventureWorks database.

❑ Transactional header tables: These table types contain root-level information about transac-

tions. These are the parent tables to high-volume transactional detail tables and can contain state
information as well as summary information for the child detail transactions. These tables are
generally your hotspots because they experience updates as well as steady retrieval since these
are the topmost node in a hierarchy.