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

Transactional detail tables: These tables are the child-level transaction information for the
parent transactional header tables. A best practice design for these tables is to design them for
insert-only operations as much as possible. Provide state types to mark them as inert instead
of deleting rows during high availability periods. Rows marked in inert states can be more eas-
ily removed in offline periods with little impact prior to reindexing operations. If updates are
allowed to these tables, you'll need to look here for performance tuning as well.

❑

❑ Workflow tables: These tables represent event-based operations that are required during the

workflow of a business process. Different flavors of these types of tables include stateful work-
flow queues where the rows stay indefinitely and only a state is changed as an entity moves
through a business process. Another option is a net-zero workflow queue where the entity ID is
moved into the structure and once the action is taken by the process and verified then the entity
ID is removed from the queue structure. This is an enterprise development concept that isn't
represented in the AdventureWorks database.

Historical tables: These tables are INSERT mode only and contain audit or historical informa-
tion. Typically, these tables are populated by insert triggers from entity or transactional tables.

❑

System tables: These tables are seldom issues for performance tuning. They contain
administrator-level information like database version or settings to define processes. These tables
are very seldom altered.

❑

Once you have iterated through your logical or physical model and have categorized your tables, it is a
good practice to create a set of administration tables to store this metadata either directly in your database
or in a separate administration-only database. Later in this chapter, you'll use this type of information
to create benchmarks that enable you to evaluate the growth of the design structures as the database
moves through a full-development life cycle. You can use this format as an example of applying this
categorization to some of the tables in AdventureWorks.

Table 14-2: Separating Tables by Usage Type

AdventureWorks Table Type

SalesOrderHeader TRANSACTIONAL HEADER TABLE

SalesOrderHeaderSalesReason ASSOCIATION TABLE

SalesPerson MASTER DATA TABLE

SalesPersonQuotaHistory HISTORICAL TABLE

If you periodically store row counts for each of these tables, you'll be able to be proactive in performance
design and tuning. The results from the AdventureWorks analysis show only four tables that qualify
as high transactional tables: SalesOrderHeader, SalesOrderDetail, PurchaseOrderHeader, and Purchase-
OrderDetail. You should pay special attention to these tables for insert and update performance issues.
Other tables such as the TransactionHistoryArchive table contain high row counts, but are INSERT-based
only. You probably won't have write issues with this table but will probably need to watch the table for
read optimizations. However, before you can make that determination, there is one more analysis that
you need to make regarding use-case throughput.