Why Are My Windowed Aggregate Queries Showing Unexpectedly High Logical Reads When Using Common Subexpression Spools?

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

When using common subexpression spools in execution plans, notably for windowed aggregate functions, surprisingly high logical reads for large tables have been observed. This behavior raises concerns about the efficiency of such spools.

Root Cause: Unique Counting of Logical Reads for Worktables

Unlike traditional spool tables, logical reads for worktables, which are internal structures used for aggregation, are counted differently. Instead of measuring hashed pages, worktable logical reads are incremented for each row read. This unique counting method results in seemingly high counts, despite worktables being highly efficient.

Understanding the Formula

In your specific case, the formula you discovered, "Worktable logical reads = 1 NumberOfRows 2 NumberOfGroups 4," holds true due to the specific behavior of these spools:

• 1 : Initial overhead and final dummy row
• NumberOfRows * 2: Full read of both secondary spools
• NumberOfGroups * 4: Primary spool emits (NumberOfGroups 1) rows

Additional Insights

• The primary spool emits an additional row to signal the end of the final group.
• The formula accounts for this extra row in the final component, making it NumberOfGroups 1.

Troubleshooting Tips

While tracing page reads is not an option for worktables, understanding these unique counting mechanisms can help explain the observed high logical reads.

Reference

For further clarification, refer to:

• Query Tuning and Optimization Book, Chapter 3
• Paul White's blog post on common subexpression spools

The above is the detailed content of Why Are My Windowed Aggregate Queries Showing Unexpectedly High Logical Reads When Using Common Subexpression Spools?. For more information, please follow other related articles on the PHP Chinese website!