January 1, 2013 at 9:39 pm
I believe hash joins can cause growth in the tempdb if there is insufficient memory. I don't think nested loops do.
January 2, 2013 at 12:06 am
If you go with the basic structure of HASH join then you will find that temp table get created (either in memory or tempdb) which might the cause of tempdb's growth. so now why i mentioned here "either in memory or tempdb" because if data doesnt get fit in memory then it will be moved to tempdb. same happens in case of temp table and table variable storage too(its a myth that table varable get created in memory and temp table in tempdb).
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 2, 2013 at 10:16 am
Golfer22 (1/1/2013)
I believe hash joins can cause growth in the tempdb if there is insufficient memory. I don't think nested loops do.
No, I do not think nested loops would affect tempdb.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 2, 2013 at 10:17 am
Bhuvnesh (1/2/2013)
If you go with the basic structure of HASH join then you will find that temp table get created (either in memory or tempdb) which might the cause of tempdb's growth. so now why i mentioned here "either in memory or tempdb" because if data doesnt get fit in memory then it will be moved to tempdb. same happens in case of temp table and table variable storage too(its a myth that table varable get created in memory and temp table in tempdb).
I think you meant internal work table, aka the hash table. Temp table implies we could address it using #tableName. Internal work tables are created by the database engine to solve a particular query and are not directly addressable by us.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply