Why Are My Windowed Aggregate Queries Showing High Logical Reads?

High Logical Reads in Windowed Aggregate Function Execution Plans with Common SubExpression Spools

High reported logical reads for large tables are often observed in execution plans utilizing common subexpression spools. The formula for worktable logical reads is:

Worktable Logical Reads = 1 + NumberOfRows * 2 + NumberOfGroups * 4

Explanation

Unlike conventional spool tables, worktables count logical reads per row read, resulting in inflated logical read counts. This is because worktables are internal to the server, and hashed page counting is deemed less valuable for analysis.

The formula breaks down as follows:

1. 1x Logical Read: The worktable is created and initialized.
2. 2x Logical Reads per Row: The primary spool reads each row twice, once to insert into the worktable and again to read from the worktable for aggregation. The secondary spools also read each row twice.
3. 4x Logical Reads per Group: The primary spool emits a row to indicate the start of each new partition, and it outputs a dummy row to finalize the processing for the final group. These additional rows account for the 4x count.

Additional Insights

Paul White, in his blog post, explains that the formula aligns with the execution plan, where the two secondary spools are fully read twice and the primary spool emits (number of groups 1) rows. The extra row is emitted by the primary spool to signify the final group's end.

Conclusion

The formula for worktable logical reads is a useful tool for understanding the inflated logical read counts observed in execution plans with common subexpression spools. By recognizing that worktables count logical reads differently, it becomes easier to interpret the read statistics and assess the efficiency of the plan.

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