Find Queries Behind a Database Slowdown

Updated 7 November 2026 · Standard formula, shown below

When a database suddenly slows down, the first step is to identify which queries are consuming the most resources. This guide shows you how to locate those queries using built-in tools and logs, so you can fix the root cause quickly.

What You’ll Need

Access to the database server (SSH or a database client).
Permissions to view system views, logs, or run SHOW commands (typically admin or DBA rights).
Knowledge of your database type (MySQL, PostgreSQL, SQL Server, etc.) – the exact commands differ per system.

Step-by-Step

Check the slow query log. Most databases have a built-in slow query log. For MySQL, enable it with SET GLOBAL slow_query_log = 'ON'; and set a threshold like SET GLOBAL long_query_time = 2; (seconds). PostgreSQL uses log_min_duration_statement in postgresql.conf. SQL Server has the sys.dm_exec_query_stats view. Once enabled, the log will record every query that exceeds the threshold.
Look at current running queries. If the slowdown is happening right now, check active queries. MySQL: SHOW FULL PROCESSLIST; – look for queries in the ‘Sending data’ or ‘Copying to tmp table’ states. PostgreSQL: SELECT * FROM pg_stat_activity WHERE state = 'active'; – note the query column and query_start time. SQL Server: SELECT * FROM sys.dm_exec_requests WHERE status = 'running';.
Identify the heaviest queries by resource usage. For a historical view, use performance_schema in MySQL (e.g., SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;). PostgreSQL: SELECT query, calls, total_exec_time, mean_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; – you need the pg_stat_statements extension. SQL Server: SELECT TOP 10 query_hash, total_worker_time FROM sys.dm_exec_query_stats ORDER BY total_worker_time DESC;.
Check for locks and blocking. Slowdowns often come from blocked queries. MySQL: SELECT * FROM information_schema.innodb_trx; to see transactions. PostgreSQL: SELECT * FROM pg_locks; and look for conflicting locks. SQL Server: SELECT * FROM sys.dm_tran_locks;.
Examine the execution plan of the top query. Once you have the query text, run EXPLAIN (MySQL/PostgreSQL) or SET SHOWPLAN_ALL ON (SQL Server) to see if it uses indexes, does table scans, or creates temp tables. Look for ‘Using temporary’ or ‘Using filesort’ in MySQL, or ‘Seq Scan’ in PostgreSQL.
Check server metrics. High CPU, I/O, or memory can make even good queries slow. Use top or htop on the server, and check database-specific metrics like buffer cache hit ratio. If the whole server is saturated, the queries may not be the root cause – but they are still the ones suffering.
Reproduce the query manually. Run the identified query in a client with timing enabled (\timing in psql, SET profiling=1; in MySQL). This tells you if it is consistently slow or just under load.
Fix the query. Common fixes: add an index (but test with EXPLAIN first), rewrite the query to avoid SELECT * or functions on indexed columns, or break a large query into smaller ones. For locks, kill the blocking session (KILL ; in MySQL, SELECT pg_terminate_backend() in PostgreSQL).

Common Mistakes

Ignoring the slow query log because it’s off. Many databases have it disabled by default. Turn it on before you need it.
Looking only at the slowest query by execution time. A query that runs 1000 times a second and takes 0.1s each may be more damaging than a 10s query that runs once.
Checking only the database, not the server. If CPU is at 100% due to a backup process, your queries will be slow even if they are perfect.
Adding an index without checking the execution plan. An index that is never used just wastes space and slows writes.
Killing a transaction without checking if it is the root cause. You might kill a long-running report that is actually fine, while the real problem is a lock held by another session.

Tips

Set up slow query logging permanently with a reasonable threshold (e.g., 1-2 seconds) so you have data when problems occur.
Use a query digest tool like pt-query-digest (Percona Toolkit) to aggregate similar queries and see the total impact.
Monitor regularly with a tool like Prometheus or Grafana, but also know how to query the system views manually.
When you find a slow query, read the execution plan from left to right – the most expensive operation is usually the one with the highest cost.
Test changes on a staging server first, and always back up your data before altering indexes or killing transactions.

Troubleshooting

I don’t see any slow queries, but the database is still slow. Check for I/O bottlenecks – use iostat or iotop. Also check if the database is waiting on network or memory. Use vmstat to see if the system is swapping.

The slow query log is empty. Make sure the threshold is low enough – if you set long_query_time to 10 seconds, you may miss many queries. Also ensure the log is enabled and the file path is writable.

I found a slow query, but adding an index didn’t help. The query may be using a function on the indexed column (e.g., WHERE DATE(created_at) = '2024-01-01' prevents index use). Rewrite it to WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'.

I see a locked query, but I don't know who is blocking it. In MySQL, run SHOW ENGINE INNODB STATUS; and look for 'LATEST DETECTED DEADLOCK'. In PostgreSQL, use SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock'; and check the blocking_pid column.

The slowdown happens only at certain times. Check if a scheduled job (backup, report) coincides. Use cron logs or the database's own job scheduler to correlate.

Frequently Asked Questions

Q: How do I enable the slow query log in MySQL?
A: Run SET GLOBAL slow_query_log = 'ON'; and optionally set long_query_time to a lower value (e.g., 1 second). To make it permanent, add these to your my.cnf file.

Q: What is the difference between total_exec_time and mean_exec_time in pg_stat_statements?
A: total_exec_time is the sum of all executions, while mean_exec_time is the average per execution. A query with high total but low mean runs many times; a high mean indicates a single slow execution.

Q: Can I kill a query that is taking too long?
A: Yes, but first check if it's part of a transaction that might need to be rolled back. Use KILL in MySQL or pg_terminate_backend in PostgreSQL. Be cautious – killing a query can leave locks or cause application errors.

Q: Why does my query use a 'Seq Scan' in PostgreSQL even with an index?
A: The planner may estimate that scanning the whole table is faster than using the index, especially if the table is small or the query returns a large percentage of rows. You can force an index with SET enable_seqscan = off; for testing, but don't do this in production.

Q: How do I find the most frequent queries, not just the slowest?
A: Use the events_statements_summary_by_digest in MySQL and order by COUNT_STAR or SUM_ROWS_EXAMINED. In PostgreSQL, use pg_stat_statements and order by calls.

Q: What if the database is slow but no query seems abnormal?
A: Check the server's overall health – CPU, memory, disk I/O, and network. Also check for replication lag if you have replicas, or check if the database is running out of connections.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.