SQL Server Tuning for High Performance

Real-world Problem Solving through Design, Internals, Indexing Strategies, Optimizing Procedural Code & Controlling Mixed Workloads

Presented by Kimberly L. Tripp

Join us for a five day event which will immerse you into the world of performance tuning and optimization for SQL Server. For 5-days most labs and demos will be targeting SQL Server 2005, however, many techniques will work across both SQL Server 2000 and SQL Server 2005 and our classroom environment will include both versions. This course started from a series of popular conference sessions and seminars – including sessions that have been top rated at conferences around the world – and now includes hands-on labs and significantly more time to really dive into the content. Together with these hands-on labs, the materials combine to create a cohesive, interesting and in-depth course. Fast-paced and packed full of information, this is the way to understand better design practices that can improve resource utilization, scalability and overall system performance. Key features discussed include. If you want to improve performance and get a better understanding of SQL Server internals...an Immersion Event is the place to be!

Improve your performance today and acquire knowledge and skills that will continue to help you tomorrow! 

Key features discussed: vertical and horizontal partitioning, table structures, index internals, statistics, how the optimizer chooses indexes, strategies for index coverage, procedure plans and plan caching, caching and recompilation, techniques to improve cache utilization, methodologies for finding performance problems, snapshot isolation, and table and index partitioning.

NOTE: The following detailed TOC is subject to change without notice. The general topics covered by this outline will not change but the specific order and layout may be adjusted for time and/or content.

Database Structures and Operational Best Practices

• The Anatomy of a Data Modification
• Data v. Log
• Write-ahead Logging
• Recovery
• Checkpoints
• Database Components
• Optimizing the Data Portion
• Instant File Initialization
• Optimizing the Log Portion
• Minimizing VLFs

File Structure/Placement

• Introduction to Filegroups
• Using Filegroups for Maintenance
• Using Filegroups for Availability
• Using Filegroups for Performance
• Guidelines for Creating Filegroups
• Distributing Data Across Disks by Using Filegroups
• Combining Filegroups with Hardware RAID
• Functional Partitioning Basics
• Common Questions/Best Practices

Designing for Performance
Part I: Table Structures

• Entities
• Attributes
• Data types
• Nullability
• Creating Tables
• Data Integrity Enforcement with Constraints
• Physical Structures
• Initial Table Structure
• A New Way of looking at “relationships”
• Table Structure and Row Size Optimizations

Designing for Performance
Part I: Table and Index Partitioning

• Concepts in Partitioning
• Partitioning Strategies
• Vertical
• Horizontal
• Implementation
• SQL Server 2000 Partitioned Views
• Requirements
• Implementation
• SQL Server 2005 Partitioned Tables
• Requirements
• Implementation
• Implementing the Sliding Window Scenario
• High Performance Loading
• Design Techniques for Better Availability

Snapshot Isolation

• Availability, What About Locking?
• Mixed Workloads
• ACID Properties
• Understanding Isolation Levels
• Controlling Isolation Levels
• Allowing Read Committed Using Statement-level Snapshot
• Allowing Snapshot Isolation
• Upgrade impact
• Potential Conflicts

Index Internals

· Index Concepts

· Table Structure

· Index Internals

o Heaps

o Why Cluster

o Table Usage

o Employee Table Case Study

· Why Cover Internals?

· Key Points…

Statistics

· Query Specific Index Usage – How?

· Data Access Patterns

· Statistics

o What do they look like?

o What are they telling us?

· Statistics and Interpolation

· Statistics

o How do you see them

o When/how do they get created

o When/how do they get updated

· Interpolation – When it goes wrong

· Even better… Indexes!

SARGs and Joins

· Selectivity

· How to Improve Queries with Varying Search Arguments (SARGs)

o Indexing for AND

o Indexing for OR

· How to Improve Joins

o Phase I

o Phase II

o Phase III

Aggregations and Indexed Views

· Indexing for Aggregations

· Views & Indexes

· Indexed Views

o Defined

o Power of Indexed Views

· Creation and Implementation

· Requirements

Index Maintenance

· How Fragmentation Occurs

· What Fragmentation Means

· How to See Fragmentation

· How to Minimize Fragmentation

· Rebuilding an Index

o Why

o When

o How

· Automating Index Rebuilds

· Understanding Index Usage

Optimizing Procedural Code

· Statement Execution/Plan Caching

o Auto-parameterization

o Accessing Cache Information

o sp_executesql

o Dynamic String Execution

· Stored Procedure Recompilation Issues

o When/what do you want to Recompile?

o Options for Recompilation?

· Stored Procedure Best Practices

o Naming Conventions

o Writing Solid Code

o Excessive Recompilations

Performance Tuning Strategies and Methodologies

· Where can performance be improved?

· Our Focus – SQL Server

o Designing for Performance

o Indexing for Performance

o Optimizing Procedural Code

· Problems

o Known Problems

o Finding the Unknown Problems

§ Excessive Resource Utilization

§ Long Running Queries

§ Frequently Executed Queries

· Steps to Improving Workload Performance

· Keeping the performance optimal

· Where to go for more information!