Audience
Experienced SQL Server professionals.
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.
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
- Tracing
- Extended Events
- Management Data Warehouse