Reporting Is a Different Workload

Most database advice is written for OLTP — many small transactions, each touching a handful of rows, optimized for concurrency and latency. Reporting is close to the opposite, and applying OLTP tuning instincts to a reporting query is a good way to spend a week and improve nothing.

The differences that matter

| | OLTP | Reporting | |---|---|---| | Rows per query | 1 to a few hundred | thousands to millions | | Operation mix | reads and writes | reads, almost exclusively | | Latency target | milliseconds | seconds is fine, minutes is not | | Aggregation | rare | central | | Concurrency | very high | low, but each query is large | | Indexing that helps | narrow, selective, seek-oriented | wide covering indexes, columnstore | | Parallelism | usually undesirable | usually desirable | | Memory grants | small | large; the main source of contention | | Data freshness needed | current | usually "as of last night" is acceptable |

That last row is the most useful one and the most often skipped. If a report can tolerate data from last night, an enormous range of solutions opens up — pre-aggregated tables, snapshots, a readable secondary. If it must be current to the second, you are constrained to querying live OLTP tables and your options narrow considerably.

Ask that question about your report before anything else. It is a business question, not a technical one, and the answer determines which of Lesson 5's structural options are available.

The specific tension

Reporting and OLTP on the same database compete in three ways:

Locks. A long report holds shared locks under the default READ COMMITTED isolation, blocking writers. Writers hold exclusive locks, blocking the report. Lesson 4 covers this.

Memory. A report asking for a 4 GB grant reserves it for the query's duration. Other queries queue on RESOURCE_SEMAPHORE. One badly-estimated report can make an entire application feel slow for reasons no one connects to the report.

Buffer pool. A report scanning a 200 GB fact table evicts the pages the OLTP workload was using. The transactional queries then read from disk until the cache re-warms. The report's own runtime looks unchanged; everything else got slower.

Check whether that third one is happening:

-- What is occupying the buffer pool, by object
SELECT TOP (25)
       OBJECT_SCHEMA_NAME(p.object_id) AS schema_name,
       OBJECT_NAME(p.object_id)        AS table_name,
       i.name                          AS index_name,
       COUNT(*) * 8 / 1024             AS cached_mb
FROM sys.dm_os_buffer_descriptors AS bd
JOIN sys.allocation_units AS au ON au.allocation_unit_id = bd.allocation_unit_id
JOIN sys.partitions       AS p  ON p.hobt_id = au.container_id
JOIN sys.indexes          AS i  ON i.object_id = p.object_id AND i.index_id = p.index_id
WHERE bd.database_id = DB_ID()
  AND p.object_id > 100
GROUP BY p.object_id, i.name
ORDER BY cached_mb DESC;

Run it before and after the report. A fact table climbing to the top and pushing your transactional tables out is the mechanism, made visible.

And the memory contention:

-- Queries currently waiting for a memory grant, and what is holding it
SELECT  r.session_id,
        r.status,
        r.wait_type,
        r.wait_time / 1000.0                     AS wait_seconds,
        mg.requested_memory_kb / 1024            AS requested_mb,
        mg.granted_memory_kb   / 1024            AS granted_mb,
        mg.used_memory_kb      / 1024            AS used_mb,
        mg.query_cost,
        SUBSTRING(t.text, 1, 200)                AS query_snippet
FROM sys.dm_exec_query_memory_grants AS mg
JOIN sys.dm_exec_requests            AS r ON r.session_id = mg.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
ORDER BY mg.requested_memory_kb DESC;

granted_mb far exceeding used_mb is the excessive-grant pattern from Module 2 — a report reserving memory it never uses while other queries wait for it.

What good reporting tuning looks like

The priority order is genuinely different from OLTP:

1. Return fewer rows. The single most effective change, and the one people skip because it involves talking to whoever specified the report. Lesson 2.
2. Aggregate server-side. Move SUM/GROUP BY into SQL rather than the report engine. Lesson 2.
3. Cover the query. Wide covering indexes are correct here even though they would be excessive on an OLTP table.
4. Stabilize the plan. Reports are the main victims of parameter sniffing (Module 5).
5. Decouple from OLTP. RCSI, a readable secondary, or a separate reporting store. Lessons 4 and 5.
6. Pre-compute. Indexed views, summary tables, columnstore. Lesson 5.

Notice that steps 1 and 2 involve no database change at all. They are also the ones with the largest effect on a typical slow report, which is why the next lesson is about them.

The short version

Reporting is a different workload from OLTP — big reads, heavy aggregation, large memory grants — so the tuning priorities invert. Wide covering indexes make sense where they would not on a transactional table, and parallelism is usually helping rather than hurting. The first question to ask is how fresh the data actually needs to be, because if "as of last night" is acceptable that opens up pre-aggregation and a readable secondary, and if it does not, you are constrained to the live tables.