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
SSIS16	SQL Server 2016 Integration Services (SSIS)	5 Days	I	Details
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 [email protected]