Database Performance Tuning Guide

This is an original planning guide. It is not a vendor manual, an official standard, or legal advice. Example addresses use documentation ranges. Review every command before you paste it.

A slow statement has a plan you can point at

Performance work starts from a statement you already have, not from a feeling that the database is slow. The plan visualizer reads an explain you paste. The index advisor drafts a suggestion. A draft is not an index until you have seen the plan before and after, on a lab or a copy.

Capture the statement, the time, and the rows it touched. A query that is slow because it reads the whole table is a filter problem or an index problem. You do not know which until the plan says so. Adding three indexes because a list suggested them is three changes.

The configuration guide owns memory and the server settings. This page owns the statement and the index. A bigger buffer will not fix a missing filter. A new index will not fix a server that is out of disk. Say which problem you are in.

Read-only explain is the first run. You do not fix a production plan by experimenting on the production database during the busy hour. A copy or a quiet lab is the place the new index is created first.

One statement, a plan before, a plan after

Take one statement from the slow log or from the ticket. Run explain on the lab with enough fake rows that the plan has something to choose. Save the plan. If the lab is empty, the plan is a toy and you say so.

If the plan shows a scan you did not expect, check the filter first. A missing condition is not an index. If the filter is right and the column is selective, draft one index. Create it on the lab. Explain again. Keep both plans in the ticket.

Write the cost you accepted. An index speeds the read and slows the write. If this table is inserted all day, say that. If you cannot say it, you are not ready to add the index on the live table.

The monitoring guide is where this statement becomes a number you watch. One statement fixed and unwatched will return. One statement is still the whole of this window.

A change you can finish

Production gets the one index, or the one rewritten filter, after the two plans are in the ticket and a second person has seen them.

Rollback is dropping that index, or putting the previous statement back. You can do either without a restore if you changed only that one thing.

Copy one real statement into the ticket. Remove any literal personal data.
Explain it on a lab that has a realistic number of fake rows.
Save the plan.
Fix the filter or add one index, not both.
Explain again and save the second plan.
Write who is hurt by the extra index, if anyone.
Schedule the production change as its own short window.

Mistakes that outlive the command

database performance fails in the same few ways. Each one looks finished on the day you type it. The list is here so the ticket can forbid them before the window, not so you can recognise them in an incident review.

Adding indexes from a list you did not explain.
Explaining an empty table and trusting the plan.
Changing the config in the same window as the index.
Experimenting on production during the busy hour.
A statement pasted with real customer values.
Five rewrites of the SQL before the first plan is saved.
An index on every column in the filter.
No note about the write cost.
Calling a draft advisor line a deployed index.
No before-and-after plan.

Decisions to write down before the window

Decision 1 for database performance. The statement is one, and its source is named. Write that decision in the ticket before you change the host. The proof is the smallest command or screen that confirms it, recorded with the hostname and the time. If the host disagrees, the ticket changes. A second change does not go on top of a failed check. The rollback is the previous value you wrote down, not a reboot that hides which line mattered.

Decision 2 for database performance. Literals that identify a person were removed. Write that decision in the ticket before you change the host. The proof is the smallest command or screen that confirms it, recorded with the hostname and the time. If the host disagrees, the ticket changes. A second change does not go on top of a failed check. The rollback is the previous value you wrote down, not a reboot that hides which line mattered.

Decision 3 for database performance. The lab has enough fake rows to make the plan meaningful. Write that decision in the ticket before you change the host. The proof is the smallest command or screen that confirms it, recorded with the hostname and the time. If the host disagrees, the ticket changes. A second change does not go on top of a failed check. The rollback is the previous value you wrote down, not a reboot that hides which line mattered.

Decision 4 for database performance. The before plan is saved. Write that decision in the ticket before you change the host. The proof is the smallest command or screen that confirms it, recorded with the hostname and the time. If the host disagrees, the ticket changes. A second change does not go on top of a failed check. The rollback is the previous value you wrote down, not a reboot that hides which line mattered.

Decision 5 for database performance. You changed the filter or the index, not both. Write that decision in the ticket before you change the host. The proof is the smallest command or screen that confirms it, recorded with the hostname and the time. If the host disagrees, the ticket changes. A second change does not go on top of a failed check. The rollback is the previous value you wrote down, not a reboot that hides which line mattered.

Decision 6 for database performance. The after plan is saved. Write that decision in the ticket before you change the host. The proof is the smallest command or screen that confirms it, recorded with the hostname and the time. If the host disagrees, the ticket changes. A second change does not go on top of a failed check. The rollback is the previous value you wrote down, not a reboot that hides which line mattered.

Decision 7 for database performance. The write cost is a sentence. Write that decision in the ticket before you change the host. The proof is the smallest command or screen that confirms it, recorded with the hostname and the time. If the host disagrees, the ticket changes. A second change does not go on top of a failed check. The rollback is the previous value you wrote down, not a reboot that hides which line mattered.

Decision 8 for database performance. Config changes belong to the configuration guide. Write that decision in the ticket before you change the host. The proof is the smallest command or screen that confirms it, recorded with the hostname and the time. If the host disagrees, the ticket changes. A second change does not go on top of a failed check. The rollback is the previous value you wrote down, not a reboot that hides which line mattered.

