SQL Server 2000/2005: Developing High-Performance Databases

Course Overview

To fully exploit the powerful features of Microsoft SQL Server, developers and designers must acquire an in-depth knowledge of SQL Server's storage engine, query optimiser and lock manager.

This course provides the details of SQL Server's internal architecture. Through hands-on exercises, you gain the skills needed to achieve a highperformance SQL Server database solution.

Audience

This course is valuable for those responsible for increasing the performance and efficiency of SQL Server databases. Course 532, " SQL Server 2000 Transact-SQL Programming ", or Course 133, " SQL Server 2005 Server-Side Programming ", is assumed. Familiarity with logical database design is also assumed.

Skills Gained

• Design and implement high-performance databases for SQL Server 2005 and 2000
• Create indexes that optimise different types of queries
• Design transactions that maximise concurrency and minimise contention
• Interpret the data access plans produced by the query optimiser
• Minimise I/O by designing efficient physical data structures
• Improve response time by introducing controlled redundancy
• Analyse and cure performance problems using SQL Server's tools

Course Outline

Fundamental Concepts

Analysing performance

• Selecting an appropriate monitoring tool
• Investigating plans with SHOWPLAN_ALL
• Interpreting STATISTICS output
• Pinpointing performance problems with aggregated Profiler data

Developing a monitoring plan

• Establishing a performance baseline
• Tracking changes over time
• Creating server-side Profiler traces
• Monitoring SQL Server and the operating system with System Monitor

Managing Storage

Database architecture

• Page and extent allocation
• Controlling data placement with file groups

Defining tables

• Selecting the correct data types
• Specifying text and image locations
• Examining internal page structures

Creating and managing indexes

• Clustered vs. non-clustered
• Defining indexed views
• Analysing and repairing fragmentation

Memory and Locking

Managing memory

• Buffer pool
• Buffer manager
• Lazywriter
• Checkpointing
• Log writer

Designing transactions

• Consistency vs. concurrency
• Investigating lock types and their compatibility
• Choosing isolation levels
• Designing transactions to limit lock duration
• Supporting optimistic concurrency

Optimising Queries

Query optimiser architecture

• Phases
• Strategies
• Data access plans
• Auto-parameterisation
• Avoiding continuous recompilation of dynamic queries

Maintaining up-to-date statistics

• Index vs. column
• Automatic vs. manual
• Full-scan vs. sample

Distinguishing among query types

• Point
• Multipoint
• Range
• Prefix match
• Extremal
• Ordering
• Grouping
• Join

Designing effective indexes

• Providing alternate access paths
• Improving join performance
• Increasing sort efficiency
• Reducing I/O with covering indexes
• Getting design advice from built-in tuning tools

Designing a Physical Data Model

Storing summarised data for faster retrieval

• Defining roll-up tables
• Materialising aggregated data with indexed views

Minimising response time by introducing redundant data

• Maintaining redundant data with triggers
• Enhancing performance with surrogate keys

Solving performance problems by redefining the database structure

• Partitioning data
• Creating a read-only query database

Scaling out with distributed partitioned views

• Designing a federated database
• Implementing a routing strategy for updates

SQL Server 2000/2005: Developing High-Performance Databases / MSQ-DH