Logo-Icon
HOMEABOUTSERVICESHOW WE WORKBLOGInstagram-Icon Talk To an Expert
RadixTrie-image
Database performance tuning
Performance

Database Performance Tuning as a Habit, Not a Project

Performance problems are cheap to fix when they are small and expensive when users are shouting. How continuous tuning works across SQL Server, Oracle, PostgreSQL and MySQL, and why it beats the occasional rescue project.
By RadixTrie3 September 20262 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. The second way is cheaper, less disruptive and far better for the business. This article describes how it works.

Why performance degrades quietly

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.

What to watch, by platform

  • SQL Server: wait statistics, the Query Store for plan regressions, missing and unused index data, blocking chains and tempdb pressure.
  • Oracle: the 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, bloat and autovacuum behaviour, lock waits, checkpoint and memory configuration, and index usage.
  • MySQL and MariaDB: the slow query log and Performance Schema, InnoDB buffer pool hit behaviour, replication lag and lock contention.

The fixes that recur

  1. Indexing. Adding the index a frequent query needs, removing indexes nothing uses, and correcting column order so an index can actually be used.
  2. Statistics and plan stability. Keeping statistics current and, where a platform supports it, pinning or guiding plans for the queries the business depends on.
  3. Query rewrites. Replacing row-by-row processing with set-based logic, removing functions that defeat indexes, and splitting queries that try to do too much at once.
  4. Configuration against workload. Memory, parallelism and I/O settings tuned for what the system actually does rather than what the installer assumed.
  5. Scheduling. Moving maintenance and batch work out of contention with the business day.
  6. Capacity. Sometimes the honest answer is that the hardware or tier is undersized, and tuning has only delayed that conversation.
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.

Tuning and development, together

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.

How RadixTrie does it

Performance tuning is part of RadixTrie's <a href="/services">support and managed DBA services</a> rather than a separately quoted project: senior engineers watch the signals above continuously across SQL Server, Oracle, PostgreSQL and MySQL/MariaDB estates and fix what they find. Our case study on <a href="/blog/how-radixtrie-helped-stabilise-postgresql-performance">stabilising PostgreSQL performance</a> shows the approach applied to one real environment.
<strong>Related reading:</strong> <a href="/blog/how-radixtrie-helped-stabilise-postgresql-performance">How RadixTrie helped stabilise PostgreSQL performance</a> &middot; <a href="/blog/database-health-check">The Database Health Check: What a Good One Covers and What It Finds</a> &middot; <a href="/blog/managed-dba-services-explained">Managed DBA Services Explained: What You Hand Over, What You Keep</a>
Tired of performance rescues? Make tuning routine.
TAGSPerformance TuningSQL ServerOraclePostgreSQLMySQLDatabase Support
RadixTrie-image
Database performance tuning
Performance

Database Performance Tuning as a Habit, Not a Project

Performance problems are cheap to fix when they are small and expensive when users are shouting. How continuous tuning works across SQL Server, Oracle, PostgreSQL and MySQL, and why it beats the occasional rescue project.
By RadixTrie3 September 20262 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. The second way is cheaper, less disruptive and far better for the business. This article describes how it works.

Why performance degrades quietly

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.

What to watch, by platform

  • SQL Server: wait statistics, the Query Store for plan regressions, missing and unused index data, blocking chains and tempdb pressure.
  • Oracle: the 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, bloat and autovacuum behaviour, lock waits, checkpoint and memory configuration, and index usage.
  • MySQL and MariaDB: the slow query log and Performance Schema, InnoDB buffer pool hit behaviour, replication lag and lock contention.

The fixes that recur

  1. Indexing. Adding the index a frequent query needs, removing indexes nothing uses, and correcting column order so an index can actually be used.
  2. Statistics and plan stability. Keeping statistics current and, where a platform supports it, pinning or guiding plans for the queries the business depends on.
  3. Query rewrites. Replacing row-by-row processing with set-based logic, removing functions that defeat indexes, and splitting queries that try to do too much at once.
  4. Configuration against workload. Memory, parallelism and I/O settings tuned for what the system actually does rather than what the installer assumed.
  5. Scheduling. Moving maintenance and batch work out of contention with the business day.
  6. Capacity. Sometimes the honest answer is that the hardware or tier is undersized, and tuning has only delayed that conversation.
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.

Tuning and development, together

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.

How RadixTrie does it

Performance tuning is part of RadixTrie's <a href="/services">support and managed DBA services</a> rather than a separately quoted project: senior engineers watch the signals above continuously across SQL Server, Oracle, PostgreSQL and MySQL/MariaDB estates and fix what they find. Our case study on <a href="/blog/how-radixtrie-helped-stabilise-postgresql-performance">stabilising PostgreSQL performance</a> shows the approach applied to one real environment.
<strong>Related reading:</strong> <a href="/blog/how-radixtrie-helped-stabilise-postgresql-performance">How RadixTrie helped stabilise PostgreSQL performance</a> &middot; <a href="/blog/database-health-check">The Database Health Check: What a Good One Covers and What It Finds</a> &middot; <a href="/blog/managed-dba-services-explained">Managed DBA Services Explained: What You Hand Over, What You Keep</a>
Tired of performance rescues? Make tuning routine.
TAGSPerformance TuningSQL ServerOraclePostgreSQLMySQLDatabase Support