Microsoft SQL Server 2000技术内幕

Foreword

Prefaces

System Requirements

Part 1 Overview

Chapter l The Evolution of Microsoft SQL Server: 1989 to 2000

SQL SERVER: THE EARLY YEARS

RON'S STORY

KALEN'S STORY

MICROSOFT SQL SERVER SHIPS

DEVELOPMENT ROLES EVOLVE

OS/2 AND FRIENDLY FIRE

SQL SERVER 4.2

OS/2 2.0 Release on Hold

Version 4.2 Released

SQL SERVER FOR WINDOWS NT

SUCCESS BRINGS FUNDAMENTAL CHANGE

THE END OF JOINT DEVELOPMENT

THE CHARGE TO SQL95

THE NEXT VERSION

THE SECRET OF THE SPHINX

SOFTWARE FOR THE NEW CENTURY

Chapter 2 A Tour of SQL Server

THE SQL SERVER ENGINE

Transact-SQL

DBMS-ENFORCED DATA INTEGRITY

Declarative Data lntegrity

Datatypes

CHECK Constraints and Rules

Defaults

Triggers

TRANSACTION PROCESSING

Atomicity

Consistency

Isolation

Durability

SYMMETRIC SERVER ARCHITECTURE

Traditional Process/Thread Model

SQL Server Process/Thread Model

Multiuser Performance

SECURITY

Monitoring and Managing Security

HIGH AVAILABILITY

DISTRIBUTED DATA PROCESSING

DATA REPLICATION

SYSTEMS MANAGEMENT

SQL Server Enterprise Manager

Distributed Management Objects

Windows Management Instrumentation

SQL-DMO and Visual Basic Scripting

SQL Server Agent

SQL SERVER UTILITIES AND EXTENSIONS

Web Assistant Wizard and Internet Enabling

SQL Profiler

SQL Server Service Manager

System Monitor Integration

Client Network Utility

Server Network Utility

SQL Server Installation

OSQL and ISQL

SQL Query Analyzer

Bulk Copy and Data Transformation Services

SNMP Integration

SQL Server Books Online

CLIENT DEVELOPMENT INTERFACES

ODBC

OLE DB

ADO

DB-Library

ESQL/C

Server Development Interface

SUMMARY

Part II Architectural Overview

Chapter 3 SQL Server Architecture

THE SQL SERVER ENGINE

The Net-Library

Open Data Services

The Relational Engine and the Storage Engine

The Access Methods Manager

The Row Operations Manager and the Index Manager

The Page Manager and the Text Manager

The Transaction Manager

The Lock Manager

Other Managers

MANAGING MEMORY

The Buffer Manager and Memory Pools

Access to In-Memory Pages

Access to Free Pages (Lazywriter)

Checkpoints

Accessing Pages Using the Buffer Manager

Large Memory lssues

The Log Manager

TRANSACTION LOGGING AND RECOVERY

Locking and Recovery

Page LSNs and Recovery

THE SQL SERVER KERNEL AND INTERACTION WITH THE OPERATING SYSTEM

Threading and Symmetric Multiprocessing

The Worker Thread Pool

Disk I/O in Windows NT/2000

SUMMARY

Part III Using Microsoft SQL Server

Chapter 4 Planning for and Installing SQL Server

SQL SERVER EDITIONS

Embedded SQL Server

HARDWARE GUIDELINES

Use Hardware on the Windows Hardware Compatibility List

Performance = Fn(Processor Cycles, Memory, I/O Throughput)

lnvest in Benchmarking

HARDWARE COMPONENTS

The Processor

Memory

Disk Drives, Controllers, and Disk Arrays

RAID Solutions

More About Drives and Controllers

Uninterruptible Power Supply

The Disk Subsystem

Fallback Server Capability

Other Hardware Considerations

THE OPERATING SYSTEM

THE FILE SYSTEM

SECURITY AND THE USER CONTEXT

LICENSING

