SQL Server Tuning Online Training
SQL Server Tuning Online Training
SQL Server Tuning Online Training
SQL Server Tuning Online Training Course Content:
Performance Tuning Overview
- The Performance-Tuning Process
- Performance vs. Price
- Performance Baseline
- Where to Focus Efforts
- SQL Server Performance Killers
System Performance Analysis
- Performance Monitor Tool
- Dynamic Management Views
- Hardware Resource Bottlenecks
- Memory Bottleneck Analysis
- Memory Bottleneck Resolutions
- Disk Bottleneck Analysis
- Disk Bottleneck Resolutions
- Processor Bottleneck Analysis
- Processor Bottleneck Resolutions
- Network Bottleneck Analysis
- Network Bottleneck Resolutions
- SQL Server Overall Performance
- Creating a Baseline
- System Behavior Analysis Against Baseline
SQL Query Performance Analysis
- Extended Events Wizard
- Extended Events Automation
- Extended Events Recommendations
- Other Query Performance Metrics Methods
- Costly Queries
- Execution Plans
Index Analysis
- What Is an Index?
- Index Design Recommendations
- Clustered Indexes
- Nonclustered Indexes
- Clustered vs. Nonclustered Indexes
- Advanced Indexing Techniques
- ColumnStore Indexes
- Special Index Types
- Additional Characteristics of Indexes
Database Engine Tuning Advisor
- Database Engine Tuning Advisor Mechanisms
- Database Engine Tuning Advisor Examples
- Database Engine Tuning Advisor Limitations
Bookmark Lookup Analysis
- Purpose of Bookmark Lookups
- Drawbacks of Bookmark Lookups
- Analyzing the Cause of a Bookmark Lookup
- Resolving Bookmark Lookups
Statistics Analysis
- The Role of Statistics in Query Optimization
- Statistics on an Indexed Column
- Statistics on a Nonindexed Column
- Analyzing Statistics
- Statistics Maintenance
- Analyzing the Effectiveness of Statistics for a Query
- Recommendations
Fragmentation Analysis
- Causes of Fragmentation
- Fragmentation Overhead
- Analyzing the Amount of Fragmentation
- Fragmentation Resolutions
- Significance of the Fill Factor
- Automatic Maintenance
Execution Plan Cache Analysis
- Execution Plan Generation
- Execution Plan Caching
- Components of the Execution Plan
- Aging of the Execution Plan
- Analyzing the Execution Plan Cache
- Execution Plan Reuse
- Query Plan Hash and Query Hash
- Execution Plan Cache Recommendations
Query Recompilation
- Benefits and Drawbacks of Recompilation
- Identifying the Statement Causing Recompilation
- Analyzing Causes of Recompilation
- Avoiding Recompilations
Query Design Analysis
- Query Design Recommendations
- Operating on Small Result Sets
- Using Indexes Effectively
- Avoiding Optimizer Hints
- Using Domain and Referential Integrity
- Avoiding Resource-Intensive Queries
- Reducing the Number of Network Round-Trips
- Reducing the Transaction Cost
Blocking Analysis
- Blocking Fundamentals
- Understanding Blocking
- Locks
- Isolation Levels
- Effect of Indexes on Locking
- Capturing Blocking Information
- Blocking Resolutions
- Recommendations to Reduce Blocking
- Automation to Detect and Collect Blocking Information
Deadlock Analysis
- Deadlock Fundamentals
- Using Error Handling to Catch a Deadlock
- Deadlock Analysis
- Avoiding Deadlocks
Cursor Cost Analysis
- Cursor Fundamentals
- Cursor Cost Comparison
- Default Result Set
- Analyzing SQL Server Overhead with Cursors
- Cursor Recommendations
Database Performance Testing
- Performance Testing Overview
- Capturing Data with the Server Side Trace
- Distributed Replay for Database Testing
- Summary and SQL Server Optimization Checklist
Conclusion