Database is slow

Symptom

Application-wide slowness; queries take much longer than usual and users are complaining.

1. First clarify what exactly is slow

Is everything slow, or a specific query/screen? When did it start, and does it coincide with a deploy/patch/data growth? Is it system-wide or a single DB? This narrows the search to one axis: CPU, I/O, locking or a plan change.

2. Look at active sessions and wait events

What is running and what is waiting right now? A dominant wait event (I/O, lock, log) points to the bottleneck.

Oracle

SELECT sid, username, status, event, sql_id FROM v$session WHERE status='ACTIVE' AND type='USER';

PostgreSQL

SELECT pid, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE state='active';

MySQL

SELECT id, user, command, time, state, info FROM information_schema.processlist WHERE command<>'Sleep' ORDER BY time DESC;

SQL Server

SELECT session_id, status, wait_type, wait_time, blocking_session_id FROM sys.dm_exec_requests WHERE session_id IN (SELECT session_id FROM sys.dm_exec_sessions WHERE is_user_process = 1);

MongoDB

db.currentOp({ active: true, secs_running: { $gt: 1 } })

Redis

SLOWLOG GET 10

3. Check for blocking

The most common cause of slowness is one session blocking others. Find the blocking session and identify the head of the chain.

Oracle

SELECT blocking_session, sid, seconds_in_wait, event FROM v$session WHERE blocking_session IS NOT NULL;

PostgreSQL

SELECT pid, pg_blocking_pids(pid) AS blocked_by, query FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid))>0;

MySQL

SELECT * FROM sys.innodb_lock_waits\G

SQL Server

SELECT session_id, blocking_session_id, wait_type FROM sys.dm_exec_requests WHERE blocking_session_id<>0;

4. Extract the most expensive queries

Find the top 10 queries consuming the most resources. Usually 90% of the problem comes from a handful of queries.

Oracle

SELECT sql_id, elapsed_time/1e6 sec, executions, sql_text FROM v$sql ORDER BY elapsed_time DESC FETCH FIRST 10 ROWS ONLY;

PostgreSQL

SELECT calls, total_exec_time, mean_exec_time, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;

MySQL

SELECT digest_text, count_star, sum_timer_wait/1e12 sec FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10;

SQL Server

SELECT TOP 10 total_elapsed_time/execution_count avg_us, execution_count, SUBSTRING(t.text,1,200) FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t ORDER BY total_elapsed_time DESC;

5. Examine the plan of the suspect query

Get EXPLAIN for the slow query. Look for full table scans, missing indexes, bad join order or stale statistics. If the plan changed suddenly, consider gathering statistics.

Oracle

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', NULL, 'ALLSTATS LAST'));

PostgreSQL

EXPLAIN (ANALYZE, BUFFERS) <query>;

MySQL

EXPLAIN ANALYZE <query>;

SQL Server

SET STATISTICS PROFILE ON; <query>; SET STATISTICS PROFILE OFF;

MongoDB

db.my_collection.find({...}).explain('executionStats')  // COLLSCAN or IXSCAN?

6. Verify system resources

At the OS level, check CPU, I/O wait (iowait), memory and disk usage. Check the cache hit ratio; if it is low, there may be a memory bottleneck. The problem may be outside the DB (full disk, noisy neighbor VM).

7. Decide: quick fix or permanent fix

For immediate relief: kill the runaway query, add the missing index, gather statistics. For a permanent fix: redesign the query/index, correct memory settings, ensure plan stability (baseline/hint).