SQL Server Processor License

Server Licenses and CALs

Multiplexing: Use of Middleware,Transaction Servers, and Multitiered Architectures

Multiple Instances

NETWORK PROTOCOLS

COLLATION

Character Sets

Sort Orders

MULTIPLE INSTANCES

Installing Named Instances

Named Instance Server Connectivity

INSTALLING SQL SERVER

Upgrading from a Previous Version

BASIC CONFIGURATION AFTER INSTALLATION

Starting the SQL Server Service

Changing the System Administrator Password

Configuring SQL Server's Error Log

Working with Multiple Instances

REMOTE AND UNATTENDED INSTALLATION

Remote Installation

Unattended Installation

Changing Installation Options

Adding Additional Components

SUMMARY

Chapter 5 Databases and Database Files

SPEClAL SYSTEM DATABASES

master

model

tempdb

pubs

Northwind

msdb

DATABASE FILES

CREATING A DATABASE

A CREATE DATABASE Example

EXPANDING AND SHRINKING A DATABASE

Automatic File Expansion

Manual File Expansion

Automatic File Shrinkage

Manual File Shrinkage

CHANGES IN LOG SlZE

Log Truncation

USING DATABASE FILEGROUPS

The Default Filegroup

A FILEGROUP CREATION Example

ALTERING A DATABASE

ALTER DATABASE Examples

DATASASES UNDER THE HOOD

Space Allocation

SETTING DATABASE OPTIONS

State Options

Cursor Optians

Auto Options

SQL Options

Recovery Options

OTHER DATABASE CONSIDERATIONS

Databases vs. Schemas

Using Removable Media

Detaching and Reattaching a Database

Compatibility Levels

BACKING Up AND RESTORING A DATABASE

Types of Backups

Recovery Models

Choosing a Backup Type

Restoring a Database

SUMMARY

Chapter 6 Tables

CREATING TABLES

Naming Tables and Columns

Reserved Keywords

Delimited Identifiers

Naming Conventions

Datatypes

Much Ado About NULL

USER-DEFINED DATATYPES

IDENTITY PROPERTY

INTERNAL STORAGE

Data Pages

Examining Data Pages

The Structure of Data Rows

Column Offset Arrays

Storage of Fixed-Length and Variable-Length Rows

Page Linkage

Text and Image Data

sql_variant Datatype

CONSTRAINTS

PRIMARY KEY and UNIQUE Constraints

FOREIGN KEY Constraints

Constraint-Checking Solutions

Restrictions on Dropping Tables

Self-Referencing Tables

CHECK Constraints

Default Constraints

More About Constraints

ALTERING A TABLE

Changing a Datatype

Adding a New Column

Adding Dropping, Disabling, or Enabling a Constraint

Dropping a Column

Enabling or Disabling a Trigger

TEMPORARY TABLES

