Create a reproducible baseline
This is a written walkthrough; it does not include a live tuning video or assert a measured speedup. Identify one slow query, its parameters, database version and the business result it must preserve. Retain a representative sanitized dataset. Running a tiny example on a laptop and comparing it with a busy production server would not establish the effect of a query change.
Record more than elapsed time
Capture the actual execution plan and the relevant logical reads, row counts and elapsed-time observations. Note whether the cache is warm and whether other workloads are active. A query that appears fast on the second run may simply benefit from cached data. Use multiple observations with the same conditions and keep unsuccessful runs visible in the results.
Investigate a specific hypothesis
Suppose a fictional order report filters by OrderDate but applies a conversion to that column in the predicate. Consider whether expressing the requirement as a date range allows a more useful access path for the actual index and engine. The example assumes typed date parameters and requires validation of time boundaries. It is not a guarantee that an index seek is always faster than a scan.
-- Illustrative half-open range for one business period
SELECT OrderId, OrderDate, Amount
FROM dbo.Orders
WHERE OrderDate >= @StartDate AND OrderDate < @EndDate;Check correctness before keeping the change
Compare row keys, duplicate counts, NULL handling and aggregate totals between the original and candidate queries. Include an order exactly on the start boundary and one exactly on the end boundary. Check time-zone conversion outside the example when the business period is local but storage is UTC. A reduced runtime is not a useful result if the revised predicate excludes valid orders.
Document the tradeoff
An added index has storage and write-maintenance costs, and a plan that works for one parameter may behave differently for another. Test representative selective and broad parameter values. Keep the previous definition and a rollback procedure. Publish a measured result only with the workload, method and limitations needed to interpret it; otherwise describe the exercise as a hypothesis and validation procedure.
