Optimising SQL for Analytical Workloads

A measured SQL performance-tuning case study using execution plans, logical reads, searchable date predicates, covering indexes, and grain-aware query design.

The previous article used ranking, cumulative totals, and cohorts to answer more demanding business questions. Those queries work comfortably on the AdventureWorks sample, but production analytical workloads rarely stay small or run only once.

Performance tuning should start before a report becomes visibly slow. It should also start with evidence. I want to reduce the work SQL Server performs without changing the meaning or result of the analysis.

This article is part of a twelve-part SQL sales analytics project based on AdventureWorks2025, supported by the sql-sales-analytics-portfolio repository.

SQL Sales Analytics Project Series

Reproduce the Analysis

Run the complete analytical workload optimisation script against AdventureWorks2025 in SQL Server Management Studio.

The script enables SET STATISTICS IO and SET STATISTICS TIME. Turn on Include Actual Execution Plan in SSMS before running it if you also want the graphical plans.

Unlike the analytical scripts in Articles 5 to 9, this one temporarily changes the database. It creates a clearly named nonclustered index on Sales.SalesOrderHeader, runs the comparisons, and drops the index in its final section. The opening cleanup also removes that same index if an earlier run was interrupted. It doesn’t alter any sales rows or leave a project object installed after successful completion.

Performance results depend on hardware, memory, cache state, SQL Server version, database settings, and concurrent activity. The elapsed times in SSMS may differ from mine. Logical reads and plan shape are the more useful comparisons in this controlled test.

The Performance Question

I use three representative problems:

Summarising a year of orders by customer
Filtering a single month of orders
Counting orders by territory

Each comparison must return the same business result before and after optimisation. A faster query that changes the population or grain isn’t an improvement.

The investigation focuses on four questions:

Which indexes already exist?
How much data does each query read?
Can SQL Server navigate to the required rows or must it inspect a wider structure?
Is the query joining data that the business question doesn't need?

Establishing a Baseline

AdventureWorks2025 supplies nonclustered indexes on CustomerID and SalesPersonID in SalesOrderHeader, but none beginning with OrderDate. SalesOrderDetail has a product index and a clustered key beginning with SalesOrderID. The index inventory query retrieves the supplied definitions from SQL Server metadata.

The baseline query summarises orders from 1 May 2024 through 30 April 2025 by customer account. It returns 16,391 customer accounts, 21,213 orders, and $51.35 million in net sales value.

WHERE OrderDate >= '2024-05-01'
  AND OrderDate < '2025-05-01'

The complete baseline query reads 686 logical pages from SalesOrderHeader on my local database.

A logical read means SQL Server requested an 8 KB data page from the buffer cache. It doesn’t necessarily mean that the page was read from disk. Logical reads are still valuable because they describe how much page-level work the query required independently of whether the data happened to be cached.

The actual plan shows a clustered index scan. There is no useful date-led index, so SQL Server reads the wider clustered structure and applies the date predicate as it goes.

Adding a Covering Date Index

I add OrderDate as the index key and include the columns needed by this small family of analytical queries.

CREATE NONCLUSTERED INDEX IX_Portfolio_SalesOrderHeader_OrderDate
ON Sales.SalesOrderHeader (OrderDate)
INCLUDE (CustomerID, TerritoryID, OnlineOrderFlag, SubTotal);

The complete index definition is deliberately tied to the access pattern being tested. OrderDate supports range filtering, while the included columns allow SQL Server to answer the queries without returning to the clustered index for each qualifying row.

Running the unchanged annual customer summary produces the same 16,391 accounts, 21,213 orders, and $51.35 million. Logical reads fall from 686 to 95, a reduction of 86.15%. The repeated query confirms that the SQL and result remain the same.

Annual customer summary	Logical reads	Change
Before covering index	686	Baseline
After covering index	95	-86.15%

The test index contains 31,465 rows, uses 139 pages, and occupies approximately 1.09 MB in this database. The index footprint query also exposes seeks, scans, and updates recorded since the index was created.

That footprint is small here, but the design isn’t free. Every insert, delete, or update affecting an indexed column may need to maintain another structure. The index also consumes memory and storage, adds statistics, and can overlap with future indexes. I would validate its value across the recurring workload rather than keep it because one query improved.

A missing-index suggestion isn’t an implementation plan

SQL Server can suggest an index for one statement without considering the full read and write workload, existing overlap, operational maintenance, or deployment constraints. Treat the suggestion as evidence to investigate, then test the combined workload.

Making the Date Predicate Searchable

An index can exist without being used efficiently. The predicate still needs to give SQL Server a searchable range.

I compare two April 2025 queries. Both return 2,279 orders and $5.22 million in net sales.

WHERE YEAR(OrderDate) = 2025
  AND MONTH(OrderDate) = 4

The function-wrapped version reads 139 pages, which is the complete demonstration index. SQL Server must calculate the year and month for index rows before deciding whether each one qualifies.

