Datasheet

Chapter 1 ✦ The Information Architecture Principle

Chapter 20, “Kill the Cursor!” explains how to create dramatic performance gains by refac-

toring complex logic cursors into set-based queries.

Indexing

Indexing is the performance bridge between queries and data and a key performance strat-

egy. An indexing strategy that uses a clustered index to reduce bookmark lookups or group

rows to a single data page, uses nonclustered indexes to cover queries and enable seeks, and

avoids redundant indexes will speed set-based code. But well-designed indexes can’t over-

come nonscalable iterative code.

Designing clustered, nonclustered, and covering indexes are explained in detail in Chapter

50, “Query Analysis and Index Tuning.”

Concurrency

Locking and blocking is more common a problem than most developers think, and too many

DBA solve the problem by lowering the transaction isolation level using

nolock — and that’s

dangerous.

Concurrency can be compared to a water fountain. If folks are taking long turns at the foun-

tain or filling buckets, a line may form, and those waiting for the resource will become frus-

trated. Setting

nolock is like saying, “Share the water.” A better solution is to satisfy the

needs with only a sip or to reduce the duration or the transaction. The best way to develop

efficient transactions is to design efficient schemas, use set-based code, and index well.

When the schema, queries, and indexes are already reducing the transaction duration, be

sure to place only the required logic within logical transactions and be careful with logic

inside triggers, since they occur within the transaction. But reducing blocked resources won’t

overcome an unnecessary table scan.

Advanced Scalability

When the schema, queries, indexes, and transactions are all running smooth, you’ll get the

most out of SQL Server’s high-end scalability features:

✦ Snapshot Isolation

✦ Partition Tables

✦ Index Views

✦ Service Broker

Will you see performance gains by using a performance technique without the enabling tech-

nologies in place? Maybe. But you’ll see the greatest gains by enabling each layer with its

enabling technologies.

Optimization Theory, the idea that there are dependencies between performance technolo-

gies, is an evolving concept. For my latest data on Optimization Theory, or my latest

Performance Decision presentation, visit www.SQLServerBible.com.

Cross-

Reference

Cross-

Reference

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