fragmented indexes cause TEMPDB growth?

Question

fragmented indexes cause TEMPDB growth?

sqlenforcer

SSC Enthusiast

Points: 175
More actions
August 27, 2014 at 6:06 pm

#295769

If there is fragmentation in some of the indexes being used in a multi-table join query, would that cause TEMPDB to grow?
There are large amounts of 'internal_object_reserved_page_count', corresponding to less 'unallocated_extent_page_count' numbers as the query runs then eventually fills up the drive that TEMPDB is on, over 146GB of space.

Viewing 4 posts - 1 through 3 (of 3 total)

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

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

sqlenforcer (8/27/2014)
If there is fragmentation in some of the indexes being used in a multi-table join query, would that cause TEMPDB to grow?

No. Fragmentation has to do with the arrangement of pages on disk. It has no relevance once pages are in memory, which they will be when the query execution engine is using them. By the time the rows get to the point of being added to work tables, their physical storage details are irrelevant.

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

sqlenforcer (8/27/2014)
If there is fragmentation in some of the indexes being used in a multi-table join query, would that cause TEMPDB to grow?

You're experiencing heavy tempdb usage during execution of a query. Rewriting the query may reduce or even eliminate tempdb usage: the execution plan (actual, not estimated) for the query is the first place to look for potential improvements. If you can post an actual execution plan here - as a .sqlplan file attachment - folks will provide you with suggestions for improvement.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

srikantmeister SSChasing Mays Points: 657 More actions · Answer 3

Tune the query and check for missing indexes. fragmentation nothing to take with tempdb grow unexpectedly.

also check for other process setup you have in environment.