Global - (+34) 91 414 89 50 | N. America - (800) 757 6543 contact@solidq.com

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

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!

UPCOMING DATES

No scheduled classes now, please contact us for further info.

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!

COURSE DELIVERY OPTIONS

5-DAY CLASS

The course will take place in a classroom with no more than 20 students in order to maintain a good level of interactivity.

PRIVATE ONSITE

The course will take place in your companie’s facilities. We limit attendance to no more than 20 students in order to maintain a good level of interactivity. Request quote here!

Course Coverage

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.

This course is designed for SQL Server administrators who are responsible for monitoring, troubleshooting and optimizing database servers and installations; and SQL Server developers who are responsible for developing efficient SQL Server queries and stored procedures.

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

Configure database physical storage and filegroups

Optimize data loading

/

Manage database compression

R

Troubleshoot common virtualization problems

Troubleshoot and optimize locking and concurrency issues

i

Read, unterstand and toubleshoot complex execution plans

j

Detect and optimize common poorly performing coding practices

Optimize and mantain statistics

Mantain database for optimal performance

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

Developer

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 Optimization 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)

Meet our Instructors

Itzik Ben – Gan

Itzik Ben-Gan is a Mentor and Co-Founder of SolidQ. A Data Platform Microsoft MVP (Most Valuable Professional) since 1999, Itzik has delivered numerous training events around the world focused on T-SQL Querying, Query Tuning and Programming. Itzik is the author of several books including T-SQL Fundamentals Third Edition and T-SQL Querying. He has written articles for SQL Server Pro, SolidQ Journal and MSDN. Itzik’s speaking activities include SQLPASS, SQLBits, SQL Nexus, SQLU, SQLTeach and various user groups around the world. Itzik is the author of SolidQ’s Advanced T-SQL Querying, Programming and Tuning and T-SQL Fundamentals courses along with being a primary resource within the company for their T-SQL related activities.

 Herbert Albert

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.

Would you like to register for this course?

Fill out the form at the top of this page