Why are logical reads for windowed aggregate functions so high?

by

Logical reads are counted differently for worktables: there is one ‘logical read’ per row read. This does not mean that worktables are somehow less efficient than a ‘real’ spool table (quite the reverse); the logical reads are just in different units.

I believe the thinking was that counting hashed pages for worktable logical reads would not be very useful because these structures are internal to the server. Reporting rows spooled in the logical reads counter makes the number more meaningful for analysis purposes.

This insight should make the reason your formula works clear. The two secondary spools are fully read twice (2 * COUNT(*)), and the primary spool emits (number of group values + 1) rows as explained in my blog entry, giving the (COUNT(DISTINCT CustomerID) + 1) component. The plus one is for the extra row emitted by the primary spool to indicate the final group has ended.

Paul

More Related Contents:

  1. Efficiently convert rows to columns in sql server
  2. T-SQL split string
  3. How can I get column names from a table in SQL Server?
  4. Conditional WHERE clause in SQL Server
  5. How can I select the first day of a month in SQL?
  6. Multi-statement Table Valued Function vs Inline Table Valued Function
  7. SQL Server Insert if not exists
  8. GROUP BY to combine/concat a column [duplicate]
  9. Select statement to find duplicates on certain fields
  10. How to set table name in dynamic SQL query?
  11. Using a Variable in OPENROWSET Query
  12. How to find current transaction level?
  13. EXISTS vs JOIN and use of EXISTS clause
  14. Check for file exists or not in sql server?
  15. Group DateTime into 5,15,30 and 60 minute intervals
  16. Why is a UDF so much slower than a subquery?
  17. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  18. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  19. UNIX_TIMESTAMP in SQL Server
  20. How can I convert a Sql Server 2008 DateTimeOffset to a DateTime
  21. How can I group by date time column without taking time into consideration
  22. Generate random int value from 3 to 6
  23. Add emoji / emoticon to SQL Server table
  24. Select SQL Server database size
  25. Backup a single table with its data from a database in sql server 2008
  26. COALESCE Function in TSQL
  27. Cannot Resolve Collation Conflict
  28. Try_Convert for SQL Server 2008 R2
  29. Display next event date
  30. Sql query for tree table

Leave a Comment