10. Indexes & Concurrency

10. Indexes & Concurrency

1. Implementing & Maintaining Indexes

   • Understanding the Anatomy of a Balanced Tree (B-Tree)
   • Understanding Index Types & Structures
     • Clustered Indexes
     • Non-Clustered Indexes
     • Covering Indexes
     • Filtered Indexes
     • Primary XML & Secondary XML Indexes
     • Spatial Indexes
     • Full-Text Indexes
     • Columnstore Indexes
   • Designing Indexes for Efficient Retrieval
     • Clustered Indexes
     • Non-Clustered Indexes
     • Covering Indexes
     • Filtered Indexes
     • Primary XML & Secondary XML Indexes
     • Full-Text Indexes & Semantic Searches
     • Columnstore Indexes
   • Understanding Statistics
     • Using Index Internals
     • Moving Row-Overflow Data
   • Creating & Modifying Indexes
     • Choosing Fill Factors
     • Determining Fragmentation
     • Removing Index Fragmentation
     • Updating Statistics
   • Tracking Missing Indexes
   • Reviewing Unused Indexes

2. Identifying & Resolving Concurrency Problems

   • Defining Transactions & Transaction Scope
   • Understanding SQL Server Lock Management
     • Lock Modes
     • Explaining Lock Compatibility
     • Understanding Dynamic Lock Management
     • Understanding Transaction Isolation Levels
     • Choosing the Read Committed Snapshot Isolation Database Option
     • Lock Duration Based on Transaction Isolation Levels
     • Monitoring Locks
     • Identifying Blocking
     • Avoiding Blocking
   • Using AlwaysOn Replicas to Improve Concurrency
   • Detecting & Correcting Deadlocks
     • Capturing Deadlock Information
   • Using Activity Monitor
   • Diagnosing Bottlenecks
   • Using Reports for Performance Analysis
     • Using Standard Reports
     • Using Custom Reports
     • Executing the KILL Process