home 
 
 
 
nlfr
 
Home
About ABIS
Services
Courses
Resources
Contact
MyABIS
All CoursesbalkjeGeneral » Introduction to HW & SW » TracksOperating systems » MVS - OS/390 - z/OS » UNIX - Linux - AIX » Windows » VMwareDatabases, transaction systems and middleware » DB2 for z/OS » DB2 UDB for Linux, UNIX and Windows » Oracle » SQL Server » MySQL » IMS » CICS » WebSphere MQ » WebSphere Application Server » Business intelligence and data warehouseApplication development » Methods and techniques » PRINCE2 » Programming languages » Internet development » Object Oriented systems » Java » IBM development tools » SAS » XML » SOA & web servicesSystems management » ITILbalkjePractical informationRegistration 
Tuning and optimizing queries using SQL Server 2005

Intended for

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

Background

Before attending this course, students must have working knowledge with SQL Server, being for instance a MS Certified Technology Specialist for SQL Server. Having attended the following courses is strongly recommended:

Knowledge of data base design techniques (normalization to 3NF). Basic knowledge of the operating system and application architectures. Good knowledge of Transact-SQL (cursors, grouping, joins, subqueries, ...) is required.

Training method

This instructor-led workshop makes use of the official Microsoft course handouts. A lot of time is spent on hands-on exercises (about 75% of the time).

Course leader

RealDolmen.

Duration

3 days.

Schedule

No public sessions are currently scheduled. We will be pleased to set up an on-site course or to schedule an extra public session (in case of a sufficient number of candidates). Interested ? Please contact ABIS.

Objectives

After completing this course, students will be able to optimize and tune queries in SQL Server 2005.

Main topics

  • 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