Fundamental Concepts

Analysing performance

• Selecting an appropriate monitoring tool
• Investigating plans with SHOWPLAN_ALL
• Interpreting STATISTICS IO 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. nonclustered
• Defining indexed views
• Analysing and repairing fragmentation

Memory and Locking

Managing memory

• Buffer pool
• Buffer manager
• Lazywriter
• Checkpoint
• Log writer

Designing transactions

• Consistency vs. concurrency
• Investigating lock types and their compatibility
• Choosing isolation levels
• Designing transactions to limit lock duration
• Resolving contention problems
• Handling deadlock
• Implementing row versioning

Optimising Queries

Query optimiser architecture

• Phases
• Strategies
• Data access plans
• Auto-parameterisation
• Avoiding recompilation of 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

• Relating indexes to query types
• Providing alternate access paths
• Improving join performance
• Increasing sort efficiency
• Reducing I/O with covering indexes
• Implementing sparse 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
• Creating a read-only query database

Solving performance problems with partitioning strategies

• Horizontal vs. vertical partitioning
• Partitioning tables
• Defining partitioned views

Managing diverse workloads

• Creating resource pools and workload groups
• Developing a classifier function