Performance Tuning Workshop

Key Concepts Covered in this Course

I work with Microsoft SQL Server for more than 20 years, and I provide with my company SQLpassion consulting and training services around the whole world.

Since 2012 I’m also a Microsoft Certified Master for SQL Server.

Course Description

Are you desperately searching for a way to enhance your SQL Server skills – especially when it comes to performance tuning? Yesterday your SQL Server was running smoothly, but today’s performance is very bad? Your end users are complaining? And You have no idea what the problem is nor how to solve it?

Because of these reasons I’m offering my SQL Server Performance Tuning Workshop that will cover everything you need to know about SQL Server performance tuning.

Info: currently only available on request or as a corporate training!

 5/5

Amazing Course

“This training on SQL Server performance tuning was extremely interesting. Klaus taught me lot of things I was not aware of. I especially loved the tips he gave on common mistakes and wrong concepts about SQL Server. Such information, coming from an independent expert (“expert” is still very understimated when talking about Klaus’ knowledge), is a lot more valuable than spending hours reading and googling. This training has a high return on investment and anyone who is seriously working with SQL Server should attend this workshop.”

Filip from Beerse, Belgium

Course Duration

5 Days

Course Price

On request

Course Level

Intermediate

Day 1 – Performance Tuning Fundamentals

Query Execution
- Data Modification Statements
- CHECKPOINT Process
- Write Ahead Logging

Database Internals
- Pages, Extent Management
- Data Storage

Index Internals
- Clustered Indexes
- Non-Clustered Indexes
- Clustered Key Dependency

Extended Events
- Overview
- Event Sessions
- Event Targets

Day 2 – Working with large Databases

How to design a large database
- File Group Design
- File Placement Guidelines
- Transaction Log
- Online Piecemeal Restores

Partitioned Tables
- Use Cases, Scenarios
- Advantages
- Limitations
- Partitioned Views
- Maintenance

Index and Statistics Maintenance
- Rebuild
- Reorganize
- Transaction Log Impact
- Maintenance options

Day 3 – Transactions & Concurrency

Pessimistic Concurrency
- Read Uncommitted
- Read Committed
- Repeatable Read
- Serializable

Optimistic Concurrency
- Read Committed Snapshot Isolation
- Snapshot Isolation
- Version Store

Lock Escalations
- Understanding Lock Escalations
- Troubleshooting
- Partitioned Tables

Deadlocking
- Analyzing Deadlocks
- Troubleshooting
- Deadlock Avoidance

Day 4 – Performance Troubleshooting

Latches & Spinlocks
- Overview
- Analysis & Troubleshooting
- Common Problems

Troubleshooting TempDb
- Overview
- Temp Tables vs. Table Variables
- TempDb Latch Contention
- Best Practices

Resource Governor
- Overview
- Resource Throttling
- CPU, Memory, I/O

Memory Management
- Configuration Options
- Troubleshooting Memory Consumption
- Best Practices

Day 5 – Performance Monitoring & Troubleshooting

Performance Monitoring Methodology
- Establishing a Baseline
- Wait Statistics
- I/O Latency Times

OS and Storage Configuration
- NUMA
- RAID Levels
- Storage Testing

Database Configuration
- File Placement
- Auto Growth
- Statistics Options
- Optimistic Concurrency

Performance Troubleshooting
- Wait Statistics
- Dynamic Management Views & Functions