How I Built a SQL Diagnostic Tool That Works Without Touching Your Database

Most developers I've worked with write SQL every day. Very few of them are DBAs. According to the 2024 Stack Overflow Developer Survey — 65,000 developers across 185 countries — database administrators make up just 0.3% of the developer population. The tools built for SQL performance were designed for that 0.3%. I built QueryTuner for everyone else.

I've spent 13 years as an application architect. In that time, I've watched the same situation repeat itself across teams: a query is slow, the developer who wrote it has to fix it, and the tools available to them are either way too expensive or way too generic. Enterprise monitoring agents like pganalyze or Datadog Database Monitoring cost hundreds of dollars a month and require installing an agent with full database credentials. Generic AI LLMs don't know whether you're on Oracle or MySQL. There's nothing useful in between.

That gap is what QueryTuner tries to fill.

The Core Constraint: No Database Connection

The first decision I made was also the most important one. QueryTuner would not connect to any database.

Every enterprise SQL tool requires credentials. In most organizations, getting credentials approved takes longer than just fixing the query manually. I wanted something a developer could try in 30 seconds without asking anyone for permission.

The tradeoff is real. Without connecting to your database, QueryTuner can't see actual row counts, current index usage, or live execution plans. But it can analyze the SQL text itself — and most slow query problems come from a small set of well-known patterns. You don't need to connect to a database to spot a function wrapped around a column in a WHERE clause.

The Heuristic Engine

QueryTuner runs 12 deterministic rules against every query before anything else happens. These rules catch the patterns that cause most slow query problems in production:

Functions on indexed columns are the most common. If you write WHERE YEAR(created_at) = 2024, the database has to call YEAR() on every row before it can filter. The index on created_at becomes useless. The fix is a range condition: WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'. The index works again.

Leading wildcard LIKE patterns are the second most common. LIKE '%value' can't use a B-tree index. The database reads every row. Most developers don't know this until they see it in an execution plan for the first time.

Correlated subqueries in the SELECT clause are the most expensive. If you have a subquery inside your SELECT list, it runs once for every row in the outer query. On a table with 50,000 rows, that's 50,000 separate database lookups. A LEFT JOIN does the same work in a single pass.

Cartesian JOINs are the most dangerous. A JOIN without an ON clause multiplies every row in table A by every row in table B. On production tables with millions of rows, this can crash your database server. QueryTuner marks these as critical severity — the only finding type at that level.

The heuristic engine runs in under 200 milliseconds. It always runs, regardless of whether the LLM layer is enabled. This was a deliberate design choice. I wanted the tool to be useful even when the AI component is unavailable.

The LLM Layer

After the heuristics run, users can optionally enable an LLM layer — HuggingFace or OpenAI. The LLM adds plain-English narrative, a rewritten query using CTEs, and flags for assumptions it can't verify without knowing the actual schema.

The key design principle here: the LLM is additive. If it fails — cold start on the free tier, rate limit, network timeout — the user still gets complete structured findings from the heuristic layer. The tool does not degrade to an empty screen when AI is unavailable.

The Dialect Problem

This was the hardest part to get right. SQL is not one language. The correct way to create an index in production differs significantly across databases.

In PostgreSQL, you use CREATE INDEX CONCURRENTLY to avoid locking the table during index creation. Without CONCURRENTLY, all writes block until the index is built. On a busy production table, that can mean minutes of downtime.

In MySQL, the idiomatic form is ALTER TABLE orders ADD INDEX idx_name (column). The CREATE INDEX syntax also works, but ALTER TABLE integrates better with InnoDB's internal operations.

In Oracle, you add NOLOGGING to skip the redo log during index creation. This makes it significantly faster, but you can't recover the index from redo logs if something fails mid-creation. Use it during maintenance windows only.

In SQL Server, CREATE NONCLUSTERED INDEX ... WITH (ONLINE=ON) allows reads and writes to continue during index creation. This is an Enterprise edition feature. FILLFACTOR=90 leaves 10% of each page free for future inserts, reducing page splits over time.

In SQLite, there's no concurrent DDL. Index creation locks the entire database file. The only mitigation is scheduling it during low-traffic windows.

Generic advice — "add an index on customer_id" — is not enough. The statement a developer runs in production depends entirely on which database they're on. Getting this wrong can cause downtime.

I solved this by centralizing all dialect-specific logic in a single file: dialect_config.py. This is a dataclass-based config with one entry per database. Each entry holds the index DDL template, optimizer rewrite syntax, LLM system prompt context, and maintenance commands for that dialect. When the tool generates a recommendation, it calls get_dialect(db_type) and gets everything it needs from one place.

The practical benefit: adding a sixth dialect means adding one dataclass entry. No other files change.

Schema-Aware Confirmed Recommendations

By default, every index recommendation carries a confirmed: false flag. The tool is analyzing SQL syntax, not your actual database. It doesn't know whether the column exists, whether an index already covers it, or what the real table name behind an alias is.

If you paste your CREATE TABLE statements alongside the query, that changes.

QueryTuner parses the DDL, builds a schema map, and cross-references every detected column against it. If the column exists and no index covers it, the recommendation flips to confirmed: true. The DDL it generates uses your real table name — not a placeholder like <o_table>. Suggestions for indexes that already exist in your DDL are suppressed entirely.

For a developer who is about to run a CREATE INDEX on a production database, that distinction matters. confirmed: true means the recommendation was verified against their actual schema. confirmed: false means it's a pattern-based estimate worth investigating.

What I'd Do Differently

The alias resolution logic — matching o to orders — is the weakest part of the system. It works for common patterns (single-letter aliases, prefix matches) but fails for arbitrary aliases. This is the first thing I'd improve with more time.

The LATERAL join gap is the other known limitation. Correlated columns inside LATERAL joins are not detected. It's documented as an intentional xfail in the test suite and will be addressed when the execution plan parsing layer is built.

Try It

QueryTuner is open source under the MIT license.

• Live: querytuner.com
• Source: github.com/AutoShiftOps/querytuner
• API: POST /analyze — accepts query, dialect, optional schema DDL

Feedback is especially welcome from Oracle and SQL Server practitioners. Those are the dialects with the least real-world battle-testing, and the production edge cases are where the tool needs the most work.