| SQLSoft Course SQL-243 SQL Server 2008 Architecture, Internals and Optimization |
|
Click on a date to register
|
 |
Live@DL |
SQL-243 -
5 days -
Instructor-led (classroom)
SQL Server 2008 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 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.
About the Author
The author of the course is Kalen Delaney, author of the best selling book Inside SQL Server 2005 and SQL Server 2008 Internals 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 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 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 intermediate TSQL language skills and knowledge of SQL Server 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
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
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
Return to Top
|