Worktables & Hash Tables

Question

Post reply

Worktables & Hash Tables

Viewing 9 posts - 16 through 23 (of 23 total)

You must be logged in to reply to this topic. Login to reply

SQLSACT SSC-Insane Points: 21838 More actions · Answer 1

GilaMonster (2/13/2013)
Why would you see a tempDB allocation when something's not spilling to TempDB?

From Grant:

All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time

None of the temporary objects are memory only

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Grab Kalen's book, Adam Machanic's memory grants video and go over them.

Memory grants for sorts are, oddly enough, memory. If the process can't get enough memory, then the sort spills to TempDB. It would be decidedly pointless to request memory, get enough memory for the sort and still write to TempDB.

Oh, and spills are not the same as work tables, temp tables or table variables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLSACT SSC-Insane Points: 21838 More actions · Answer 3

GilaMonster (2/13/2013)
Grab Kalen's book, Adam Machanic's memory grants video and go over them.
Memory grants for sorts are, oddly enough, memory. If the process can't get enough memory, then the sort spills to TempDB. It would be decidedly pointless to request memory, get enough memory for the sort and still write to TempDB.
Oh, and spills are not the same as work tables, temp tables or table variables.

Thanks

Memory grants for sorts are, oddly enough, memory. If the process can't get enough memory, then the sort spills to TempDB

How does this process differ for Hash Joins?

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

SQLSACT (2/14/2013)
How does this process differ for Hash Joins?

Why don't you go and read the book that's been recommended several times, do the research and see for yourself?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398821 More actions · Answer 5

SQLSACT (2/13/2013)
GilaMonster (2/13/2013)
Why would you see a tempDB allocation when something's not spilling to TempDB?
From Grant:
All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time
None of the temporary objects are memory only

You're conflating multiple different things into one. Sorts are different. You'll note in the quote you threw out there, I don't say sorts.

"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

SQLSACT SSC-Insane Points: 21838 More actions · Answer 6

SQLSACT

SSC-Insane

Points: 21838

February 14, 2013 at 4:00 am

#1587814

Thanks Gail and Grant

SQLSACT SSC-Insane Points: 21838 More actions · Answer 7

Hi Guys

I know this post is getting old, I figured this response would help someone else as much as it did me

http://blogs.msdn.com/b/craigfr/archive/2006/08/10/687630.aspx

Thanks

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 8

Grant Fritchey (1/31/2013)
Get a copy of SQL Server 2008 Internals by Kalen Delaney. Then read it 100 times. You'll still be confused (as I am) but less so.

Grant, i have read this text around 10 times and it always put smile on my face So i decided this time to post something which often came in my mind when i was here .That

if a master like you can get confuse then i shouldn't get hopelesss to open this book again and again. it actually motivates me :-).

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Grant Fritchey SSC Guru Points: 398821 More actions · Answer 9

Bhuvnesh (3/8/2013)
Grant Fritchey (1/31/2013)
Get a copy of SQL Server 2008 Internals by Kalen Delaney. Then read it 100 times. You'll still be confused (as I am) but less so.
Grant, i have read this text around 10 times and it always put smile on my face So i decided this time to post something which often came in my mind when i was here .That
if a master like you can get confuse then i shouldn't get hopelesss to open this book again and again. it actually motivates me :-).

Thanks. Happy to help.

"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