Optimise a SQL Server query with execution plans

Collect evidence before adding an index

Optimising a SQL Server query requires more than looking for a table scan. Record the query, parameter types, database version, compatibility level, indexes and data distribution. Note whether the delay is constant or appears only for particular customers or times. Blocking waits call for a different investigation from excessive logical reads. The following example uses a temporary table with fictional orders. It illustrates a controlled comparison, not a measured client result or a production recommendation for your schema. Keep the original statement and plan available throughout the investigation.

Estimated plans, actual plans and statistics

An estimated execution plan does not contain runtime measurements for an executed query. An actual execution plan collects information during execution, so the query really runs. Use a safe test environment with the necessary permissions. SET STATISTICS IO reports logical reads, including pages read from the data cache, while SET STATISTICS TIME reports CPU and elapsed times. Returned row counts and pages read are different quantities. Compare both information sources for the same query and data rather than treating a shorter result set as proof of lower I/O. Preserve the complete messages for review.

-- SQL Server example. Use a NEW session in a separate test database.
-- Never run this in production. Stop on the first error and close the session.
-- Enable Include Actual Execution Plan in your SQL client before running.
CREATE TABLE #cg_sqlserver_orders (
    id int NOT NULL PRIMARY KEY,
    customer_id int NOT NULL,
    created_at datetime2(0) NOT NULL
);
INSERT INTO #cg_sqlserver_orders VALUES
    (1,42,'2026-08-31T23:59:59'), (2,42,'2026-09-01T00:00:00'),
    (3,42,'2026-09-01T12:00:00'), (4,42,'2026-09-01T12:00:00'),
    (5,42,'2026-09-02T00:00:00'), (6,7,'2026-09-01T12:00:00'),
    (7,42,'2026-09-01T23:59:59'), (8,42,'2026-09-02T00:00:01');

DECLARE @customer_id int = 42;
DECLARE @from datetime2(0) = '2026-09-01T00:00:00';
DECLARE @until datetime2(0) = '2026-09-02T00:00:00';
SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT id, customer_id, created_at FROM #cg_sqlserver_orders
WHERE customer_id = @customer_id AND created_at >= @from AND created_at < @until
ORDER BY created_at DESC, id DESC;

CREATE INDEX cg_sqlserver_customer_date_id
ON #cg_sqlserver_orders(customer_id, created_at, id);

SELECT id, customer_id, created_at FROM #cg_sqlserver_orders
WHERE customer_id = @customer_id AND created_at >= @from AND created_at < @until
ORDER BY created_at DESC, id DESC;

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;
-- Expected IDs in both results: 7, 4, 3, 2. Inspect each SELECT separately.
-- Index creation also produces messages; do not confuse them with SELECT costs.
-- Eight rows cannot establish a production speedup. A scan can be appropriate.

Read operators in context

Compare estimated and actual row counts at relevant operators. Large differences may justify reviewing statistics, parameters and data distribution. Investigate residual predicates, sorts, spills and implicit conversions, but establish why they occur before changing anything. A seek followed by many lookups can cost more than a scan. A missing-index suggestion is an investigation lead, not automatic approval: it does not settle write overhead or the value of overlapping indexes. Do not change the query, indexes and server settings simultaneously when the objective is to understand the effect of one intervention.

Validate different parameters and the same result

The demonstration expects IDs 7, 4, 3 and 2. It includes another customer, both date boundaries and equal timestamps. In a real application, also test small and large customers, frequent and rare values and empty results. Parameter-dependent plans can behave differently across these cases. Recompiling or forcing a plan is not a default remedy without analysis. Check business results and access restrictions rather than only row counts or a more attractive graphical plan. Preserve the same isolation requirements: adding NOLOCK to hide blocking can change correctness and is not an acceptable substitute for diagnosis.

Make the change operationally controlled

Measure multiple runs with recorded cache conditions and concurrent load. Do not clear a production cache to make a comparison look cleaner. Then compare insert and update behavior and index storage as well. Agree rollback procedures and stop criteria before modifying a production index. This T-SQL example has not been executed against SQL Server here; its expected IDs describe fixture logic, not a validated SQL Server plan or timing. Explicitly adapt the version, privileges and schema to the actual environment. Record unresolved risks instead of describing a plausible index as a proven optimisation.

Continue the investigation

A query plan is one part of the diagnosis. The following references connect the technical investigation to a scoped SQL service. Demonstrations use fictional data, not the results of a client engagement. For an enquiry, start with a problem description and anonymised context. Do not send passwords, complete database dumps or customer personal data.

• Choose database indexes from real Laravel queries
• Investigate a slow MySQL query with EXPLAIN
• Discuss SQL query optimisation
• Reproducible SQL demonstration
• Request a SQL analysis