Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Could nested loops cause the growth in tempdb? Expand / Collapse
Author
Message
Posted Tuesday, January 1, 2013 9:39 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, December 28, 2013 5:45 PM
Points: 94, Visits: 96
I believe hash joins can cause growth in the tempdb if there is insufficient memory. I don't think nested loops do.
Post #1401647
Posted Wednesday, January 2, 2013 12:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:11 AM
Points: 2,840, Visits: 3,976
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
Post #1401674
Posted Wednesday, January 2, 2013 10:16 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 7,135, Visits: 12,749
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
Post #1401949
Posted Wednesday, January 2, 2013 10:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 7,135, Visits: 12,749
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
Post #1401950
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse