| SQLSoft Course SQL-242 SQL Server 2005 Architecture, Internals and Optimization |
|
Click on a date to register
|
 |
Live@DL |
SQL-242 -
5 days -
Instructor-led (classroom)
SQL Server 2005 Architecture, Internals and Tuning
Price: 3,950
Table of Contents
Introduction
Audience
At Course Completion
Prerequisites
Microsoft Certified Professional Exams
Student Materials
Course Outline
Introduction
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. Optional hands-on
labs are available.
Note: Kalen Delaney is currently working with the new SQL Server 2008 product and will be able to talk about changes and enhancements. Those who attend this class will receive a resource CD containing dozens of scripts, tools and whitepapers, some of which are written specifically for SQL Server 2008. In addition, students who take this class through the end of 2008 will receive a copy of Kalen's book, "Inside SQL Server 2005: The Storage Engine".
About the Author
The author of the course is Kalen Delaney, author of the best selling book
Inside SQL Server 2000 and Inside SQL Server 2005 (coming soon). 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.
Return to Top
Audience
Advanced SQL Server professionals.
Return to Top
At Course Completion
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 2005 Dynamic Management Objects to determine SQL Server's
internal behavior.
- Read graphical- and text-based 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 2005 Dynamic Tools such as Profiler to determine how SQL Server is performing, and to use those tools as a basis
for improving performance.
Return to Top
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.
Return to Top
Microsoft Certified Professional Exams
No MCP exam currently exists for this course.
Return to Top
Student Materials
The student kit includes a comprehensive workbook and other necessary materials
for this class.
Return to Top
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
- Datatype Internals
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
- 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
- Indexed Views
- Processing Partitions
- Processing CLR Queries
- Data Modification
Module 6: Optimization and Recompilation
- Query Processing Overview
- SQL Server’s Query Optimizer
- Plan Management and Reuse
- Causes of Recompilation
- Forcing Recompilation
- Optimizer Metadata
- Optimization Hints
- Plan Guides
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
- SQL Server Features Using Row Versioning
- Database Snapshots
Module 8: Index and Query Tuning
- Indexing Guidelines
- Covering Indexes
- Included Columns
- General Strategies
Day 5
Module 9: Query Tuning
- Query Improvements
- Search Arguments
- Constants and Variables
- User Defined Functions and Computed Columns
- Other Tuning Suggestions
Module 10: Troubleshooting and Maintenance
- Verifying Database Consistency
- Managing Tempdb
- Maintenance Suggestions
- Performance Monitor
- Tracing SQL Server 2005
- Tracking Down Problems
- Database Tuning Advisor
Return to Top
|