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-availabilty 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. Course 133, "SQL Server 2005 Server-Side Programming," or Course 532, "SQL Server 2000 Transact-SQL 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 as much as $2,350 on a Triple-Pack!

Developing High-Performance SQL Server Databases: Hands-On
Upcoming Dates
May 19 - 23, 2008
 Washington, DC (Reston, VA)
Jun 2 - 6, 2008
 New York
Jun 2 - 6, 2008
 Toronto
Jun 23 - 27, 2008
 Washington, DC (Rockville, MD)
Aug 4 - 8, 2008
 Washington, DC (Reston, VA)
Aug 11 - 15, 2008
 Chicago (Schaumburg)
Aug 18 - 22, 2008
 Atlanta
Aug 25 - 29, 2008
 New York
Oct 20 - 24, 2008
 Ottawa
Oct 27 - 31, 2008
 Toronto

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

Course Tuition
$ 2,950 Standard Tuition
Tuition with a Savings Plan
$ 2,075 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.

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