SQL Server 2008 Architecture Internals and Optimization

• Examine details of SQL Server's internal structures to determine their impact on query performance.
• Use the SQL Server 2008 Dynamic Management Objects to determine SQL Server's internal behavior.
• Read query plans to determine where a poorly performing query needs tuning.
• Determine when plan caching is beneficial and when recompilation will give better performance.
• Learn techniques for building the best indexes for your queries, and ways to make sure your queries can take advantage of those indexes

Part 1: SQL Server Metadata

Architecture Overview
Metadata Overview
Dynamic Management Views
SQL Server 2005 Configuration Tools

Part 2: Index Structures and Index Tuning

Metadata for Storage
Space Allocation
Tools for Examining Physical Structures
Heaps and B-Trees
Clustered Indexes
Nonclustered Indexes
Fragmentation
Filtered Indexes
Rebuilding Indexes
Partitioning Overview
Creating and Maintaining Partitions
Metadata for Partitioning

Part 3: Query Processing and Query Plans

SHOWPLAN Options
Query Plan Elements
Types of Joins
Aggregation
Sorting
Data Modification

Part 4: Optimization and Recompilation

Optimization Overview
SQL Server’s Query Optimizer
Plan Management and Reuse
Causes of Recompilation
Forcing Recompilation
Optimizer Metadata

Part 5: Index Tuning

Special Index Features
Indexed Views
Covering Indexes
Filtered Indexes
Included Columns
Indexing Guidelines

Part 6: Query Tuning

Query Improvements
Search Arguments
Constants and Variables
User Defined Functions and Computed Columns
Plan Guides
Query Hints
Tracing Query Performance
Management Data Warehouse