1-800-THE-TREE (1-800-843-8733)
 

Developing High-Performance SQL Server Databases: Hands-On

 
Course: 535     Type: Hands-On Training     Duration: 5 Days

Quick Enroll    

You Will Learn How To
  • Design and implement high-performance databases for SQL Server 2005 and 2000
  • Create indexes that optimize different types of queries
  • Design transactions that maximize concurrency and minimize contention
  • Interpret the data access plans produced by the query optimizer
  • Minimize I/O by designing efficient physical data structures
  • Analyze and cure performance problems using SQL Server's tools

Course Benefits
High-availability database systems offer timely access to business-critical data. Microsoft SQL Server offers powerful features to maintain these vital systems. In this course, you acquire an in-depth knowledge of SQL Server's essential tools--the storage engine, lock manager and query optimizer. Through hands-on exercises, you gain the skills to implement a high-performance SQL Server database solution.

Who Should Attend
Those responsible for increasing the performance and efficiency of SQL Server databases. Knowledge of SQL at the level of Course 133, "SQL Server 2005 Server-Side Programming," and a familiarity with logical database design is assumed.

Hands-On Training
Hands-on exercises provide experience developing high-performance SQL Server databases. Exercises include:
  • Monitoring and analyzing performance
  • Developing a performance baseline
  • Setting up a server-side Profiler trace
  • Eliminating extent fragmentation
  • Inspecting procedures in the procedure cache
  • Creating indexes for different query types
  • Improving performance with indexed views
  • Implementing partitioning solutions

Course 535 Content
Fundamental Concepts
Analyzing performance
  • Selecting an appropriate monitoring tool
  • Investigating plans with SHOWPLAN_ALL
  • Interpreting STATISTICS IO output
  • Pinpointing performance problems with aggregated Profiler data
Developing a monitoring plan
  • Establishing a performance baseline
  • Tracking changes over time
  • Creating server-side Profiler traces
  • Monitoring SQL Server and the operating system with System Monitor
Managing Storage
Database architecture
  • Page and extent allocation
  • Controlling data placement with file groups
Defining tables
  • Selecting the correct data types
  • Specifying text and image locations
  • Examining internal page structures
Creating and managing indexes
  • Clustered vs. non-clustered
  • Defining indexed views
  • Analyzing and repairing fragmentation
Memory and Locking
Managing memory
  • Buffer pool
  • Buffer manager
  • Lazywriter
  • Checkpoint
  • Log writer
Designing transactions
  • Consistency vs. concurrency
  • Investigating lock types and their compatibility
  • Choosing isolation levels
  • Designing transactions to limit lock duration
  • Resolving contention problems
  • Handling deadlock
  • Implementing row versioning
Optimizing Queries
Query optimizer architecture
  • Phases
  • Strategies
  • Data access plans
  • Auto-parameterization
  • Avoiding recompilation of queries
Maintaining up-to-date statistics
  • Index vs. column
  • Automatic vs. manual
  • Full-scan vs. sample
Distinguishing among query types
  • Point
  • Multipoint
  • Range
  • Prefix match
  • Extremal
  • Ordering
  • Grouping
  • Join
Designing effective indexes
  • Relating indexes to query types
  • Providing alternate access paths
  • Improving join performance
  • Increasing sort efficiency
  • Reducing I/O with covering indexes
  • Getting design advice from built-in tuning tools
Designing a Physical Data Model
Storing summarized data for faster retrieval
  • Defining roll-up tables
  • Materializing aggregated data with indexed views
Minimizing response time by introducing redundant data
  • Maintaining redundant data with triggers
  • Enhancing performance with surrogate keys
  • Creating a read-only query database
Solving performance problems with partitioning strategies
  • Horizontal vs. vertical partitioning
  • Partitioning tables
  • Defining partitioned views
Scaling out with distributed partitioned views
  • Designing a federated database
  • Implementing a routing strategy for updates

Related Courses
SQL Server is a trademark of Sybase, Inc.
  
 
Request More Info

Salutation

First Name

Last Name

Company

Zip Code

Country
   Codes
Work Phone

Extension

E-mail

A representative will contact you to follow up your request.
Privacy Statement

Save an EXTRA $200 on a 10-Day Training Pass!

Developing High-Performance SQL Server Databases: Hands-On
Upcoming Dates
Oct 6 - 10, 2008
 Boston (Waltham)
Oct 20 - 24, 2008
 Dallas
Oct 20 - 24, 2008
 Ottawa
Oct 27 - 31, 2008
 Toronto
Oct 27 - 31, 2008
 Washington, DC (Reston, VA)
Nov 3 - 7, 2008
 Los Angeles
Nov 17 - 21, 2008
 New York
Dec 8 - 12, 2008
 Washington, DC (Rockville, MD)
Jan 12 - 16, 2009
 Chicago (Schaumburg)
Jan 26 - 30, 2009
 Philadelphia

Developing High-Performance SQL Server Databases: Hands-On
Bring Learning Tree On-Site

Course Tuition
$ 2,950 Standard Tuition
Tuition with a Savings Plan
$ 1,580 10-Day Pass
$ 1,665 Training Passport
$ 1,830 Flex-Pass
$ 2,095 Voucher 10-Pack
$ 2,655 Alumni Gold Discount
$ 2,620 Government Discount
 

 

Developing High-Performance SQL Server Databases: Hands-On
Developing High-Performance SQL Server Databases: Hands-On
Participants exploring query optimizer plans.
The most recent 100 evaluations scored this course at:

  (3.77/4.00)


SQL Server 2005 for the Enterprise


Certification Core Course CPE 29 Credits Vendor Cert - Microsoft 2 Hour(s) College Credit
Customer Service or Enroll: 1-800-843-8733