u/db_Forge

This keeps happening right before prod deploys.

Most of the time, query tuning doesn't start until something goes wrong. Timeout, alerts, someone staring at execution plans at 2 AM.

The annoying part is the slow query was already there.

It just didn’t hurt yet.

Staging data is usually tiny compared to prod. Dev data is clean, row counts are low, cost estimates look cheap, QA passes.

Then the same query hits real production volume and the plan changes.

Seeks become scans. Nested loops suddenly touch millions of rows. The query that looked harmless is now the problem.

I’ve seen the same thing with waits too. During load testing, IO waits or lock waits start creeping up, but they get ignored because nothing is technically broken yet. Then release happens and everyone acts surprised.

Parameter sniffing is another fun one. SQL Server caches the plan from the first execution, and if those parameters are weird, the cached plan can be terrible for the normal workload.

Some teams try to catch this earlier by profiling while the app is still in staging. Usually the risky queries are the ones with too many reads, CPU spikes, or big estimate gaps. We’ve used regular execution plans and profilers for this, including the query profiler in dbForge Studio when checking SQL Server stuff.

How do people here handle this? Do you look at query performance during staging, or mostly once something breaks in prod?

SQL autocomplete sounds simple until the query stops being simple. For simple SELECTs, almost any SQL autocomplete tool feels fine. Table name, column name, done.

The real test starts with messy and complex queries. Nested CTEs, aliases that made sense three years ago, subqueries, temp tables, joins across half the schema, and one column name that exists in five places.

That’s where basic suggestions are not enough. Good SQL IntelliSense should understand context. It should know which alias you’re inside, what columns are available there, and not suggest random objects from the whole database like it’s guessing in the dark.

We see this pain a lot regardless of the DBMS: SQL Server, MySQL, PostgreSQL, Oracle, etc. Autocomplete is useful only when it saves time without making you double-check every suggestion.

For us, the best autocomplete behavior is boring: correct syntax, correct names, correct context, fewer interruptions, and no fighting the editor. What usually breaks autocomplete for you?

SQL formatting feels like one of those things nobody cares about until they inherit a query from hell. A short query can look ugly and still be fine. But a huge report query with nested joins, random aliases, and conditions added by five different people over two years? That’s where formatting starts to matter. A SQL formatter won’t make bad SQL good. Would be nice, but no. It does make the mess easier to read. Same indentation, joins in predictable places, WHERE conditions not glued together like a ransom note. The only thing I hate is when a SQL beautifier goes too far and turns a normal query into a 200-line waterfall. Do you format SQL properly, or only clean it up when it gets embarrassing?

One thing that keeps coming up before deployments is that the comparison usually ends at the schema. 

But the order does matter. Not hard to miss. Most teams start there, and that's the right way to do it: diff dev and staging first so that any structural problems show up before the migration runs. It's easy to do this with dbForge's Schema Compare. You can see the objects in scope side by side, filter them, and review the sync script before it runs. 

But schema is just one part of the whole thing. Drift usually shows up in stored procedures and views. These tend to drift without making a sound. When there is an incident, a fix goes straight to staging and never comes back.  

By the next release, the object state between environments is quietly switched back. After the schema pass, an object compare catches this. 

It's easy to see why you should do schema first. If the schema isn't right yet, the object diff gets noisy, and you end up chasing drift that isn't really there. 

Permissions are usually the last thing to be checked and the first thing to cause problems in production. Over time, small access changes build up in production environments, and these don't show up in a schema diff.  

When things are moving quickly, the part of the workflow that is most likely to be skipped is running a dedicated permissions audit before the sync. That's when it usually matters the most. 

The hard part is that environment drift usually happens in things you weren't looking at, not things you were. Running schema, object, and permissions comparisons in that order every time is what makes the drift show up before the sync instead of after. 

Tools like dbForge Schema Compare make it easier to run those comparisons in one place. Makes it easier to keep the workflow consistent before a sync. 

Is this how you handle environment comparisons before deploys, or is something else usually the step that gets missed?