question

Raj More avatar image
Raj More asked

Can bad joins fill up TempDB

If I have bad joins (that ignore indexes) on large tables, will that affect / fill up my TempDB database?
joinsindexestempdb
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered
Heck yes. The different JOIN mechanisms within the optimizer can store information in tempdb. For example, a hash join builds a hash table which is in tempdb. A merge join might require a sort operation and the sort will take place in tempdb. That's just the first two that I thought of. There are others that can, and will, dump into tempdb. So yes, "bad" JOINs are an issue for tempdb.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Sacred Jewel avatar image
Sacred Jewel answered
Yes very much possible. If the server is unable to keep it in the memory, then it will spill it to the tempdb space.
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.