Objectives

This course will help the student to become familiar with tools and techniques used in SQl Server to optimize performance of queries and applications.

At the end of the course, students will have a good knowlegde of the different techniques to be used when optimizing performace of applications accessing data in a SQL Server database.

Main topics

• SQL Query Performance Tuning introduction
• The Performance-Tuning process
• Performace vs. Price
• Performance Baseline
• SQL Server Performace Killers
• System Performance Analysis
• Performance Monitor Tool
• Dynamic Management Views
• Identifying Hardware Resource Bottlenecks
• Memory Bottlenecks : Analysis and Resolutions
• Disk Bottlenecks : Analysis and Resolutions
• Processor Bottlenecks : Analysis and Resolutions
• Network Bottlenecks : Analysis and Resolutions
• SQL Server Overall Performance
• Creating and Analysis of a Baseline
• SQL Query Performace Analysis
• The SQL Profiler Tool
• Trace Automation
• Combining Trace and Performance Monitor Output
• SQL Profiler Recommendations
• Identification of Costly Queries
• Analyzing Execution Plans
• Query Cost Measurement
• Index Analysis
• Index Benefits and Drawbacks
• Index Design Recommendations
• Clustered vs. Non-Clustered Indexes
• Advanced Indexing Techniques
• Special Index Types (Full-text, Spatial, XML)
• Index Characteristics
• The Database Engine Tuning Advisor
• Mechanism
• Examples
• Limitations
• Bookmark Lookup Analysis
• Purpose of Bookmark Lookups
• Drawbacks of Bookmark Lookups
• Analyzing the Cause of Bookmark Lookups
• Resolving Bookmark Lookups
• Statistics Analysis
• The Role of Statistics in Query Optimization
• Statistics on Indexed and Non-Indexed Columns
• 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
• Importance of the Fill Factor
• Automatic Fragmentation Maintenance
• Execution Plan Cache Analysis
• Execution Plan Generation and 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
• Stored Procedure Recompilation
• Benefits and Drawbacks of Recompilation
• Identifying the Statement Causing Recompilation
• 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
• Understanding Blocking
• Types of 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 Deadlocks
• Deadlock Analysis
• Avoiding Deadlocks
• Cursor Cost Analysis
• Cursor Fundamentals
• Cursor Cost Comparison
• Default Result Set
• Analyzing SQL Server Overhead with Cursors
• Cursor recommendations
• Database Workload Optimization
• Workload Optimization Fundamentals
• Workload Optimization Steps
• Capturing and Analyzing Workload
• Identifying the Costliest Query
• Determining the Baseline Resource use of the Costliest Query
• Analyzing and Optimizing External Factors
• Analyzing the Internal Behavior of the Costliest Query
• Optimizing the Costliest Query
• SQL server Optimization Checklist
• Database Design Checklist
• Query Design Checklist
• Configuration Settings Checklist
• Database Administration Checklist
• Database Backup Checklist