Why Do Windowed Aggregate Functions with Common Subexpression Spools Result in Such High Logical Reads?

Why are Logical Reads So High for Windowed Aggregate Functions with Common Subexpression Spools?

You've noticed that in execution plans with common subexpression spools, the reported logical reads for large tables are significantly higher than expected. Investigation reveals a formula that appears to hold true: Worktable logical reads = 1 NumberOfRows 2 NumberOfGroups 4.

Understanding the Formula

The high logical reads arise from the unique way worktables handle reads. Logical reads in this context refer to the number of rows read, not hashed pages. This means that for each row processed, the worktable registers one logical read.

Breakdown of Logical Reads

• 1: The initial read to process the first row.
• NumberOfRows * 2: Each row is read twice during the two passes of the secondary spools.
• NumberOfGroups * 4: The primary spool emits four rows per group: one to start the group, one to end the group, and two to hold the aggregate values.

Replication Attempt with Lower Logical Reads

Your attempt to replicate the process with a heap table shows only 11 logical reads because:

• Less Rows: Your test table has only 5 rows compared to the larger #Orders table.
• No Nested Loops: Your query uses an OUTER APPLY instead of nested loops, which eliminates the need for a dummy row.

Conclusion

The formula you found holds true because it accurately reflects the number of rows read by the worktable. The higher logical reads are due to the row-based counting method, not inefficiency. The nested loops plan used in your original query amplifies this count further.

The above is the detailed content of Why Do Windowed Aggregate Functions with Common Subexpression Spools Result in Such High Logical Reads?. For more information, please follow other related articles on the PHP Chinese website!