Schedules:

There are currently no scheduled classes for this course.

 
  Query Tuning for SQL Server 2000 & 2005
 

2-Day (SQ-QURYTUN-401-EN)

Description
Author
Audience
Prerequisites
Delivery Format
T-SQL Courses
Course Outline

Description

This seminar teaches you how to identify queries that need tuning and how to tune them. The seminar starts by describing in detail a query tuning methodology developed by Solid Quality Mentors and practiced in production systems. You will learn how to identify the bottlenecks in the system and the processes/queries that need to be tuned. Once those are identified, the actual tuning process starts. The seminar covers in detail tools to analyze and tune queries, internal structures, index tuning, index access methods, index optimization scale, query revisions, preparing sample data, set-based vs. iterative solutions and tuning examples.

Author

This seminar was developed by Itzik Ben-Gan, a mentor and one of the founders of Solid Quality Mentors, and the author of Inside Microsoft SQL Server 2005: T-SQL Querying (MSPress, 2006) and Inside Microsoft SQL Server 2005: T-SQL Programming (MSPress, 2006).

Audience

  • T-SQL Programmers and DBAs.
  • Those that need to tune queries in SQL Server 2000 and 2005.

Prerequisites

The seminar assumes at least one year of querying and query tuning experience in SQL Server.

Delivery Format

2 days, 5 modules, hands on along with instructor's demos

T-SQL Courses

T-SQL Courses currently available:

  • A - Introduction to Transact-SQL for SQL Server 2000 and 2005 (3-day)
  • B - Query Tuning for SQL Server 2000 and 2005 (2-day)
  • C - Advanced Transact-SQL Querying, Programming and Tuning for SQL Server 2000 and 2005 (5-day)

Course Outline

Module 1: Tuning Methodology

  • Analyze Waits at the Instance Level
  • Correlate Waits with Queues
  • Determine Course of Action
  • Drill Down to the Database/File Level
  • Drill Down to the Process Level
  • Tune Indexes/Queries

Module 2: Tools for Query Tuning

  • syscachobjects
  • Clearing the Cache
  • Dynamic Management Objects
  • STATISTICS IO
  • Measuring Runtime of Queries
  • Analyzing Execution Plans
  • Hints
  • Traces/Profiler
  • Database Engine Tuning Advisor

Module 3: Index Tuning

  • Table and Index Structures
  • Index Access Methods
  • Index Optimization Scale
  • Fragmentation
  • Partitioning

Module 4: Preparing Sample Data

  • Data Preparation
  • TABLESAMPLE

Module 5: Set-Based vs. Iterative/Procedural Approach and Tuning Examples

  • Understanding when to use each approach
  • Tuning examples