.webp)
.svg)
Program overview
Online
On-site
Hybrid
SQL Performance and Query Optimization
A vendor-neutral course for engineers who need slow queries to stop being a guessing game. Three days covering execution plans, index design, join algorithms, cardinality estimation, partitioning, locking and workload-level diagnosis. Labs use deliberately slow queries against realistic data volumes, and every technique is grounded in evidence from the plan rather than folklore.
Duration:
3 Days
Level:
Advanced
.avif)
1500+ users onboarded
Who will Benefit from this Training?
Training Objectives
Build a high-performing, job-ready tech team.
Personalise your team’s upskilling roadmap and design a befitting, hands-on training program with Uptut
Key training modules
Comprehensive, hands-on modules designed to take you from basics to advanced concepts
Download Curriculum
- How a Query Actually Executes
- Parsing, rewriting, planning and execution stages
- Cost-based optimisation and what the cost number means
- Logical versus physical operations, and why SQL is declarative
- Storage fundamentals: pages, rows, heaps and the buffer cache
- Hands-on: instrument a session and capture baseline timings
- Reading Execution Plans
- EXPLAIN and EXPLAIN ANALYZE and their equivalents across platforms
- Scan, seek, join, sort and aggregate operators and what each costs
- Estimated versus actual rows as the single most useful diagnostic signal
- Buffers, I/O counts and reading a plan bottom up
- Plan caching and reuse behaviour
- Hands-on: diagnose five slow queries from their plans alone
- Index Structures and Selection
- B-tree internals and why depth and selectivity matter
- Clustered versus non-clustered organisation
- Composite indexes and the leading column rule
- Covering indexes and included columns to avoid lookups
- Index selectivity, density and when a scan is genuinely the right choice
- Hands-on: design an index set for a query workload and measure the result
- Specialised Indexes and Their Trade-offs
- Partial and filtered indexes for skewed data
- Expression and function-based indexes
- Hash, GIN, GiST, full text and columnstore structures and their use cases
- The write cost of every index you add
- Detecting unused, duplicate and overlapping indexes
- Hands-on: audit an over-indexed table and justify each removal
- Join Algorithms and Join Order
- Nested loop, hash join and merge join and when each is optimal
- How join order changes cost, and how the optimiser searches for it
- Semi joins, anti joins and rewriting EXISTS, IN and NOT IN
- Diagnosing a bad join choice from the plan
- Hands-on: force a plan change and quantify the difference
- Writing Queries the Optimiser Can Use
- SARGability: predicates that permit index seeks and predicates that prevent them
- Functions on columns, implicit type conversion and collation mismatches
- Wildcard patterns, OR conditions and range predicates
- Filtering early, projecting narrowly and avoiding SELECT star
- Rewriting correlated subqueries and views that block predicate pushdown
- Hands-on: rewrite a query set without changing results and measure each gain
- Statistics and Cardinality Estimation
- Histograms, sampling and how row estimates are produced
- Stale and insufficient statistics as a root cause of plan regressions
- Correlated columns and multi-column statistics
- Parameter sniffing, plan skew and mitigation options
- Hands-on: create a misestimation, observe the plan collapse and repair it
- Sorting, Aggregation and Memory
- Sort and hash aggregate operators and their memory demands
- Spills to disk and how to recognise them in a plan
- Working memory configuration and per-operation grants
- Temporary objects, materialisation and intermediate result sizing
- Hands-on: eliminate a spilling sort through indexing and memory tuning
- Partitioning and Very Large Tables
- Range, list and hash partitioning strategies
- Partition pruning and the predicates required to trigger it
- Partition-wise joins and parallel execution
- Archiving, retention and rolling window maintenance
- Trade-offs and cases where partitioning makes performance worse
- Hands-on: partition a large fact table and verify pruning in the plan
- Locking, Concurrency and Blocking
- Isolation levels and the anomalies each one permits
- Multi-version concurrency control versus lock-based approaches
- Lock escalation, blocking chains and deadlock detection
- Long transactions, bloat and vacuum or cleanup behaviour
- Designing transactions for short duration and consistent lock ordering
- Hands-on: reproduce a deadlock, read the diagnostic output and redesign the transaction
- Finding the Right Queries to Tune
- Workload-level instrumentation: statement statistics, query stores and slow query logs
- Wait event analysis to separate CPU, I/O, lock and network bound problems
- Prioritising by total impact rather than single execution time
- Benchmarking correctly: warm caches, repeat runs and realistic data
- Regression testing and monitoring plans over time
- Building a tuning runbook your team can follow
- Hands-on: capstone triage of a full workload from evidence to validated fixes
Hands-on Experience with Tools
Training Delivery Format
Flexible, comprehensive training designed to fit your schedule and learning preferences
Opt-in Certifications
AWS, Scrum.org, DASA & more
.avif)
100% Live
on-site/online training
.avif)
Hands-on
Labs and capstone projects
.avif)
Lifetime Access
to training material and sessions
Skill-Gap Assessment
Analysing skill gap and assessing business requirements to craft a unique program
1
Personalisation
Customising curriculum and projects to prepare your team for challenges within your industry
2
Implementation
Supplementing training with consulting support to ensure implementation in real projects
3
Why this course
- Evidence over guesswork: Engineers learn to read execution plans and prove what is slow instead of adding indexes speculatively.
- Cuts infrastructure spend: Query and index tuning usually reclaims more capacity than the next instance upgrade would buy.
- Realistic data volumes: Labs run against tables large enough that plan choices actually change, not toy datasets.
- Transferable across platforms: Core optimiser principles taught vendor-neutral, with specifics for PostgreSQL, SQL Server, MySQL and cloud warehouses.
Training objectives
- Explain how a query is parsed, planned and costed by a modern optimiser
- Read and interpret execution plans including estimated versus actual row counts
- Design indexes deliberately, including composite, covering and partial indexes
- Recognise join algorithms and understand why the optimiser chose each one
- Rewrite non-SARGable predicates so indexes can actually be used
- Diagnose cardinality misestimation, stale statistics and parameter sniffing
- Apply partitioning and archiving strategies to very large tables
- Identify and resolve locking, blocking and concurrency bottlenecks
- Find the queries worth tuning using workload-level instrumentation
- Benchmark a change and prove the improvement is real
Who will benefit
- Backend and application engineers writing production SQL
- Database administrators and platform engineers
- Data engineers building warehouse and pipeline workloads
- Site reliability engineers handling database incidents
- Technical leads responsible for application performance
.avif)
Lead the Digital Landscape with Cutting-Edge Tech and In-House " Techsperts "
Discover the power of digital transformation with train-to-deliver programs from Uptut's experts. Backed by 70,000+ professionals across the world's leading tech innovators.
.svg)
.svg)
.svg)
.svg)
.svg)
Recommended Training
Continue your learning journey with these hand-picked courses and bundles
Frequently Asked Questions
2. Will my team get any practical experience with this training?
.svg)
With our focus on experiential learning, we have made the training as hands-on as possible with assignments, quizzes and capstone projects, and a lab where trainees will learn by doing tasks live.
3. What is your mode of delivery - online or on-site?
We conduct both online and on-site training sessions. You can choose any according to the convenience of your team.
4. Will trainees get certified?
Yes, all trainees will get certificates issued by Uptut under the guidance of industry experts.
5. What do we do if we need further support after the training?
We have an incredible team of mentors that are available for consultations in case your team needs further assistance. Our experienced team of mentors is ready to guide your team and resolve their queries to utilize the training in the best possible way. Just book a consultation to get support.