Most SQL Server performance problems are not spread evenly across thousands of queries. A small handful of statements — usually a few percent of the total — account for the majority of the CPU, IO and wait time. Tuning is therefore a prioritization problem before it is a rewriting problem: find the few that hurt, and fix those first.
Start with the wait, not the code
Before touching a single query, look at what the engine is waiting on. CXPACKET points at parallelism and often at a bad plan; PAGEIOLATCH and WRITELOG point at IO; LCK_M_ waits point at blocking and lock design. The wait tells you which layer to fix. Rewriting a query that is blocked on a lock is wasted effort.
Read the actual plan, top to bottom
Open the actual execution plan and read it the way the engine executes it. The most expensive operator is usually the one to attack. Common, high-value fixes in order of frequency:
- A key lookup on a large result — make the index covering with
INCLUDE. - A table scan where an index seek is possible — add or fix the index.
- A sort or hash join on a column that should be indexed — remove the work.
- A parameter-sniffing plan that is good for one value and terrible for another — consider
OPTION (RECOMPILE)or plan guides.
Fix the plan, then the query. The optimizer is usually right about what it is doing — it is just working with the wrong inputs.
Keep statistics honest
A surprising share of "mystery" slow queries are really stale-statistics problems. The optimizer estimates a row count that is off by an order of magnitude, picks a plan that is fine for the estimate and catastrophic for reality. Check sys.dm_db_stats_properties for last-updated times and sys.dm_db_index_usage_stats for what is actually being used, and let auto-update do its job on the tables that matter.
If your estate is full of legacy T-SQL that nobody dares to touch, our team in Bucharest does exactly this kind of production tuning — profiling the real workload, fixing the top offenders, and leaving the system measurably faster.