SQL Server Performance and Tuning for Data Warehouses

Course:  SSPTDW
Duration:  5 Days
Level:  III
Course Summary

This course will provide students with a good understanding of SQL Server Architecture and Query Performance so that applications and their supporting data warehouse environment can be tuned for optimal performance. It will also help students understand how to size, plan, monitor and tune a SQL Server application for either a OLTP or OLAP environment. Everything from Operating System & SQL Server service tuning through to query and index tuning will be covered.

« Hide The Details
Topics Covered In This Course

Performance & Tuning

  • Sizing & Planning
  • Monitoring Performance
  • Analyzing Performance Data
  • Tuning & Optimizing Performance

SQL Server Internals

  • Windows & SQL Server Architecture
  • Process & Thread management
  • Memory Use & Management
  • I/O Management
  • Networking

SQL Server Architecture

  • Data Storage
  • Transaction Logging
  • Locking
  • Indexing
  • OLTP databases
  • Data Warehouses

Planning A SQL Server Database Application

  • Capacity Planning
  • Sizing Databases
  • Sizing Servers

System Performance & Tuning

  • Hardware Resource Tuning
  • I/O Performance
  • Database Layout
  • SQL Server Configuration Options

Monitoring Tools

  • Performance Monitor
  • SQL Profiler
  • DBCC

Optimal Database Design

  • Database Design
  • Table Design
  • Indexes
  • OLTP databases
  • Data Warehouses

Index Optimization

  • Index Types
  • Covering Indexes
  • Index Strategies

Locking

  • Lock Types
  • Locking & Granularity
  • Blocking
  • Locking & Transactions
  • Transaction Isolation Levels
  • Transaction Design
  • Analyzing Locks

Query Optimization

  • Query Optimizer
  • Recompilation
  • Query Cache
  • Query Types
  • Range
  • Ordering
  • Grouping
  • Table joins & Join Algorithms

Analyzing Queries

  • Query Analyzer & Execution Plans
  • SHOWPLAN
  • Graphical
  • Index Statistics
  • Index Tuning Wizard

Tuning SQL Statements

  • Comparing Queries
  • Avoiding Sub-queries
  • Table Hints

Database Application Tuning

  • Using Stored Procedures & User Defined Functions
  • Views
  • Partitioning Data
  • Derived Data
  • Minimizing Redundant Data With Triggers
  • Optimizing Transactions
  • Optimizing Cursors

Tuning SQL Server Services & Tasks

  • Replication Services
  • DTS Packages
  • Backup & Recovery

Analysis Services Specifics

  • Understanding the Query Processor Architecture
  • Session Management
  • Enhancing Query Performance
  • Optimizing Dimensions
  • Identifying Attribute Relationships
  • Using Hierarchies Effectively
  • Maximizing the Value of Aggregations
  • Detecting Aggregation Hits
  • How to Interpret Aggregations
  • Building Aggregations
  • Using Partitions to Enhance Query Performance
  • Partition Slicing
  • Aggregation Considerations for Multiple Partitions
  • Optimizing MDX
  • Increasing Query Parallelism
  • Memory Heap Type
  • Blocking Long-Running Queries
  • Network Load Balancing and Read-Only Databases
  • Enhancing Dimension Processing Performance
  • Understanding Dimension Processing Architecture
  • Dimension-Processing Commands
  • Dimension Processing Tuning Flow Chart
  • Dimension Processing Performance Best Practices
  • Use SQL Views to Implement Query Binding for Dimensions
  • Optimize Attribute Processing Across Multiple Data Sources
  • Tuning the Relational Dimension Processing Query
  • Optimizing Data Inserts, Updates, and Deletes
  • Picking Efficient Data Types in Fact Tables
  • Tuning the Relational Partition Processing Query
  • Getting Rid of Joins
  • Getting Relational Partitioning Right
  • Getting Relational Indexing Right
  • Monitoring and Adjusting Server Memory
  • DTS Packages
  • Backup & Recovery
What You Can Expect

At the end of this course, students will be able to:

  • Successfully improve the performance of SQL-based applications
  • Understand, in-depth, the SQL Server 2008 architecture
  • Understand query processing technology including parsing, cursors, the importance of bind variables, optimizer statistics and more
  • Understand lock architecture, and code applications that do not impede scalability
  • Choose which index type best fits a given scenario
  • Use the EXPLAIN PLAN review access path choices made by the optimizer
  • Rewrite queries with the goal of finding a less expensive query
  • Identify queries that are blocked and find the source of the blocking lock
  • Use HINTs to influence optimizer choices
  • Understand Warehouse partitioning considerations
Who Should Take This Course

This course is designed for senior database analysts, developers or database administrators.

Recommended Prerequisites

Students should have extensive experience with SQL Server 2005 or later and should have a knowledge level equivalent to that provided by the Data Warehouse overview and Dimensional Modeling.

Training Style

Instructor led with 50% lecture and 50% lab.

« Hide The Details
Related Courses
Code Course Title Duration Level
SSASBI
Implementing and Maintaining SQL Server 2008 Analysis Services and Business Intelligence Solutions
5 Days
I
Details
SSASMDX
SQL Server Analysis Services with MDX
5 Days
II
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at info@verhoef.com