The Scenario
A Pretoria financial services firm has an internal CRM database. Every morning at 09:00, when staff generate reporting dashboards, database CPU spikes to 100%, causing app-wide timeouts. The IT manager needs a senior DBA to diagnose the cause, optimize the bottleneck query, and establish an index and maintenance strategy to keep the server running smoothly.
The Brief
Produce a database performance diagnostic playbook, document the optimization of a complex slow-query scenario using SQL indices, and design a database maintenance policy.
Deliverables
- A diagnostic playbook detailing commands (e.g. EXPLAIN ANALYZE, pg_stat_activity, or process monitoring) to isolate the CPU-heavy queries and table locks
- A query optimization case study showing a before-and-after SQL rewrite (e.g., optimizing subqueries to JOINs, removing wildcard SELECT *, or correcting data type mismatches) and the exact CREATE INDEX statements needed
- A database maintenance policy document outlining routine operations like statistics updates (ANALYZE), table vacuuming/defragmentation, and index rebuild schedules
- A database monitoring dashboard specification defining 5 key metrics (e.g. buffer cache hit ratio, index scan vs sequential scan ratio, lock wait times) to watch
Submission Guidance
Ensure the query optimization is technically sound. Use realistic SQL syntax. Explain exactly how the query optimizer's execution plan changes (from a sequential scan to an index scan) once the index is applied.
Submit Your Work
Your submission is graded against the rubric on the right. If you pass, you get a public Badge URL you can share on LinkedIn. There is no draft save, so work offline first and paste your finished response here.