SQL Server Performance Tuning and Troubleshooting (Versions 2017, 2016 and 2014)

SQ-OPT2017-301-EN

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. SQL Server 2017 already incuded!

}

Duration: 40 hours

Level: 300

Objectives

  • Configure database physical storage and filegroups
  • Manage database compression
  • Troubleshoot common virtualization problems
  • Troubleshoot and optimize locking and concurrency issues
  • Optimize and maintain statistics

 

  • Read, understand and troubleshoot complex execution plans
  • Detect and optimize common poorly performing coding practices
  • Optimize data loading
  • Maintain databases for optimal performance

 

”Very knowledgeable and good energy. Kept the class moving and engaged”

What does this course cover?

We have put together a seminar that will not only allow you to optimise the overall performance of your Data Server, but also teach you how to anticipate potential problems and mitigate their effects; after all we need to work within the constraints as dictated by the business strategy. We will start by giving you an understanding of the architecture of SQL Server, focussing on the key subsystems. Once you have a firm foundation for further knowledge, we will build on this to cover key problem areas and how you can negotiate existing performance problems. Having shown you how to trace and eliminate existing problems we will progress onto helping you towards the planning of a configuration of SQL Server for optimal performance.

Who is this course designed for?

  • SQL Server administrators who are responsible for monitoring, troubleshooting and optimizing database servers and installations
  • SQL Server developers who are responsible for developing efficient SQL Server queries and stored procedures

 

Pre-requisites: What do you need to know?

  • Experience with SQL Server
  • Understanding of database concepts
  • Experience with SQL Server administration
  • Experience with Transact-SQL programming
  • Knowledge of SQL Server Performance Tuning concepts

All our courses can be offered as a private delivery and tailored for your team's specific needs

Course outline

Module 01: SQL Architecture
  • SQL Server Components
  • Network Layer
  • The Database Engine
  • SQL OS
  • Virtualized vs. Physical
Module 02: 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 03: CPU and Scheduling
  • Scheduling Architecture and Configurations
    • Scheduling
    • NUMA
  • Parallelism
  • Waits and Queues
  • Troubleshooting and Tuning
  • Resource Governor
Module 04: SQL Server Memory
  • Memory Architecture in SQL Server
  • Memory Configuration
  • Buffer Pool Extension
  • Memory Monitoring
Module 05: 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 06: Query Optimzation and Execution
  • Cardinality Estimation
  • Working with Statistics
  • Query Optimization
  • Query Execution
  • Plan Caching and Recompilation
Module 07: 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 08: SQL Server Concurrency
  • Locking, Latching and Spinlocks
  • Deadlocks and Blocking
  • Monitoring Concurrency
  • Transaction Isolation Levels
  • Snapshot Isolation and Row Versioning
  • Locking Hints
Module 09: SQL Server In-Memory Technologies
  • In-Memory Overview
  • ColumnStore Indexes
  • In-Memory OLTP (Hekaton)

WHEN and WHERE is this course running next?

This course may be scheduled in more than one region. Please check availability in your country.