Datasheet

Chapter 1 ✦ The Information Architecture Principle

Compliance with Sarbanes-Oxley not only requires that data is secure, but that any changes

in data ownership and access restrictions are documented.

Audit Trails

Audit trails identify the source and time of creation and any updates. At the minimum level,

the audit trail captures the created date/time, and the last updated date/time. The next level

of auditing captures the username of the person or process who created or updated the data.

A complete audit trail records every historical value. The ability to recreate the data at any

given point in time is important for temporal, or time-based, data.

Chapter 24, “Exploring Advanced T-SQL Techniques,” includes methods of creating audit

trails.

Optimization Theory and SQL Server

Ask 20 DBAs for their favorite optimization technique or strategy, and you’ll likely hear 40 dif-

ferent answers ranging from indexing to adding memory. Is it possible to order this heap of

performance ideas? I believe so. Data modeling is essentially puzzling out the pattern of data.

So is there is a pattern to the various strategies that can revealed by examining how one

strategy affects another?

The first clue is that not all performance strategies perform well or uniformly because there’s

an inherent dependency between performance strategies that’s easily overlooked. For

instance, using indexing to improve the performance of a query reduces the duration of a

transaction, which facilitates concurrency. So there’s some kind of connection between index-

ing and concurrency. Maybe there’s more. Certain performance strategies enable other strate-

gies while some performance strategies have little effect if other strategies have not already

been applied.

Optimization Theory addresses these dependencies and provides a framework for planning

and developing an optimized data store. Optimization Theory identifies five key optimization

strategies (see Figure 1-3). Each strategy has several specific techniques. Each strategy is

enabled by its supporting strategy, and no strategy can overcome deficiencies in their sup-

porting strategies.

Schema Design

Schema Design is my number-one performance strategy. Well-designed schemas enable you

to develop set-based queries and make it easier to plan effective indexes.

To design an effective schema, you need to do the following:

✦ Avoid overcomplexity.

✦ Select a key carefully.

✦ Handle optional data.

✦ Enforce an abstraction layer.

I believe that the purist logical data modeler is the number-one performance problem in our

industry because of the cascading problems caused by his burdensome designs.

Cross-

Reference

Note

05_542567 ch01.qxp 9/27/06 9:58 PM Page 21