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 2008 and cover features specific to that
version, but most of the information is relevant to SQL Server 2005 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 SQL Server 2008 Internals, 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:
http://sqlserverinternals.com/about.html
Course Outline
DAY 1
Module 1: SQL Server Architecture and Metadata
- Architecture Overview
- Metadata Overview
- Dynamic Management Views
- Memory Management
- Process Management
- The Resource Governor
- 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
- Sparse Columns
- Data Compression
DAY 2
Module 3: Logging and Recovery
- Structure of The Transaction Log
- Management of the Transaction Log
- Recovery and Recovery Models
Module 4: Index Structures and Partitions
- Heaps and B-Trees
- Clustered Indexes
- Nonclustered Indexes
- Fragmentation
- Rebuilding Indexes
- Partitioning Overview
- Metadata for Partitioning
DAY 3
Module 5: Query Processing and Query Plans
- SHOWPLAN
- Query Plan Elements
- Types of Joins
- Aggregation
- Sorting
- UNION
- 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
- XML Plans
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
- Database Snapshots
Module 8: Index Tuning
- Covering Indexes
- Included Columns
- Filtered Indexes
- General Indexing Strategies
DAY 5
Module 9: Query Tuning
- Query Improvements
- Search Arguments
- Constants and Variables
- Query Hints and Plan Guides
Module 10: Tracing and Troubleshooting
- Verifying Database Consistency with DBCC
- Maintenance Suggestions
- Tracing
- Other Tools for Tracking Down Problems