Private Temporary Tables (#)

Global Temporary Tables (##)

Direct Use of tempdb

Constraints on Temporary Tables

SYSTEM TABLES

SUMMARY

Chapter 7 Querying Data

THE SELECT STATEMENT

JOINS

Outer Joins

The Obsolete *= OUTER JOIN Operator

Cross Joins

DEALING WITH NULL

NULL in the Real World

IS NULL and = NULL

SUBQUERIES

Correlated Subqueries

VIEWS AND DERIVED TABLES

Altering Views

Partitioned Views

OTHER SEARCH EXPRESSIONS

LIKE

BETWEEN

Aggregate Functions

Datacube——Aggregate Variations

TOP

UNION

SUMMARY

Chapter 8 Indexes

INDEX ORGANIZATION

Clustered Indexes

Nonclustered Indexes

CREATING AN INDEX

Constraints and Indexes

THE STRUCTURE OF INDEX PAGES

Clustered Index Rows with a Uniqueifier

Index Row Formats

INDEX SPACE REQUIREMENTS

B-Tree Size

Actual vs. Estimated Size

MANAGING AN INDEX

Types of Fragmentation

Detecting Fragmentation

Removing Fragmentation

SPECIAL INDEXES

Prerequisites

Indexes on Computed Columns

Indexed Views

USING AN INDEX

Looking for Rows

Joining

Sorting

Grouping

Maintaining Uniqueness

SUMMARY

Chapter 9 Modifying Data

BASIC MODIFICATION OPERATIONS

INSERT

UPDATE

DELETE

Modifying Data Through Views

DATA MODIFICATION INTERNALS

Inserting Rows

Splitting Pages

Deleting Rows

Updating Rows

Table-Level vs. Index-Level Data Modification

Logging

Locking

SUMMARY

Chapter 10 Programming with Transact-SQL

TRANSACT-SQL AS A PROGRAMMING LANGUAGE

Programming at Multiple Levels

TRANSACT-SQL PROGRAMMING CONSTRUCTS

Variables

Control-of-Flow Tools

CASE

PRINT

RAISERROR

FORMATMESSAGE

Operators

Scalar Functions

Table-Valued Functions

TRANSACT-SQL EXAMPLES AND BRAINTEASERS

Generating Test Data

Getting Rankings

Finding Differences Between Intervals

Selecting Instead of Iterating

FULL-TEXT SEARCHING

Full-Text Indexes

Setting Up Full-Text Indexes

Maintaining Full-Text Indexes

Querying Full-Text Indexes

Performance Considerations for Full-Text Indexes

SUMMARY

Chapter 11 Batches, Stored Procedures, and Functions

BATCHES

ROUTINES

STORED PROCEDURES

Nested Stored Procedures

Recursion in Stored Procedures

Stored Procedure Parameters

USER-DEFINED FUNCTIONS

Table Variable s

Scalar-Valued Functions

Table-Valued Functions

System Table-Valued Functions

Managing User-Defined Functions

REWRITING STORED PROCEDURES AS FUNCTIONS

ROLLING YOUR OWN SYSTEM ROUTINES

Your Own System Procedures

Your Own System Functions

EXECUTING BATCHES, OR WHAT'S STORED ABOUT STORED PROCEDURES (AND FUNCTlONS)?

Step One: Parse Commands and Create the Sequence Tree

Step Two: Compile the Batch

Step Three: Execute

Step Four: Recompile Execution Plans

Storage of Routines

Encrypting Routines

Altering a Routine

TEMPORARY STORED PROCEDURES

Private Temporary Stored Procedures

Global Temporary Stored Procedures

Procedures Created from Direct Use of tempdb

AUTOSTART STORED PROCEDURES

SYSTEM STORED PROCEDURES

General System Procedures

Catalog Stored Procedures

SQL Server Agent Stored Procedures

Replication Stored Procedures

Extended Stored Procedures

EXECUTE("ANY STRING")

SUMMARY

Chapter 12 Transactions and Triggers

TRANSACTIONS

Explicit and Implicit Transactions

Error Checking in Transactions

Transaction Isolation Levels

Other Characteristics of Transactions

Nested Transaction Blocks

Savepoints

TRIGGERS

After Triggers

Instead-of Triggers

Managing Triggers

Using Triggers to Implement Referential Actions

Recursive Triggers

SUMMARY

Chapter 13 Special Transact-SQL Operations:Working with Cursors and Large Objects

CURSOR BASICS

CURSORS ANO ISAMS

Problems with ISAM-Style Applications

CURSOR MODELS

Transact-SQL Cursors

API Server Cursors

Client Cursors

Default Result Sets

API Server Cursors vs. Transact-SQL Cursors

APPROPRIATE USE OF CURSORS

Row-by-Row Operations

Query Operations

Scrolling Applications

Choosing a Cursor

Cursor Membership, Scrolling, and Sensitivity to Change

WORKING WITH TRANSACT-SQL CURSORS

DECLARE

OPEN

FETCH

UPDATE

DELETE

CLOSE

DEALLOCATE

The Simplest Cursor Syntax

Fully Scrollable Transact-SQL Cursors

Concurrency Control with Transact-SQL Cursors

CURSOR VARIABLES

Obtaining Cursor Information

WORKING WITH TEXT AND IMAGE DATA

WRITETEXT

READTEXT

UPDATETEXT

SUMMARY

PartIV Performance and Tuning

Chapter 14 Locking

THE LOCK MANAGER

The Lock Manager and Isolation Levels

Spinlocks

Deadlocks

LOCK TYPES FOR USER DATA

Lock Modes

Lock Granularity

Lock Duration

Lock Ownership

Viewing Locks

LOCK COMPATIBILITY

INTERNAL LOCKING ARCHITECTURE

Lock Blocks

Lock Owner Blocks

Syslockinfo Table

BOUND CONNECTIONS

ROW-LEVEL VS. PAGE-LEVEL LOCKING

Lock Escalation

LOCKING HINTS AND TRACE FLAGS

SUMMARY

Chapter 15 The Query Processor

THE SQL MANAGER

COMPILATION AND OPTIMIZATION

Compilation

Optimization

How the Query Optimizer Works

Join Selection

Other Processing Strategies

Maintaining Statistics

THE PROCEDURE CACHE

USING STORED PROCEDURES AND CACHING MECHANISMS

Ad Hoc Caching

Autoparameterization

The sp_executessq/Procedure

The Prepare and Execute Method

Sharing Cached Plans

Examining the Plan Cache

Multiple Plans in Cache

When to Use Stored Procedures and Other Caching Mechanisms

Recompiling Stored Procedures

Other Benefits of Stored Procedures

EXECUTION

SUMMARY

Chapter 16 Query Tuning

THE DEVELOPMENT TEAM

APPLICATION AND DATABASE DESIGN

Normalize Your Database

Evaluate Your Critical Transactions

Keep TabIe Row Lengths and Keys Compact

PLANNING FOR PEAK USAGE

PERCEIVED RESPONSE TIME FOR INTERACTIVE SYSTEMS

PROTOTYPING, BENCHMARKING, AND TESTING

Development Methodologies

CREATING USEFUL INDEXES

Choose the Clustered Index Carefully

Make Nonclustered Indexes Highly Selective

Tailor Indexes to Critical Transactions

Pay Attention to Column Order

Index Columns Used in Joins

Create or Drop Indexes as Needed

The Index Tuning Wizard

MONITORING QUERY PERFORMANCE

STATISTICS IO

STATISTICS TIME

Showplan

Using Query Hints

Stored Procedure Optimization

CONCURRENCY AND CONSISTENCY TRADEOFFS

RESOLVING BLOCKING PROBLEMS

Indexes and Blocking

RESOLVING DEADLOCK PROBLEMS

Cycle Deadlock Example

Conversion Deadlock Example

Preventing Deadlocks

Handling Deadlocks

Volunteering to Be the Deadlock Victim

Watching Locking Activity

Identifying the Culprit

Lock Hints

SEGREGATING OLTP AND DSS APPLICATIONS

ENVIRONMENTAL CONCERNS

Case Sensitivity

Nullability and ANSl Compliance Settings

Locale-Specific SET Options

SUMMARY

Chapter 17 Configuration and Performance Monitoring

OPERATING SYSTEM CONFIGURATION SETTINGS

Task Management

Resource Allocation

PAGEFILE.SYS Location

File System Selection

Nonessential Services

Network Protocols

SQL SERVER CONFIGURATION SETTINGS

Serverwide Options

Buffer Manager Options

Startup Parameters on SQLSERVR.EXE

SYSTEM MAINTENANCE

MONITORING SYSTEM BEHAVIOR

SQL Profiler

System Monitor

Other Performance Monitoring Considerations

SUMMARY

Bibliography and Suggested Reading

Index