Advanced Database Performance Tuning and Architecture Training Course

Course overview

Database latency, lock contention, CPU saturation and uncontrolled storage growth rarely have a single cause. Senior engineers need to distinguish an inefficient query from a poor execution plan, missing statistics, unsuitable index design, overloaded hardware or an architectural bottleneck. This course equips participants to investigate those causes using evidence from workload metrics, wait events, execution plans and system telemetry, then justify corrective action in terms of response time, throughput, availability and cost.

Over five days, participants build a disciplined performance-tuning workflow for relational database systems. They analyse query plans, cardinality estimates, joins, indexing strategies, partitioning, memory use, concurrency controls, transaction isolation and I/O behaviour. The course also addresses architecture decisions including read replicas, connection pooling, high availability, workload separation and capacity planning. Labs use PostgreSQL, Microsoft SQL Server and Oracle Database diagnostics to show where vendor tooling differs while reinforcing transferable tuning methods.

Instruction combines expert-led technical briefings with guided diagnostic labs and realistic incident cases. Participants work from baseline measurements through root-cause analysis, remediation testing and post-change validation. Each attendee leaves with a database performance improvement plan for a supplied or approved workplace scenario, including a workload profile, priority findings, recommended changes, measurement targets, rollout risks and monitoring requirements.

The course is designed for database professionals and senior engineers who already support production data platforms and need to make defensible tuning and architecture decisions rather than rely on isolated query rewrites.

Course objectives

By the end of this course, participants will be able to:

• Interpret execution plans to identify join, scan, sort and cardinality-estimation bottlenecks
• Construct a repeatable workload-baselining process using latency, throughput, wait and resource metrics
• Design indexing strategies using selectivity, covering indexes, composite key order and write-cost trade-offs
• Diagnose locking, blocking and deadlock incidents using transaction isolation and session-level evidence
• Tune memory, storage I/O and connection settings against observed workload characteristics
• Evaluate partitioning, read replicas and connection pooling for specific scalability constraints
• Use PostgreSQL, SQL Server and Oracle diagnostic views to isolate high-impact database workload problems
• Produce a performance improvement plan with ranked recommendations, validation tests, rollback criteria and monitoring thresholds

Benefits of attending

Target competencies

Who should attend

• Database Administrators — who own availability, capacity and production performance across database estates
• Database Engineers — who design schemas, indexing standards and resilient data-platform configurations
• Senior Software Engineers — who need to diagnose application SQL and database interaction patterns
• Data Platform Engineers — who operate shared database services and plan scaling or workload separation
• Solutions Architects — who must select database architecture patterns that meet performance and resilience targets
• Technical Operations Managers — who need evidence-based priorities for database remediation and infrastructure investment

Requirements and prerequisites

Participants should have at least two years of practical experience administering, developing against or supporting a relational database in production. They need to be comfortable writing and reading SQL, including joins, aggregations, transactions and basic DDL, and should understand tables, indexes, normalization, primary keys and foreign keys. Familiarity with one of PostgreSQL, Microsoft SQL Server or Oracle Database is expected, along with basic command-line or database-client use. Prior performance-tuning experience is helpful but not required. No programming, cloud certification, data science background or specialist hardware knowledge is required.

Training methodology

The course uses instructor-led analysis of production-style database incidents, followed by hands-on labs in which participants capture baselines, inspect execution plans, trace waits and test tuning changes. Small groups compare alternative index, query and architecture choices against stated service-level targets. Case studies include blocking incidents, slow reporting workloads and scale-out decisions. Each day closes with a structured diagnostic exercise, and the final session converts findings into an implementable performance improvement plan with owners, risks, tests and monitoring measures.

Course outline

Day 1: Performance evidence and workload baselining

• Database performance dimensions: latency, throughput, concurrency and resource utilisation
• Service-level objectives and workload-specific performance targets
• Workload inventory by query class, transaction type and business criticality
• Baseline collection using response-time percentiles and throughput measures
• Wait-event analysis and resource bottleneck classification
• Operating-system, storage and database metric correlation
• Performance investigation workflow from symptom to validated root cause

Workshop: Participants build a baseline dashboard and initial problem statement from a supplied order-processing workload and telemetry set.

Day 2: SQL execution plans and index engineering

• Reading estimated and actual execution plans
• Cardinality estimation errors and stale statistics
• Join algorithms: nested loops, hash joins and merge joins
• Access paths: table scans, index seeks and bitmap strategies
• Composite, covering, filtered and function-based index design
• Sargability, predicate rewrites and parameter-sensitive plans
• Index maintenance, write amplification and statistics refresh policies

Workshop: Participants diagnose slow SQL from plan outputs, redesign indexes and predicates, then document measured before-and-after results.

Day 3: Concurrency, transactions and resource tuning

• Transaction isolation levels and consistency trade-offs
• Lock compatibility, blocking chains and deadlock graphs
• Long-running transactions and version-store or MVCC pressure
• Connection pooling, session limits and connection storm prevention
• Memory allocation for caches, sorts, hashes and work areas
• Storage I/O patterns, checkpoint behaviour and write-ahead logging
• CPU saturation, parallel query execution and workload throttling

Workshop: Participants investigate a simulated peak-hour blocking incident and produce a remediation sequence covering SQL, transactions and configuration.

Day 4: Scalable database architecture and resilience

• Vertical scaling limits and capacity-model assumptions
• Table partitioning strategies and partition-pruning behaviour
• Read replicas, replication lag and read-routing design
• High-availability topologies and failover performance considerations
• Workload isolation for OLTP, reporting and batch processing
• Caching layers and database cache invalidation boundaries
• Schema evolution and performance regression prevention in delivery pipelines

Workshop: Participants create an architecture recommendation for a growing transactional and reporting platform, including scaling triggers and failure risks.

Day 5: Diagnostic tooling and performance improvement planning

• PostgreSQL pg_stat_statements and EXPLAIN ANALYZE workflows
• Microsoft SQL Server Query Store and wait-stat diagnostics
• Oracle Automatic Workload Repository and Active Session History interpretation
• Cross-platform comparison of query, session and storage diagnostics
• Load-test design, representative data volumes and repeatable test scripts
• Change validation using baselines, guardrails and rollback thresholds
• Performance governance, alert thresholds and continuous tuning backlogs

Workshop: Participants complete a capstone performance review and produce a prioritised improvement plan with findings, tests, owners, risks and monitoring measures.

Tools & standards covered

PostgreSQL, Microsoft SQL Server, Oracle Database, SQL Server Query Store

A typical training day

Live online deliveries follow the same structure in the East Africa Time zone, with shorter screen blocks and longer breaks.

What the fee includes

How you can take this course

Certification

Participants who complete the full five days receive the Skillset Development Certificate of Completion, stating the course title, course code, dates and delivery format — suitable for professional-development records and employer reimbursement.

Frequently asked questions