New Horizons Lexington KY Computer Training
New Horizons Computer Learning Center presents

2784 Tuning and Optimizing Queries Using Microsoft SQL Server 2005 On-Site Training

This on-site training class is also available as Public Schedule Seminar.

This workshop provides database developers working in enterprise environments using Microsoft SQL ServerT 2005 the knowledge and skills to evaluate and improve queries and query response times.

Course Description/Agenda

Overview :
This workshop provides database developers working in enterprise environments using Microsoft SQL ServerT 2005 the knowledge and skills to evaluate and improve queries and query response times.

At Course Completion:
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.

Prerequisite Comments:
Have a Microsoft Certified Technology Specialist: Microsoft SQL Server 2005 credential or equivalent experience
Have a Microsoft Certified Technology Specialist: Microsoft SQL Server 2005 credential or equivalent experience

Prerequisite(s) or equivalent knowledge:

2778 Writing Queries Using Microsoft SQL Server 2008 Transact-SQL

2779 Implementing a Microsoft SQL Server 2005 Database

2780 Maintaining a Microsoft SQL Server 2005 Database

Lesson 1: Measuring Database Performance

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

Lesson 2: Optimizing Physical Database Design

Performance Optimization Model
Schema Optimization Strategy: Keys
Schema Optimization Strategy: Responsible Denormalization
Schema Optimization Strategy: Generalization

Lesson 3: Optimizing Queries for Performance

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

Lesson 4: Refactoring Cursors into Queries

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

Lesson 5: 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

Lesson 6: 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

More Seminar Information