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

fragmented indexes cause TEMPDB growth? Expand / Collapse
Author
Message
Posted Wednesday, August 27, 2014 6:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 6:01 PM
Points: 1, Visits: 1
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.
Post #1608103
Posted Thursday, August 28, 2014 2:34 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 3:29 AM
Points: 43,022, Visits: 36,182
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 2008, MVP
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

Post #1608188
Posted Thursday, August 28, 2014 4:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1608211
Posted Thursday, September 4, 2014 1:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:11 AM
Points: 124, Visits: 184
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.
Post #1610388
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse