Abnormal growth of temp db

• Ganeshkumar005

  Ten Centuries

  Points: 1140

  More actions

  February 15, 2011 at 12:17 am

  #148465

  I can see abnormal growth of temp db during a update query:

  UPDATE tlstagingdb.dbo.tbltlxyz SET lImpStatus = 10 where limpidentity in ( select xxxxxxxxxxxxxxxxxxxxx)

  select subquery works excellent and the tblxyz hardly carries 4000 rows. If the select query is working fine, why the query hangs and causes the temp db to grow abnoramlly and to any extent (if it gets space for that :-))

  Same kind of quey in the same kind of sproc (for a diffrent job simillar in nature) has also stated causing problem now.

  Another important factor is issue occurs close to before 3am, whenver occurs. Issue is not permanent.

  Please help.

• Grant Fritchey

  SSC Guru

  Points: 398815

  More actions

  February 15, 2011 at 7:10 am

  #1287041

  Deletes are going to lead to indexes getting updated. That takes place in tempdb. Are you shrinking tempdb over & over? Don't do that. Grow it out to an appropriate size and leave it there.

  To attempt to tune the query, you'll need to post structure, code, sample data and actual execution plans from your system.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning

• Jeff Moden

  SSC Guru

  Points: 1004177

  More actions

  February 15, 2011 at 8:14 am

  #1287103

  ganeshkumar005 (2/15/2011)

  ---

  I can see abnormal growth of temp db during a update query:

  UPDATE tlstagingdb.dbo.tbltlxyz SET lImpStatus = 10 where limpidentity in ( select xxxxxxxxxxxxxxxxxxxxx)

  select subquery works excellent and the tblxyz hardly carries 4000 rows. If the select query is working fine, why the query hangs and causes the temp db to grow abnoramlly and to any extent (if it gets space for that :-))

  Same kind of quey in the same kind of sproc (for a diffrent job simillar in nature) has also stated causing problem now.

  Another important factor is issue occurs close to before 3am, whenver occurs. Issue is not permanent.

  Please help.

  Like Grant suggested, there are a lot of things that can cause this. One of those things may be an "accidental cross join" (ie: many-to-many) join in your sub-select (frequently the case for "runaway growth of TempDB). Please refer to the second link in my signature line below for what and how to post items which may help us find your problem for you.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)