Performance
Database Performance Tuning: A Practical Step-by-Step Guide
How to tune a slow database in the order that works: measure first, find the expensive statements, read the plan, fix indexing, keep statistics current, rewrite the query, tune configuration to the workload, then make it a habit. Covers SQL Server, Oracle, PostgreSQL and MySQL.
Need a hand with this? Talk to a senior database DBA ↓By RadixTrie3 September 20265 min read
There are two ways to do database performance work. The first is the rescue project: users complain, a specialist is brought in, a dramatic fix is found, everyone relaxes, and the cycle repeats a year later. The second is the habit: someone looks at the right signals every week, notices the query whose plan changed or the index nobody uses, and fixes it before anyone notices. This guide sets out the tuning process step by step, in the order that finds the real problem fastest, and then how to turn it into the habit.
Why databases get slow
Databases do not get slower on their own. Data grows past the point where a plan that was fine at a million rows is fine at a hundred million. Statistics age and the optimiser chooses differently. A release adds a query that scans instead of seeks. A batch job moves into the same window as month-end reporting. Indexes accumulate that nobody uses but every write pays for. Each change is small; the sum is the Monday morning everyone remembers.
Step 1: Measure before you change anything
Tuning without a baseline is guessing with confidence. Capture what the system is waiting on, which statements consume the most time and resources, and how CPU, memory, storage and connections behave over a representative period that includes the busy windows. Each platform has its own instruments:
- SQL Server: wait statistics, the Query Store for plan history and regressions, the missing and unused index views, blocking chains and tempdb pressure.
- Oracle: the Automatic Workload Repository and Active Session History for top SQL and wait classes, plan changes after statistics gathering, and undo and temp usage.
- PostgreSQL: pg_stat_statements for the heaviest statements, EXPLAIN ANALYZE for individual plans, bloat and autovacuum behaviour, lock waits, and checkpoint and memory configuration.
- MySQL and MariaDB: the slow query log and Performance Schema, InnoDB buffer pool behaviour, replication lag and lock contention.
Step 2: Find the expensive statements
A small number of statements account for most of the load on almost every database. Rank them by total time, not by single execution time: a query that runs in fifty milliseconds a million times a day costs more than a report that runs once for a minute. Rank them again by reads and by CPU, because the top of each list is often different, and note which ones the business actually waits on. That short list is where the tuning effort goes.
Step 3: Read the execution plan
Every platform can show how it executed a statement. Read the plan for the signs that recur everywhere: full scans of large tables where a seek was expected, estimated row counts that are far from the actual counts, sorts and hash operations spilling to disk, key or row lookups repeated thousands of times, implicit data type conversions that defeat an index, and nested loops driven by a wrong estimate. The gap between estimated and actual rows is usually the first clue, because it points at statistics or at a predicate the optimiser cannot reason about.
Step 4: Fix the indexing
- Add the index a frequent query needs, with the columns in the order the predicates and joins use them, and consider including the columns the query returns so the lookup disappears.
- Remove indexes nothing uses. Every one of them is paid for on every insert, update and delete, and the usage statistics tell you which ones are dead weight.
- Merge duplicate and overlapping indexes into one that serves both queries.
- Check that the index can actually be used: a function or conversion applied to the indexed column in the predicate stops it being used, and so does a leading wildcard.
- Rebuild or reorganise only where fragmentation genuinely hurts the workload, not on a calendar.
Step 5: Keep statistics current and plans stable
The optimiser is only as good as its estimates. Make sure statistics are gathered often enough on the tables that change most, and more often on the columns whose distribution skews. Where a platform supports it, capture a known good plan for the statements the business depends on and guard against regressions: the Query Store in SQL Server, SQL plan management in Oracle, and extension-based plan control or careful configuration in PostgreSQL. A plan that changed overnight after a statistics update is the classic Monday morning incident.
Step 6: Rewrite the query
Some statements cannot be indexed into health. Replace row-by-row processing in loops and cursors with set-based logic. Move functions off the indexed columns and onto the parameters. Split a query that tries to do too much at once into stages with an intermediate result. Replace OR chains and long IN lists with joins to a small table. Page through results with a keyset rather than an ever-growing offset. Each of these is a small change with an outsized effect, and each is best made with the developer who wrote the query in the room.
Step 7: Tune configuration to the workload
Defaults are set for the installer's imagined server, not yours. Memory allocated to the database against what the operating system and other services need, parallelism thresholds and degree, working memory for sorts and hashes, tempdb or temporary file layout, checkpoint and log settings, and connection pooling in front of the database all deserve a deliberate value based on the measurements from step one. Change one setting at a time and measure again; configuration is where well-meaning tuning most often makes things worse.
Step 8: Remove contention and fix the schedule
Not every slow database is short of resources. Blocking chains, lock escalation, long-running transactions holding locks, and maintenance or batch jobs running in the business day all present as slowness. Find the blockers, shorten the transactions, choose isolation levels deliberately, and move the maintenance and batch work out of contention with the hours the business cares about.
Step 9: Know when it is capacity
Sometimes the honest answer is that the hardware or the service tier is undersized, and tuning has only delayed that conversation. The measurements from step one make the case: if the top statements are efficient, the indexes are right and the system is still saturated at peak, it is time to size for the workload rather than keep squeezing it.
A useful discipline: every month, review the ten most expensive statements on each production database and decide, for each, whether to fix it, accept it or watch it. Twelve such reviews prevent most rescue projects.
Make it a habit, with the developers
The best results come when the person tuning the database talks to the people writing the queries. A plan review before a release costs minutes; the same query found in production costs a weekend. Good support arrangements make that conversation routine, with the DBA reviewing schema changes and heavy new queries as part of the release process rather than after the fact, and the monthly review above keeping the estate honest between releases.
How RadixTrie does it
Performance tuning is part of RadixTrie's support and managed DBA services rather than a separately quoted project: senior engineers watch the signals above continuously across SQL Server, Oracle and PostgreSQL estates and fix what they find, with direct access to the engineer and no service desk. Our SQL Server, Oracle and PostgreSQL service pages describe the cover, and our case study on stabilising PostgreSQL performance shows the approach applied to one real environment.
Need a hand with this?
Tell us what you run today and when it has to move. A senior database DBA will reply directly, usually within one working day. Prefer email or phone? Write to expert@radixtrie.com or call +27 10 143 0304, or use the contact page to book a 30-minute call.
TAGSPerformance TuningSQL ServerOraclePostgreSQLMySQLDatabase Support