SQL Server Performance Tuning and Troubleshooting

5 days /40 hours | online / onsite

Be able to push your SQL Server to the limits .

What Does this course Cover?

This course will enable you to quickly and efficiently address SQL Server issues that arise, as well as improve the overall performance of your database. First, we will give you an overview of the architecture of key SQL Server components. After that, we’ll turn to the most severe problems that may occur, how to track them down and how to solve them. You’ll also learn where you can pull the lever to decisively improve the performance of your database. This course can significantly reduce the number of support requests you receive!

Next Dates

Currently there are no public classes planned for this course.

Prerequisites

Attendees should have basic understanding of SQL Server administration and programming.

Course Outline

SQL Server Architecture

  • SQL Server Components
  • Network Layer
  • The Database Engine
  • SQL OS
  • Virtualized vs Physical

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

CPU and Scheduling

  • Scheduling Architecture and Configurations
    • NUMA
    • Parallelism
  • Waits and Queues
  • Troubleshooting and Tuning
  • Resource Governor

SQL Server Memory

  • Memory Architecture in SQL Server
  • Memory Configuration
  • Buffer Pool Extension
  • Memory Monitoring

SQL Server IO

  • Storage Internals
  • Transaction Log and Recovery
  • Bulk Operations
  • Delayed Durability
  • Pre-Installation Testing
  • Best Practices
  • Troubleshooting Tempdb
  • Monitoring IO
  • Data Compression

Query Optimization and Execution

  • Cardinality Estimation
  • Working with Statistics
  • Query Optimization
  • Query Execution
  • Plan Caching and Recompilation

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

SQL Server Concurrency

  • Locking, Latching and Spinlocks
  • Deadlocks and Blocking
  • Monitoring Concurrency
  • Transaction Isolation Levels
  • Snapshot Isolation and Row Versioning
  • Locking Hints

SQL Server In-Memory Technologies

  • In-Memory Overview
  • ColumnStore Indexes
    • In-Memory OLTP

Herbert Albert

Trainer & Author

Herbert Albert is a mentor with Lucient. He has been working with SQL Server since version 6.0 as a consultant, database developer and trainer. He is co-author of official Microsoft courses on Administering and Developing SQL Server, and author of the Lucient training “Troubleshooting and Performance Tuning for SQL Server”. He is also co-author of the “Upgrade Technical Reference Guide” and Technical Editor of Microsoft Press books. As a trainer, consultant and speaker, his focus is on Performance Tuning, T-SQL programming and High Availability, where he works internationally.