Decision 9 for database performance. Production waits for a second reader. Write that decision in the ticket before you change the host. The proof is the smallest command or screen that confirms it, recorded with the hostname and the time. If the host disagrees, the ticket changes. A second change does not go on top of a failed check. The rollback is the previous value you wrote down, not a reboot that hides which line mattered.

Decision 10 for database performance. Rollback is drop-the-index or restore-the-statement. Write that decision in the ticket before you change the host. The proof is the smallest command or screen that confirms it, recorded with the hostname and the time. If the host disagrees, the ticket changes. A second change does not go on top of a failed check. The rollback is the previous value you wrote down, not a reboot that hides which line mattered.

Decision 11 for database performance. Monitoring will watch this statement afterwards. Write that decision in the ticket before you change the host. The proof is the smallest command or screen that confirms it, recorded with the hostname and the time. If the host disagrees, the ticket changes. A second change does not go on top of a failed check. The rollback is the previous value you wrote down, not a reboot that hides which line mattered.

Decision 12 for database performance. The next slow statement stays in the queue. Write that decision in the ticket before you change the host. The proof is the smallest command or screen that confirms it, recorded with the hostname and the time. If the host disagrees, the ticket changes. A second change does not go on top of a failed check. The rollback is the previous value you wrote down, not a reboot that hides which line mattered.

The hour you stop

Write the database name, the host, and the clock time before you change anything for database performance. If this database is serving people and you do not have a copy, this hour is a plan. A plan you can read tomorrow is a success. A change you cannot undo is not.

Use a lab or a restored copy. The rows you read are rows you are allowed to hold. You do not point a client at a database, a cluster, or an account you do not administer. The tools on this site run in the browser. They do not log in, and they do not scan.

prompt-for-this is a reminder that a secret is missing. It is not a password, a token, or a key you save. The real secret stays in the password store. If a connection string, a log, or a ticket still contains a live secret, stop and rotate it. Then delete the copy.

One change. A config line, an index, a grant, and a restore do not share a window. If two things move and the outcome is wrong, you will not know which one did it. The rollback is the previous file or the backup you already tested.

A second person reads the scope aloud: which host, which database, which account. If they cannot find it in the ticket, the ticket is not ready. Silence is not agreement.

The lab listens on localhost. 127.0.0.1 is the address you bind until a firewall story exists. example.com is not your host. A generated file that listens on every address is a file you edit before it runs.

Do not run a statement you cannot undo. A delete or a drop without a backup and a where-clause you have read is the mistake this page is here to prevent. Read the statement. Count the rows it will touch. Then decide.

Keep the previous config and the previous dump. A generator draft is not the previous file. The previous file is what you put back if the new one is wrong. You practised the reload with a second session open when the change can lock you out.

If a step fails twice, stop. The failure is the result. Adding a command from a forum beside a half-applied config produces a database nobody can explain. Rebuild the lab from the copy you kept.

When the checks pass, the next database waits until someone else can repeat the proof from the notes. database performance is done for this window when the proof is written, not when the client printed a prompt.

This page is a planning guide. It is not a vendor manual, not an official standard, and not legal advice. If an auditor must see the decision, they see the ticket and the evidence, not this paragraph.

Hand the finished note to the neighbouring guide that owns the next step, and leave this database alone until that guide has its own window.

Performance checks before the index reaches production

Check 1 in Performance checks before the index reaches production. One statement is in the ticket. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 2 in Performance checks before the index reaches production. The before plan is attached. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 3 in Performance checks before the index reaches production. The lab row count is written. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 4 in Performance checks before the index reaches production. Only one change was made. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 5 in Performance checks before the index reaches production. The after plan is attached. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 6 in Performance checks before the index reaches production. A second person can see the difference. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 7 in Performance checks before the index reaches production. No personal literals remain. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 8 in Performance checks before the index reaches production. The write cost is stated. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 9 in Performance checks before the index reaches production. The config file was not edited. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 10 in Performance checks before the index reaches production. Rollback is a single drop or a single revert. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 11 in Performance checks before the index reaches production. Production is still on the old plan. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 12 in Performance checks before the index reaches production. The monitoring note names the statement. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 13 in Performance checks before the index reaches production. The hostname in the prompt matches the hostname in the ticket for database performance. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 14 in Performance checks before the index reaches production. The result line includes the time and the person, not only the command. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 15 in Performance checks before the index reaches production. You can point at the rollback value without searching a chat history. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Check 16 in Performance checks before the index reaches production. Done is a sentence a second person can repeat: the proof for database performance happened, and the next host is not started early. Record the device name, the result, and the time on the same line. If the result does not match this check, stop and fix the ticket before you add another change. The rollback for this check is the previous value you wrote down, not a reboot. A reboot hides whether the check was the one that mattered. When the check passes, move to the next one. When it fails, the incident is this check.

Tools for this guide

These tools run in the browser on this site. They do not log into your machines.

Guides in this cluster

These supporting pages belong to this pillar. A published page is linked. The others are named until they are published.