Description
This five-day course focuses on performance, design, and availability, leveraging internals to help you get the most out of SQL Server 2005 and 2008. After taking this course you will have a thorough understanding of how to keep your databases up, running, and ready to go wherever your business needs.
Day 1: SQL Server Internals
On-disk structures: how the data is stored
Index internals: how the data is organized
Logging and recovery: how the data is protected
Day 2: Designing for Performance
- Choosing the RIGHT Data Type
- Understanding data types
- Character data, overflow and LOB
- LOB considerations
- Date and time data types
- Heterogeneous data types
- Sparse columns
- Application inconsistencies in types
- Designing for Performance
Table & Index Partitioning Strategies - Concepts/motivating factors in Partitioning
- SQL Server 2000+ Partitioned Views
- SQL Server 2005+ Partitioned Tables
- Partitioning Design Techniques Combined
- Partitioned and non-partitioned Indexes
- Implementing the Sliding Window Scenario
Day 3: Indexing for Performance
- Data Access
- Data Access Patterns
- Covering
- Using INCLUDE (SQL Server 2005+)
- Using Filters (SQL Server 2008+)
- Indexing Strategies
- Indexing for AND
- Indexing for OR
- Indexing for Joins
- Indexing for Aggregates
- Indexed Views (Overview – detailed if time permits)
Day 4: Essential Database Maintenance
- Data and log file provisioning and management
- Database layout considerations
- Growing and shrinking
- Instant initialization
- Tempdb
- Index and statistics maintenance
- Fragmentation and its causes
- Detecting and removing
- Using backup and restore
- Types of backups
- Backup strategy
- How restore works
- Restore options
Day 5: Essential Database Maintenance
- Consistency checking and disaster recovery
- Page protection options
- IO errors and monitoring
- DBCC CHECKDB
- Best practices
- Interpreting output
- Recovering from corruption