SQL Server 2000 Query Performance Tuning
Course Level: Advanced
Duration: 15 Hours
Covers: SQL Server 2000, 7.0
Benefits:
- Learn more about internal of SQL Server & SQL Server Query Optimizer
- Learn about the Query Performance Tuning Process
- Learn more about Indexes, statistics, fragmentation, execution plan
caching, blocking, deadlocks which affect the performance of SQL Server
- Capable of tuning complex queries
Prerequsites:
- At least 1 year+ experience with T-SQL Programming
- Familiarity with SQL Server Administration
Course Outline:
Query Performance Tuning Overview
- Performance Tuning Process
- Performance Baseline
- Factors that affect SQL Server Performance
Server Performance Analysis
- System Monitor
- System Resource Utilization
- Memory Bottleneck Analysis / Resolution
- Disk Bottleneck Analysis / Resolution
- Processor Bottleneck Analysis / Resolution
- Network Bottleneck Analysis / Resolution
- SQL Server Overall Performance
- Creating a Baseline
SQL Server Performance Tuning Tools
- Using SQL Profiler Tool
- Tips on Using Profiler
- Using SQL Query Analyzer's Features for Performance Tuning
- Identifying Costly Queries
- Execution Plan
- Query Cost
SQL Server Data Storage Architecture and Indexes
- SQL Server Storage Architecture: Extents and Pages
- What is an Index?
- Clustered Index
- Non-clustered Index
- Clustered vs Non-clustered Index
- Index Design Recommendations
- Advanced Indexing techniques
- Other Characteristics of Index
Index Tuning Wizard
- Index Tuning Architecture
- Using Index Tuning Wizard
- Choosing a SQL Trace for the ITW
- Limitations
Bookmark lookup Analysis
- Purpose of Bookmark Lookup
- Drawbacks of Bookmark Lookup
- Analyzing the cause of a bookmark lookup
- Resolving a bookmark lookup
Statistics Analysis
- What is Statistics?
- Importance of Statistics
- Statistics Maintenance
- Analyzing Effectiveness of Statistics for a Query
- Recommendations
Fragmentation Analysis
- Causes of Fragmentation
- Fragmentation Overhead
- Analyzing Amount of fragmentation
- Fragmentation Resolution
- Automatic Maintenance
Execution Plan Cache Analysis
- Internals of Execution Plan Generation
- Execution Plan Caching
- Components of the Execution Plan
- Aging of the Execution Plan
- Analyzing the execution plan cache
- Execution Plan Reuse
- Execution Plan Cache Recommendations
Stored Procedure Recompilation
- Benefits and Drawbacks of Recompilation
- Identifying statement causing recompilation
- Analyzing causes of recompilation
- Avoiding Recompilation
Query Design Analysis
- Query Design Recommendations
- Operate on Small Result Sets
- Use Indexes Effectively
- Avoid Optimizer Hints
- Use Domain and Referential integrity
- Avoid resource intensive queries
- Reduce number of network round trips
- Reduce Transaction Cost
Blocking & Deadlock Analysis
- Blocking Fundamentals
- Understanding Blocking
- Database Locks
- Isolation Levels
- Effect of indexes on Locking
- Blocking Analysis
- Blocking Resolutions
- Deadlock Fundamentals
- Deadlock Analysis
- Deadlock Resolution
Cursor Cost Analysis
- Cursor Fundamentals
- Cursor Cost Comparison
- Analyzing Overhead with Cursors
- Connection performance Issues
- Database Connection Cost
- ODBC and OLE DB Connection Pooling
- Effects of Session pooling on Performance
- ADO/ADO.NET Threading Model
- Selecting Data Access Mechanism
- Database Connection Recommendations
Database Workload Optimization
- Workload Optimization Fundamentals
- Workload Optimization Steps
- Capturing and Analyzing Workload
- Identifying the Costliest Query
- Determining the Baseline Resource use of Costliest Query
- Analyzing and Optimizing External Factors
- Analyzing the Internal Behavior of Costliest Query
- Optimizing Query
