March 16, 2011 at 5:31 pm
Can someone familiar with TableSpools take a look at the attached query plan? I'm beyond confused, I'm outright confuzzled.
I've got a TableSpool that's getting fed 83 rows... and ends up with 9379. :blink:
Whaaa?
Thanks in advance.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 16, 2011 at 6:04 pm
It looks like the Stream Aggregate is getting called in a loop. If you look at the actual output from it, it has 83 rows. The table spool is executed 113 times. 113x83=9379, which is the number of rows coming out of the table spool. That corresponds to this bit of your code: ,IsDelivered = MAX(CONVERT(int, D.IsComplete))
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 16, 2011 at 6:15 pm
Grant Fritchey (3/16/2011)
It looks like the Stream Aggregate is getting called in a loop. If you look at the actual output from it, it has 83 rows. The table spool is executed 113 times. 113x83=9379, which is the number of rows coming out of the table spool. That corresponds to this bit of your code: ,IsDelivered = MAX(CONVERT(int, D.IsComplete))
Ah hah, a bit of math and it suddenly makes more sense. I mis-remembered something I'd read (in your book, to boot... bad memory, bad bad) and thought the actual rows was the # of rows in the spool, not the number of reads of the rows.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 16, 2011 at 7:14 pm
Craig Farrell (3/16/2011)
Grant Fritchey (3/16/2011)
It looks like the Stream Aggregate is getting called in a loop. If you look at the actual output from it, it has 83 rows. The table spool is executed 113 times. 113x83=9379, which is the number of rows coming out of the table spool. That corresponds to this bit of your code: ,IsDelivered = MAX(CONVERT(int, D.IsComplete))Ah hah, a bit of math and it suddenly makes more sense. I mis-remembered something I'd read (in your book, to boot... bad memory, bad bad) and thought the actual rows was the # of rows in the spool, not the number of reads of the rows.
Ha! I'd be surprised if that bit was in my book. However, you look at enough of these things and you don't have to be as smart as Paul White to begin to figure out what's going on.... most of the time. And in those times, find Paul.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply