SQL Server Performance Tuning and Troubleshooting

40 hours / 5 days

Learn the internal architecture of SQL Server and the typical environments it operates in.

Diagnose problems and optimize the performance of your SQL Server environment.

What Will I Learn?

  • Configure database physical storage and filegroups.
  • Detect and optimize common poorly performing coding practices.
  • Troubleshoot and optimize locking and concurrency issues.
  • Manage database compression.
  • Mantain database for optimal performance.
  • Troubleshoot common virtualization problems.
  • Optimize data loading.
  • Optimize and mantain statistics.
  • Read, unterstand and toubleshoot complex execution plans.

Prerequisites

We recommend to have experience with SQL Server (SQL Server administration and Transact-SQL programming) and knowledge of database and SQL Server Performance Tuning concepts.
 

What Does this Course Cover?

Along the course you will learn the basis of the architecture of SQL Server, focussing on the key subsystems, and the key problem areas.
Join us and you’ll be able to optimize the overall performance of your Data Server and also to anticipate potetial problems and mitigate their effects!

About the Author

Herbert Albert is a Mentor and Managing Director of SolidQ’s Central and Eastern Europe subsidiary. Since SQL Server 6.0 Albert worked as a consultant, a database developer and trainer and holds several Microsoft certifications including MCT. He was involved in the development of several Microsoft Official Curriculum courses. Herbert co-authored “SQL Server Upgrade Technical Reference Guide” for SQL Server 2008 and 2012 and the Microsoft Press book “SQL Server 2005 Step-by-Step Applied Techniques”. He is a regular speaker at international conferences and events. As a trainer Herbert is focused on courses for T-SQL programming and performance tuning, which he delivers successfully all over Europe.

Course Outline

Module 1. SQL Architecture
  • SQL Server Components
  • Network Layer
  • The Database Engine
  • SQL OS
  • Virtualized vs. Physical
Module 2. SQL Server Performance Monitoring Tools
  • Anatomy of a Monitoring Solution
  • Dynamic Management Objects (DMV/DMF)
  • Extended Events and Tracing
  • System and Performance Monitor
  • Creating and Analyzing a Baseline
  • Query Store in SQL 2016
  • Third Party Tools
Module 3. CPU and Scheduling
  • Scheduling Architecture and Configurations
  • Scheduling
  • NUMA
  • Parallelism
  • Waits and Queues
  • Troubleshooting and Tuning
  • Resource Governor
Module 4. SQL Server Memory
  • Memory Architecture in SQL Server
  • Memory Configuration
  • Buffer Pool Extension
  • Memory Monitoring
Module 5. SQL Server IO
  • Storage Internals
  • Transaction Log and Recovery
  • Bulk Operations
  • Delayed Durability
  • Pre-Installation Testing
  • Best Practices
  • Troubleshooting tempdb
  • Monitoring IO
  • Data Compression
Module 6. Query Optimization and
  • Cardinality Estimation
  • Working with Statistics
  • Query Optimization
  • Query Execution
  • Plan Caching and Recompilation
Module 7.SQL Server Indexes and Query Plan Analysis
  • Execution Plan Basics
  • Indexes
  • Clustered and Non-Clustered Indexes
  • Filtered Indexes
  • Indexed Views
  • Indexes on Computed Columns
  • Index Analysis
  • Searchable Arguments
  • Monitoring Index Usage
  • Workload Optimization
  • Index Fragmentation
  • Rebuild vs Reorg
  • Analyzing Execution Plans
  • Physical Operators
  • Joins
  • Spills and Warnings
Module 8. SQL Server Concurrency
  • Locking, Latching and Spinlocks
  • Deadlocks and Blocking
  • Monitoring Concurrency
  • Transaction Isolation Levels
  • Snapshot Isolation and Row Versioning
  • Locking Hints
Module 9. SQL Server In-Memory Technologies
  • In-Memory Overview
  • ColumnStore Indexes
  • In-Memory OLTP (Hekaton)

Upcoming classes

Herbert Albert

Mentor