Microsoft® SQL Server® 2012 Bible

27

Chapter 2: Data Architecture

2

A database with an excellent physical schema, well-written set-based queries, and the right

set of indexes will have tight transactions and perform well with multiple users.

When a poorly designed database displays symptoms of locking and blocking issues,

transaction isolation level tuning only partially alleviates the problem. The sources of the

concurrency issue are the long transactions and additional workload caused by the poor

database schema, lack of set-based queries, or missing indexes. Concurrency tuning cannot

overcome the defi ciencies of a poor database design.

Advanced Scalability

With each release, Microsoft has consistently enhanced SQL Server for the enterprise. These

technologies can enhance the scalability of heavy transaction databases.

The Resource Governor can restrict the resources available for different sets of queries,

enabling the server to maintain the service-level agreement (SLA) for some queries at the

expense of other less critical queries.

Indexed views were introduced in SQL Server 2000. They actually materialize the view as a

clustered index and can enable queries to select from joined data without hitting the joined

tables, or to pre-aggregate data. In effect, an indexed view is a custom covering index that

can cover across multiple tables.

Partitioned tables can automatically segment data across multiple fi legroups, which can

serve as an auto-archive device. By reducing the size of the active data partition, the

requirements for maintaining the data, such as defragging the indexes, are also reduced.

Service Broker can collect transactional data and process it after the fact, thereby provid-

ing an “over time” load leveling as it spreads a 5-second peak load over a 1-minute execu-

tion without delaying the calling transaction.

Column store indexes, introduced in SQL Server 2012, are column-based indexes (rather

than traditional row-based indexes) that can greatly improve query speed in certain data-

base environments.

Chapter 45, “Indexing Strategies,” covers column-based indexes in more depth.

Although these high-scalability features can extend the scalability of a well-designed

database, they are limited in their ability to add performance to a poorly designed data-

base, and they cannot overcome long transactions caused by a lack of indexes, iterative

code, or all the multiple other problems caused by an overly complex database design.

The database component is the principle factor determining the overall monetary cost of

the database. A well-designed database minimizes hardware costs, simplifi es data access

c02.indd 27c02.indd 27 7/30/2012 4:07:53 PM7/30/2012 4:07:53 PM

http://www.it-ebooks.info