WHERE OrderDate >= '2025-04-01'
  AND OrderDate < '2025-05-01'

The range version reads 13 pages, a reduction of 90.65%. The predicate comparison contains both queries.

The range is also safer than an inclusive end date when a source column contains times. OrderDate < '2025-05-01' includes every time on 30 April without relying on a final timestamp such as 23:59:59.997.

This pattern is often described as SARGable, meaning the predicate can be used as a search argument. The practical point is simpler: compare the stored column with boundaries that let the engine navigate the index.

Querying at the Required Grain

Indexing isn’t the only way to reduce work. The territory question asks for order counts, and those rows already exist in SalesOrderHeader.

The order-grain query reads 95 header pages and returns the ten territory totals. The direct order-count query performs no line-level join.

I then reproduce the same totals by joining SalesOrderDetail and applying COUNT(DISTINCT SalesOrderID). The result is numerically identical, but the unnecessary line-join query reads the same 95 header pages plus 274 detail pages.

Territory order count	Header reads	Detail reads	Total reads
Query at order grain	95	0	95
Query after line join	95	274	369

The second version reads almost four times as many pages, performs a larger join, and needs a distinct aggregate to recover the order grain it discarded. On a larger fact table, that unnecessary work can affect memory grants, parallelism, tempdb use, and concurrency.

This is also a correctness issue waiting to happen. If I summed order-level SubTotal after joining the details, orders with several lines would be counted several times. Choosing the right grain improves performance and makes accidental duplication less likely.

Reduce work before tuning how it is performed

Removing an unnecessary table is often more dependable than trying to make the larger join faster. Start from the entity the business question actually asks about.

Reading Execution Plans Without Chasing Percentages

Graphical execution plans are useful, but the estimated cost percentages inside one plan aren’t elapsed-time measurements. They are optimiser estimates used to compare operators in that plan.

I look for several kinds of evidence together:

Access method: Is the engine scanning a structure or seeking to a range?
Estimated and actual rows: Large differences may indicate stale statistics, skew, or assumptions the optimiser couldn’t model well.
Rows flowing between operators: An early expansion can make later sorts, joins, and aggregates expensive.
Warnings: Spills, implicit conversions, and excessive memory grants deserve investigation.
Runtime statistics: Logical reads, CPU time, duration, and repeated production observations show whether the change helped outside the optimiser’s model.

An index seek isn’t automatically good, and a scan isn’t automatically bad. Reading most of a small table can be cheaper as a scan. The annual query still covers a large share of the data, while the narrow April range provides a much more selective index search.

A Practical Tuning Workflow

For analytical SQL, I would normally work in this order:

1Confirm the business result and required grain.
2Capture a representative baseline using the actual plan and runtime statistics.
3Look for unnecessary rows, columns, joins, sorts, and repeated calculations.
4Make predicates searchable and keep data types aligned.
5Review existing indexes before proposing another one.
6Test one material change at a time against the same parameters.
7Validate correctness as well as resource use.
8Observe the full workload before deploying and after release.

The script’s cleanup section drops the demonstration index. Keeping it would require broader workload and write-impact evidence that this isolated article can’t provide.

Business Recommendations

Establish performance baselines for recurring reports

Record representative parameters, row counts, logical reads, duration, and plan identity. Without a baseline, a change can feel faster while merely benefiting from a warm cache or quieter server.

Index the reporting workload, not individual queries in isolation

The tested date index supports several common filters and dimensions, but it should be assessed alongside ingestion, operational transactions, other reports, and existing indexes before deployment.

Preserve analytical grain in reusable models

The separate order and order-line views created in Article 4 help consumers begin from the right entity. Documentation and semantic models should make that choice obvious rather than expecting every report author to rediscover it.

Monitor regressions after deployment

Data distribution, parameters, statistics, and SQL Server upgrades can change plan choice. Query Store and workload monitoring are more reliable than assuming a plan that works today will remain optimal indefinitely.

Limitations

Small sample: AdventureWorks is too small to reproduce the pressure of a production analytical workload.
Local measurements: Elapsed time isn’t transferable between machines or cache states.
Single-user test: The benchmark doesn’t measure blocking, concurrency, or write overhead under load.
One index candidate: Other key orders, included columns, filtered indexes, or data-model changes may perform better across a broader workload.
No production history: Index usage statistics in this test cover only the current SQL Server instance since the demonstration index was created.
No columnstore comparison: Rowstore tuning is appropriate for this stage, but a dimensional analytical model may later justify columnstore evaluation.

The measured results show that query shape and a targeted index can reduce work materially. They don’t justify copying this exact index into an unrelated production database.

Next in the Series

The next article will design an analytics-friendly dimensional model from the operational sales data.

That will move the project beyond tuning queries over the OLTP schema and towards a model built around analytical workloads from the beginning.