|
|
Class Outline*
|
|
| Request Info | Contact Us
|
|
SQL Server 2008 Architecture, Internals, and Tuning
Duration:
5 Days
Overview:
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 covers 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.
About the Author:
Kalen is one of the world's leading experts in SQL Server internals. She's the author of the bestselling book SQL Server 2008 Internals (published in 2009), as well as 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 been successfully delivered at internal Microsoft training events and as on-site courses for premier Microsoft partners around the world.
Objectives:
At the end of this course, the student will be able to:
- 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 if concurrency issues are causing problems with performance and troubleshoot those problems.
- Use the SQL Server 2008 Tracing and Auditing Tools to determine how SQL Server is performing, and to use those tools as a basis for improving performance.
Audience:
Experienced SQL Server professionals
Prerequisites:
Before attending this course, students should have a good understanding and practical experience of SQL Server programming and administration. This is an advanced seminar designed for advanced SQL Server professionals.
|
|
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 Configuration Tools
|
|
Module 2: File and Table Structures
|
- Tools for Examining Physical Structures
- Database Files and Space Allocation
- Table and Page Structures
- Large Data 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
- Creating and Maintaining Partitions
- Metadata for Partitioning
|
|
Day 3
|
|
Module 5: Query Processing and Query Plans
|
- SHOWPLAN
- Query Plan Elements
- Types of Joins
- Aggregation
- Sorting
- Unions
- Data Modification
|
|
Module 6: Optimization and Recompilation
|
- Query Processing Overview
- Index and Column Statistics
- 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 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: Maintenance and Troubleshooting
|
- Verifying Database Consistency with DBCC
- Maintenance Suggestions
- Tracking Down Problems
o Tracing
o Extended Events
o Management Data Warehouse
|
|
*Content, days, and times vary depending on your location. Please view the outline prior to purchase or contact the local center for more information.
|