SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Worktables & Hash Tables


Worktables & Hash Tables

Author
Message
SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4784 Visits: 2969
Hi All

After testing and researching - I want to make sure that my understanding is correct regarding Worktables & Hash Tables

>Worktables & Hash Tables are 2 different things
>Hash tables are only created when there is a Hash Join.
>Worktables are created when immediate results need to be stored somewhere
>Worktables & Hash Tables are both created in Memory first and spill to tempdb if necessary
>A Merge join will not employ a worktable if both inputs are unique

Please advise if I am on the right track here?

Thanks
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95455 Visits: 33013
Basically correct. Although, it really only matters to the internals engine. For your purposes, both are dealt with the same way. According the to the Internals book, a hash table goes to what is called a work file, but that's treated in all ways the same as a work table. Further, when you have a hash join and you look at the I/O stats of the query, that hash is evidenced as something called a work table. So, I just treat the hash as a type of work table since the external evidence is exactly the same and MS labels it that way.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4784 Visits: 2969
Thanks

Basically correct. Although, it really only matters to the internals engine. For your purposes, both are dealt with the same way. According the to the Internals book, a hash table goes to what is called a work file, but that's treated in all ways the same as a work table. Further, when you have a hash join and you look at the I/O stats of the query, that hash is evidenced as something called a work table. So, I just treat the hash as a type of work table since the external evidence is exactly the same and MS labels it that way.


wrt to the memory/tempdb situation

Are work/hash tables first created in memory and spilled to tempdb if necessary?

Thanks
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95455 Visits: 33013
SQLSACT (1/31/2013)
Thanks

Basically correct. Although, it really only matters to the internals engine. For your purposes, both are dealt with the same way. According the to the Internals book, a hash table goes to what is called a work file, but that's treated in all ways the same as a work table. Further, when you have a hash join and you look at the I/O stats of the query, that hash is evidenced as something called a work table. So, I just treat the hash as a type of work table since the external evidence is exactly the same and MS labels it that way.


wrt to the memory/tempdb situation

Are work/hash tables first created in memory and spilled to tempdb if necessary?

Thanks


Yes & no. All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time. But the primary work is done in memory, if it can. Otherwise it puts the excess on the place it reserved on the disk.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4784 Visits: 2969
Grant Fritchey (1/31/2013)
SQLSACT (1/31/2013)
Thanks

Basically correct. Although, it really only matters to the internals engine. For your purposes, both are dealt with the same way. According the to the Internals book, a hash table goes to what is called a work file, but that's treated in all ways the same as a work table. Further, when you have a hash join and you look at the I/O stats of the query, that hash is evidenced as something called a work table. So, I just treat the hash as a type of work table since the external evidence is exactly the same and MS labels it that way.


wrt to the memory/tempdb situation

Are work/hash tables first created in memory and spilled to tempdb if necessary?

Thanks


Yes & no. All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time. But the primary work is done in memory, if it can. Otherwise it puts the excess on the place it reserved on the disk.


Thanks

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


Which area of work will go to memory and what will go to disk?

But the primary work is done in memory


If memory cannot accomodate the primary work, is that spilled to disk?

I think I need to do some more research on this topic, still very confused
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95455 Visits: 33013
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.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4784 Visits: 2969
I've read your Execution Plans book and I've taken a lot from there.

This particular area of worktables/hashtables is eluding me though

Thanks for your help
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95455 Visits: 33013
Well what's the major issue. You're asking about very low level behavior. It's not immediately applicable to most tuning situations. I mean, there is a specific area called workspace memory used for temporary allocation of objects within a query that is pulled from the buffer pool. How is that useful to you if you have a query creating a work table? Fix the query, add the index, modify the structure... Do what's necessary to get by. Does it really matter exactly where the allocation goes?

If you need to look at allocations, start with sys.dm_os_memory_clerks. You can see what is being allocated to the different memory management allocations. You can look at the buffer pool through sys.dm_os_buffer_descriptors. These can help you see where the allocation is going.

Memory allocation for a query is shown in the actual execution plan. You can also look at sys.dm_exec_query_memory_grants for execution queries. To see specifically where information is going, take a look at sys.dm_db_session_space_usage. That's going to show you where the memory allocations are going.

As to precisely when the memory/disk spill occurs, I'm not sure. It's largely a question of how much memory do you have. Once there's not enough, everything goes to disk. You can kind of figure out if you're using disk or not by looking at the allocations available through the DMOs I listed above.

But I'm still unclear where this gets you, except a more complete understanding of why a hash, sort, or work table actually does cause things to slow down.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
SQLSACT
SQLSACT
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4784 Visits: 2969
Thanks

I understand the workspace memory area (Well, I hope I do). A query will request required memory and once the query has been granted that, if it needs to, it will request additional memory for processing a hash join for example, which might have to spill to disk it memory can't accomodate that additional memory. Correct?

I'm not experiencing any particular issue, I'm just trying to understand the behaviour.

Where I got thrown off a bit was here
All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time.


Thanks
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)SSC Guru (95K reputation)

Group: General Forum Members
Points: 95455 Visits: 33013
SQLSACT (1/31/2013)
Thanks

I understand the workspace memory area (Well, I hope I do). A query will request required memory and once the query has been granted that, if it needs to, it will request additional memory for processing a hash join for example, which might have to spill to disk it memory can't accomodate that additional memory. Correct?

I'm not experiencing any particular issue, I'm just trying to understand the behaviour.

Where I got thrown off a bit was here
All temporary tables (hash/work tables, temp tables, table variables) are written to disk as well as memory, every time.


Thanks



Sorry, but they are. You can watch it occur in the allocations. None of the temporary objects are memory only. They all require some disk allocation. It's just that the majority of the storage is, usually, depending on the system, etc., in memory.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search