Module 1: Database Structures
The fundamental building block of knowledge is how SQL Server stores data on disk and this module will cover all structures in a database. Topics covered include:
- Records
- Pages
- Extents
- Allocation bitmaps
- Allocation units and IAM pages
- Using DBCC PAGE
Module 2: New Database Structures in SQL Server 2008
SQL Server 2008 added three new structures that deserve special consideration as to how their structures differ from anything previously and their potential impact if used. Topics covered include:
- Data compression
- Sparse columns
- FILESTREAM
Module 3: Data File Internals and Maintenance
Although this may seem like a simple topic, there are many things to consider around data file configuration and management, especially for tempdb. Topics covered include:
- Data file and filegroup layout
- Allocation algorithms
- Data file growth and shrinking
- Instant file initialization
- Tempdb
- Monitoring and troubleshooting
Module 4: Logging, Recovery, and Log File Internals and Maintenance
Logging and recovery underpins SQL Server's ability to recover after a crash and all high-availability and disaster recovery technologies. It can also have a profound effect in the performance of a workload. This module will explain these topics in great depth. Topics covered include:
- Logging and log records
- Analyzing transaction log contents
- Checkpoints and crash recovery
- Transaction log logical and physical architecture
- Recovery models and their effects
- Log file maintenance
- Monitoring and troubleshooting
Module 5: Locking and Blocking
Locking and blocking both happen all the time, but very often blocking and deadlocks lead to performance and application problems. This module will explain the various aspects of locking and how to troubleshoot and avoid problems. Topics covered include:
- Locking
- Lock escalation
- Blocking
- Deadlocks
- Monitoring and troubleshooting
- Latches
Module 6: Snapshot Isolation
In many systems today, real-time analysis is required – often at the expense of OLTP activity. And, when significant OLTP activity is present, real-time analysis is prone to inconsistencies. After discussing locking and blocking, we will move to discussing statement-level and transaction-level inconsistencies, when they can occur, how to minimize/eliminate them using locking (at the expense of concurrency) and then how to use versioning to get the best of both worlds (concurrency and accuracy). But, at what expense? The primary focus of this module is to discuss when using these isolation levels is appropriate, what the possible trade-offs are, and what are the best practices for using versioning. Topics covered include:
- Mixed workload challenges
- Understanding isolation levels
- Controlling isolation levels
- Statement-level read consistency
- Transaction-level read consistency
Module 7: Table Design Strategies and Partitioning
After having spent time on internals, we will apply them directly to best practices in table design. Primarily we will discuss the use and benefits of vertical partitioning as well as horizontal partitioning. Topics covered include:
- Understanding row width (vertical partitioning)
- Character data, overflow and LOB
- LOB considerations
- Application inconsistencies in types
- Horizontal partitioning strategies (partitioned views and partitioned tables)
- Implementing the sliding window scenario
- Partitioning design techniques combined
- Partition-aligned indexed views
Module 8: Index Internals
Taking your design further, we will now consider our base table strategies in indexes. This is fundamentally one of the most misunderstood and improperly handled areas of SQL Server. This first module will explain the internals of both the clustered and nonclustered indexes and will be the foundation for the remainder of the class. Topics covered include:
- Index concepts
- Table structure
- Heaps
- Clustered indexes
- Nonclustered indexes
- Index internals case study
Module 9: Internals and Data Access
Understanding the options that SQL Server has for accessing data from within indexes is critical to creating the RIGHT indexes. In this module we’ll continue our internals discussion with primary focus on how some of the special features (INCLUDE and filters) enhance your indexing options. Topics covered include:
- Data access patterns
- Covering
- Using INCLUDE (SQL Server 2005+)
- Using filters (SQL Server 2008+)
Module 10: Statistics
Now that we know how SQL Server uses indexes and the different options available, how does SQL Server know which index to choose? Topics covered include:
- Cost-based optimization
- Data access patterns
- Statistics - what do they look like?
- Statistics - what are they telling us?
- Statistics - how do you see them?
- Statistics - when/how do they get created?
- Statistics - when/how do they get updated?
- Even distribution – always?
Module 11: Indexing Strategies
What kinds of indexes are best to create? In what order should the columns be and should they be in the key or only in the leaf-level? This module focuses on a variety of real queries and then debates all of the options that should be considered and above all emphasizes consolidation. Topics covered include:
- Indexing for AND (highly-selective queries, index intersection, covering)
- Indexing for OR (tuning, re-writing)
- Indexing for joins (join types, which strategies use what types of indexes, 3 phases of tuning)
- Indexing for aggregates (hash aggregates, stream aggregates)
- Indexed views (overview/key points)
Module 12: Index Fragmentation
Even with the most carefully-designed indexing strategy, fragmentation is unavoidable. This module will explain the causes and effects of fragmentation, as well as the trade-offs between the various methods of removing it. Topics covered include:
- How the Storage Engine uses an index structure
- Forms of index fragmentations
- Causes and effects of index fragmentation
- Detecting index fragmentation
- Removing index fragmentation
- Case study: GUIDs
|