Performance & SQL tuning

Define the slow operation, expected service level, input size, time window, and baseline. Separate queue delay, application execution, database work, and external waits. Capture comparable successful and slow runs.

Use evidence from job state, CPU, I/O, locks, and SQL activity. A single high CPU percentage or one slow user report is insufficient to choose a fix. Measure the outcome the business cares about, such as completed orders per minute or response-time percentiles.

The SQL plan cache records query-plan and execution information useful for identifying expensive statements. ACS tooling can inspect statements and Visual Explain can show the chosen access strategy. The cache is finite and changes over time.

Capture relevant statements or snapshots while the evidence exists. Compare parameters, row counts, selectivity, and access paths for slow and fast cases. A plan diagram is a hypothesis about cost; validate its practical impact with measured execution.

The optimizer estimates whether an access path is cheaper for the query and data distribution. A table scan can be reasonable for a large fraction of rows; an index may not match useful leading keys or may require expensive lookups.

Inspect predicates, conversions, selectivity, statistics, and required ordering. Do not force an index solely because it exists. Additional indexes impose write and storage costs, so evaluate both read improvement and overall workload impact.

Applying transformations to indexed columns or comparing incompatible types can prevent or weaken efficient keyed access, depending on optimizer support. Broad wildcard patterns and low-selectivity conditions can also limit benefit.

Express a range directly when possible and keep types aligned. For a timestamp day filter, compare against start and next-day boundaries rather than assuming a formatting function is free. Confirm the actual plan; some expressions can be optimized or supported by suitable indexes.

Measure record count, reads per result, key access, repeated opens, and work inside the loop. Eliminate unnecessary full scans and repeated lookups where a correct keyed path or set-based query helps.

Keep correctness first: selective reads must still include every qualifying record, and changing locking can change behavior. Compare representative volumes and concurrency. Do not assume CHAIN is universally faster than SQL or that changing opcodes is the main bottleneck.

Compare before and after on representative data and load, measuring throughput, elapsed distribution, resource usage, lock waits, and error rates. Verify output equivalence and transaction behavior.

Account for warm caches, concurrent workloads, and different parameters. A faster isolated query can slow writers or consume excessive memory under load. Document the measurement conditions and keep a rollback path for plan or workload regressions.