|
Microsoft SQL Server 2005 Architecture, Internals and Tuning - Master Class
Class Scheduling
This class is currently taught as both a public and private class. Information on public class schedules can be found on Kalen's website. Contact us for more information on private classes.
Overview
Master Classes are developed and delivered by the world's foremost experts in their fields. Attending a Master Class means that you will enjoy access to the most advanced courseware available in the world on a particular subject. The designation of an instructor for a Master Class means that you are receing instruction from one of the top experts in the industry on the given subject. You will not find better training anywhere in the world.
This course draws on 20 years of practical experience in SQL Server architecture, internals, and query tuning providing up to date information, not theory. Topics covered in this class:
- 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
Want a customized version of this course which will delve more deeply into specific topics of interest in your company? Join the dozens of other companies who have selected this option and contact us. We will be more than happy to provide the customized, personalized, private training that you desire.
Instructors
The author and primary instructor of this course is Kalen Delaney, author of the best selling book 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 been 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
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.
- 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.
- This 5-day intensive seminar was developed by one of the world's leading experts in SQL Server internals, Kalen Delaney.
- This course is available for private onsite classes as well. Request information about onsite private classes using this course.
Audience
This course is intended for experienced DBAs and database developers who are seeking a deeper understanding of SQL Server internals and archiecture along with advanced techniques for query tuning.
Comments from Attendees
Prerequisites
This course requires that students meet the following prerequisites:
- Experience with either SQL Server 2000 or SQL Server 2005
Course Objectives
After completing this course, the student will be able to:
- Describe data and log file structures
- Retrieve information directly from data pages on disk
- Describe index and index structures
- Generate and interpret query plans
- Understand internal operations of the optimizer
- Effectively apply concurrency and transaction isolation
- Design appropriate indexing strategies
- Manipulate query parameters for optimal performance
- Trace, analyze, and identify the sources of poor query performance
Course Outline
- Module 0: Introductions and Course Overview
- 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
- 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
- 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
- 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
- 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
|
