Tuning and Optimizing Queries Using Microsoft SQL Server 2005 (2784)

OBJETIVOS
This course provides database developers working in enterprise environments using Microsoft SQL Server 2005 the knowledge and skills to evaluate and improve queries and query response times. The course focuses on systematic identification and optimization of database factors that impact query performance.
After completing this course, students will be able to:
Normalize databases.
Design a normalized database.
Optimize a database design by denormalizing.
Optimize data storage.
Manage concurrency
Manage concurrency by selecting the appropriate transaction isolation level.
Select a locking granularity level.
Optimize and tune queries for performance.
Optimize an indexing strategy.
Decide when cursors are appropriate.
Identify and resolve performance-limiting problems

PARTICIPANTES
This course is intended for current professional database developers who have three or more years of on-the-job experience developing SQL Server database solutions in an enterprise environment.

EQUIPOS Y DURACIÓN
Se dispone de un ordenador por alumno. El curso tiene una duración de 15 horas (consultar los horarios disponibles en el calendario de New Horizons). El nº de plazas es limitado.

 

[Temario]

Measuring Database Performance
Importance of Benchmarking
Key Measures for Query Performance: Sysmon
Key Measures for Query Performance: Profiler
Guidelines for Identifying Locking and Blocking

Optimizing Physical Database Design
Performance Optimization Model
Schema Optimization Strategy: Keys
Schema Optimization Strategy: Responsible Denormalization
Schema Optimization Strategy: Generalization

Optimizing Queries for Performance
Performance Optimization Model: Queries
What Is Query Logical Flow?
Considerations for Using Subqueries
Guidelines for Building Efficient Queries

Refactoring Cursors into Queries
Performance Optimization Model: Query-Set-based solutions
Five Steps to Building a Cursor
Strategies for Refactoring Cursors

Optimizing an Indexing Strategy
Performance Optimization Model: Indexes
Considerations for Using Indexes
Best Uses of the Clustered Index
Best Practices for Non-Clustered Index Design
How to Document an Indexing Strategy

Managing Concurrency
Performance Optimization Model: Locking and Blocking
Multimedia - "How to Use Efficient Queries to Reduce Locking and Blocking"
Strategies to Reduce Locking and Blocking