10987A: Performance Tuning and Optimizing SQL Databases




    4 Day(s) Course


    Official Courseware


    Certified Trainer


    Exam Voucher


    1:1 Training Available on Demand


    Customize Your Batch

    Course Information

    Performance Tuning & Optimizing SQL Databases is a four-day training course that has been designed for individuals who manage and maintain SQL Server databases to equip them with the skills to performance tune and optimize their databases.

    Completion of the course equips the participants with the following concepts and skills:

    • Architectural overview of SQL Server and its various components.
    • Core I/O concepts, Storage Area Networks and performance testing.
    • SQL Server execution model, waits and queues.
    • Architectural concepts, troubleshooting scenarios and best practices related to Plan Cache.
    • Architectural concepts and best practices related to data files for user databases and tempDB.
    • Architectural concepts of the Optimizer and how to identify and fix query plan issues.
    • Architectural concepts, troubleshooting strategy and usage scenarios for Extended Events.
    • Techniques to identify and diagnose bottlenecks to improve overall performance.
    • Data collection strategy and techniques to analyze collected data.


    Module 1: SQL Server Architecture, Scheduling, and Waits

    • SQL Server Components and SQL OS
    • Waits and Queues
    • Windows Scheduling vs SQL Scheduling

    Module 2: SQL Server I/O

    • Core Concepts
    • I/O Setup and Testing
    • Storage Solutions

    Module 3: Database Structures

    • Database Structure Internals
    • TempDB Internals
    • Data File Internals

    Module 4: SQL Server Memory

    • Windows Memory
    • In-Memory OLTP
    • SQL Server Memory

    Module 5: SQL Server Concurrency

    • Concurrency and Transactions
    • Locking Internals

    Module 6: Statistics and Index Internals

    • Statistics Internals and Cardinality Estimation
    • Column-store Indexes
    • Index Internals

    Module 7: Query Execution and Query Plan Analysis

    • Query execution plans
    • Analysing query execution plans
    • Query execution and optimizer internals

    Module 8: Plan Caching and Recompilation

    • Plan cache internals
    • Query store
    • Troubleshooting plan cache issues

    Module 9: Extended Events

    • Extended events core concepts
    • Work with extended events

    Module 10: Monitoring, Tracing, and Baselining

    • Baselining and benchmarking
    • Monitoring and tracing


    • The primary audience includes database professionals who are engaged in administering and maintaining SQL Server databases and are also responsible for optimal performance of SQL Server instances that they manage.
    • The secondary audience includes professionals who develop applications that deliver content from SQL Server databases.


    In addition to the professional experience, candidates who attend this training must possess the following technical knowledge:

    • Knowledge of the Microsoft Windows operating system and its functionality.
    • Working knowledge of Transact-SQL.
    • Working knowledge of database administration and maintenance


    Name Code Fee



    What Our Trainees Say

    Mercury Solutions Ltd. is rated 4.6 stars by based on 18 reviews.