Realdolmen Education

Details

SQL Server Performance Tuning

This course is currently not scheduled on the open calendar, but can be organized on request.

Request Course

Duration

2 day(s)

Audience

This course is intended for SQL Server database administrators, implementers, system engineers, and developers who are responsible for writing queries and optimizing applications accessing a SQL Server Database.

Prerequisites

Experience using a Microsoft Windows operating system. An understanding of basic relational database concepts, including: Logical and physical database design. Data integrity concepts. Relationships between tables and columns (primary key and foreign key). How data is stored in tables (rows and columns). Knowledge of basic Transact-SQL statements.

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.

Methods

Class training alternated with demos and exercises. Each student disposes of a workstation.

Description

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.

Contents

  • Module 1: SQL Query Performance Tuning Introduction
    • The Performance-Tuning process
    • Performace vs. Price
    • Performance Baseline
    • SQL Server Performace Killers
  • Module 2: 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
  • Module 3: 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
  • Module 4: 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
  • Module 5: The Database Engine Tuning Advisor
    • Mechanism
    • Examples
    • Limitations
  • Module 6: Bookmark Lookup Analysis
    • Purpose of Bookmark Lookups
    • Drawbacks of Bookmark Lookups
    • Analyzing the Cause of Bookmark Lookups
    • Resolving Bookmark Lookups
  • Module 7: 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
  • Module 8: Fragmentation Analysis
    • Causes of Fragmentation
    • Fragmentation Overhead
    • Analyzing the Amount of Fragmentation
    • Fragmentation resolutions
    • Importance of the Fill Factor
    • Automatic Fragmentation Maintenance
  • Module 9: 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
  • Module 10: Stored Procedure Recompilation
    • Benefits and Drawbacks of Recompilation
    • Identifying the Statement Causing Recompilation
    • Causes of Recompilation
    • Avoiding Recompilations
  • Module 11: 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
  • Module 12 : 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
  • Module 13 : Deadlock Analysis
    • Deadlock Fundamentals
    • Using Error handling to Catch Deadlocks
    • Deadlock Analysis
    • Avoiding Deadlocks
  • Module 14 : Cursor Cost Analysis
    • Cursor Fundamentals
    • Cursor Cost Comparison
    • Default Result Set
    • Analyzing SQL Server Overhead with Cursors
    • Cursor recommendations
  • Module 15 : 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
  • Module 16 : SQL server Optimization Checklist
    • Database Design Checklist
    • Query Design Checklist
    • Configuration Settings Checklist
    • Database Administration Checklist
    • Database Backup Checklist