home | the book | the author | schedule | error log | q&a | my blog | recommended reading | feedback form

SQL Server 2005
Architecture, Internals and Tuning

Description

This is an advanced 5-day course designed for advanced SQL Server professionals. The course includes extensive demonstrations that illustrate the details of SQL Server internals as well as tuning techniques. This course will be presented on SQL Server 2005 and cover features specific to that version, but much of the information is relevant to SQL Server 2000 as well.

This 5-day intensive seminar was developed by one of the world's leading experts in SQL Server internals. Topics to be covered include the following:

• SQL Server Architecture and Metadata
• Physical Storage Structures
• Index Design and Tuning
• Query Processing and Query Plans
• Optimization and Recompilation
• Concurrency Control: Locking, Blocking and Row Level Versioning
• Query Tips and Techniques
• Troubleshooting Tools

The course includes extensive demonstrations that illustrate the details of SQL Server internals as well as performance tuning techniques. A reference CD including all of the demonstration scripts as well as dozens of whitepapers and useful tools will be distributed at the end of the week.

About the Author

The author of the course is Kalen Delaney, author of the bestselling books Inside SQL Server 2000, Inside SQL Server 2005: The Storage Engine and Inside SQL Server 2005: Query Tuning and Optimization. Kalen has been a columnist at SQL Server Magazine since the first issue, as well as writing for MSDN and TechNet Magazines. Kalen's courses and seminars on SQL Server Internals have successfully delivered at internal Microsoft training events and as on-site courses for premier Microsoft partners around the world. More information about Kalen is available at her web site: www.InsideSQLServer.com

Course Outline

DAY 1

Module 1: SQL Server Architecture and Metadata

• Architecture Overview
• Metadata Overview
• Dynamic Management Views
• Memory Management
• Process Management
• SQL Server 2005 Configuration Tools

Module 2:  File and Table Structures

• Tools for Examining Physical Structures
• Database Files and Space Allocation
• Table and Page Structures
• Large Datatype Storage

DAY 2

Module 3: Logging and Recovery

• Structure of The Transaction Log
• Management of the Transaction Log
• Recovery and Restore
• Recovery Models

Module 4: Index Structures and Partitions

• Heaps and B-Trees
• Clustered Indexes
• Nonclustered Indexes
• Fragmentation
• Statistics
• Rebuilding Indexes
• Partitioning Overview
• Creating and Maintaining Partitions
• Metadata for Partitioning

DAY 3

Module 5: Query Processing and Query Plans

• Querying Tools
• SHOWPLAN
• Query Plan Elements
• Types of Joins
• Aggregation
• Sorting
• Data Modification

Module 6: Optimization and Recompilation

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

DAY 4

Module 7: Concurrency Control

• Optimistic and Pessimistic Concurrency
• ANSI SQL Isolation Levels
• Pessimistic Concurrency Control with Locking
• Aspects of Locking
• Locking Resources
• Controlling Locking
• Locking Metadata
• Optimistic Concurrency Control with Row Versioning
• Snapshot Isolation
• Snapshot Isolation Metadata

Module 8: Index Tuning

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

DAY 5

Module 9: Query Tuning

• Query Improvements
• Search Arguments
• Constants and Variables
• User Defined Functions and Computed Columns
• Plan Guides
• Query Hints

Module 10: Tracing and Troubleshooting

• SQL Profiler and Server-side Tracing
• Verifying Database Consistency
• Database Snapshots
• Maintenance Suggestions
• Tracking Down Problems