THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Page Free Space

See also my SQL Server technical articles on SQLperformance.com

Partitioning and the Common Subexpression Spool

Published Wednesday, July 28, 2010 4:36 AM by Paul White

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Page Free Space - Paul White said:

In my last post I showed how SQL Server 2005 and later can use a Segment Spool to efficiently implement

July 28, 2010 5:12 AM
 

Martin said:

Hi Paul,

Great Article.

Have you any idea why the logical reads reported by "STATISTICS IO" for these spools are so much greater (about double) than when I try and simulate what is going on manually?

Example Script Here: http://stackoverflow.com/questions/4230838/why-are-logical-reads-for-windowed-aggregate-functions-so-high

March 4, 2011 8:24 AM
 

Paul White said:

Hi Martin,

(copied from my answer to your Stack Overflow question)

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

March 4, 2011 9:20 AM
 

Paul White: Page Free Space said:

This is a post for T-SQL Tuesday #43 hosted by my good friend Rob Farley . The topic this month is Plan

June 11, 2013 5:00 AM

Leave a Comment

(required) 
(required) 
Submit
Privacy Statement