Model Online / On-Site
Duration
Modules 5 modules

Course Objective & Scope

Scaling stateless application servers horizontally is straightforward, yet a single non-SARGable query, bad join choice or blocking transaction in the underlying database can bring the entire system to a halt. Rather than guessing at indexes, this course teaches developers and DBAs how to diagnose and fix bottlenecks methodically using Execution Plans and optimizer statistics.

It covers Cost-Based Optimizer internals and cardinality estimation, Execution Plan operators (Scan/Seek, Key/Heap Lookups, Nested Loops, Hash/Merge Joins, Sort/Hash Spills), composite and covering index engineering, SARGability and SQL rewriting patterns, ORM performance traps (EF Core / Hibernate), Parameter Sniffing, and lock/deadlock troubleshooting. Labs are tailored to the organisation's primary engine (PostgreSQL, SQL Server, Oracle or MySQL).

Who Should Attend?

  • Senior backend developers and software architects working with high-volume relational tables
  • DBAs and data engineers responsible for query tuning and database performance
  • Tech leads diagnosing slow SQL generated by ORMs
  • Prerequisite: hands-on SQL experience and familiarity with relational databases

Key Takeaways

  • Read estimated vs actual Execution Plans to spot high-cost operators, I/O reads and cardinality misestimates
  • Engineer composite, covering (INCLUDE) and filtered/partial indexes without over-indexing writes
  • Rewrite non-SARGable predicates, scalar functions, problematic OR clauses and deep OFFSET pagination
  • Resolve Parameter Sniffing regressions, stale statistics and memory-to-disk sort/hash spills
  • Catch ORM anti-patterns including N+1 queries, cartesian explosions and implicit type conversions

Curriculum

Query Lifecycle, the Cost-Based Optimizer and Execution Plans
  • Parsing, binding, Cost-Based Optimization (CBO), plan caching and execution phases
  • Reading Estimated vs Actual Execution Plans and measuring logical reads / buffer hits
  • Histograms, density statistics and diagnosing cardinality estimation skew
Index Engineering and Data Access Paths
  • B-Tree internal and leaf pages, Clustered vs Heap tables and the tipping point of Key/Heap Lookups
  • Composite index column ordering (equality before inequality), Covering Indexes (INCLUDE) and Filtered/Partial Indexes
  • Balancing write amplification, index bloat/fragmentation and pruning redundant indexes
Join Algorithms, Aggregations and Memory Spills
  • Nested Loops, Merge Join and Hash Join: how the optimizer chooses and what happens when statistics lie
  • Stream vs Hash Aggregates, avoiding expensive Sort operators with index order
  • Fixing memory grant / work_mem miscalculations that spill sorts and hashes to disk
SQL Rewriting, SARGability and ORM Performance Traps
  • SARGability rules: removing column-wrapping functions, leading wildcards and Implicit Data Type Conversions
  • Replacing scalar UDFs and correlated subqueries with Window Functions, EXISTS and Keyset (Seek) pagination
  • Diagnosing Parameter Sniffing regressions and fixing common EF Core / Hibernate query anti-patterns
Locking, Isolation Levels and Hands-On Tuning Workshop
  • Blocking chains, lock escalation, reading Deadlock graphs and RCSI / MVCC read-consistency trade-offs
  • Finding top resource-consuming queries (Query Store / pg_stat_statements / AWR) and tuning them in a live lab

Other Courses in This Category

  • Oracle PL/SQL Database Programming and Performance

    PL/SQL block structure, cursors, modular Package design with procedures and functions, enterprise exception handling, triggers, dynamic SQL and high-volume processing with FORALL and BULK COLLECT.

    Databases Corporate
    Live Course
    5 modules
    View
  • Relational Data Modelling, SQL Normalization and Schema Optimization

    Conceptual/logical/physical data modelling, 1NF–3NF/BCNF normalization rules, preventing data anomalies, controlled denormalization, natural vs surrogate keys, constraints and zero-downtime schema evolution.

    Databases Corporate
    Live Course
    5 modules
    View
  • SQL Server Database Querying Fundamentals SQL Querying Fundamentals

    Foundational SQL Server course covering relational database concepts, data modeling, normalization, indexing, database objects (Tables, Views, Stored Procedures), and JOIN queries.

    Databases Corporate
    Live Course
    8 modules
    View
  • SQL Server Database Querying Training SQL Server Querying

    Comprehensive SQL Server and T-SQL querying course covering SSMS, SELECT workflows, multi-table JOINs (INNER, OUTER, CROSS, SELF), filtering and pagination, data types, and built-in Scalar, Aggregate, and Window functions.

    Databases Corporate
    Live Course
    7 modules
    View
  • Big Data Querying and Analytics with Apache Spark SQL

    Apache Spark SQL and the DataFrame API, Catalyst Optimizer and Adaptive Query Execution (AQE), Parquet/Delta Lake/Iceberg table formats, join strategies and fixing data skew — for teams building big-data analytics.

    Databases Corporate
    Live Course
    5 modules
    View
  • PostgreSQL for Developers: From Fundamentals to Advanced SQL

    PostgreSQL data types, advanced SQL (CTEs, window functions, LATERAL), JSONB document modelling, full-text search, B-Tree/GIN/GiST/BRIN indexes, PL/pgSQL, MVCC/locking and partitioning — for application developers.

    Databases Corporate
    Live Course
    5 modules
    View

Get in touch