Post reply

TempDB Inserts and Updates

  • February 5, 2013 at 4:08 am

    #273790

    Hi

    Do Inserts and updates affect the TempDB?

    I am not using temporary tables just everyday update and insert statements. There is a lot of data tho

    could these be the cause of the growth of the tempdb?

    Thanks

  • February 5, 2013 at 4:22 am

    #1584283

    Not unless you're using one of the snapshot isolation levels or have complex select/from portions to those insert/updates that does hash joins or needs work tables or have triggers on the tables being inserted/updated.

    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

  • February 5, 2013 at 4:34 am

    #1584290

    nope none of that

    what about joins and sorts on the queries? would that affect the tempDB?

  • February 5, 2013 at 4:41 am

    #1584292

    Sorts will affect TempDB as thats the holiding area while it sorts the records.

  • February 5, 2013 at 4:45 am

    #1584294

    There are a fe windowing functions

    if these queries were ran in an ssis package would the tempdb be used in the same way?

  • February 5, 2013 at 4:53 am

    #1584298

    erics44 (2/5/2013)

    nope none of that

    what about joins and sorts on the queries? would that affect the tempDB?

    I mentioned those:

    or have complex select/from portions to those insert/updates that does hash joins or needs work tables

    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

  • February 5, 2013 at 5:00 am

    #1584307

    Hi Eric,

    Sorting/Joining would cause temdb grow. Even if you run using SSIS package, it will internally sort the data from cache memory set to SSIS.

    Hope this helps!!

    Thanks,

    Sridhar.

  • February 5, 2013 at 5:05 am

    #1584309

    GilaMonster (2/5/2013)

    erics44 (2/5/2013)

    nope none of that

    what about joins and sorts on the queries? would that affect the tempDB?

    I mentioned those:

    or have complex select/from portions to those insert/updates that does hash joins or needs work tables

    appologies i didnt know thats what you meant

  • February 5, 2013 at 5:09 am

    #1584312

    smamidala (2/5/2013)

    Hi Eric,

    Sorting/Joining would cause temdb grow. Even if you run using SSIS package, it will internally sort the data from cache memory set to SSIS.

    Hope this helps!!

    Thanks,

    Sridhar.

    hmmm, perhaps i am looking in the wrong place for the reason the tempdb is growing then

    the updating and inserting was originally in an ssis package, this package had issues with connections and linked servers so i converted it to script yesterday, today the tempdb is growing

    i assumed it was because the ssis package used the tempdb differently

    the sql is identical in both, originally it was better in a ssis package because data was coming from lots of different places, it no longer does so the ssis package isnt neccessary it was just still in there because it was working and there was more higher priority work

  • February 5, 2013 at 5:40 am

    #1584325

    erics44 (2/5/2013)

    i assumed it was because the ssis package used the tempdb differently

    the sql is identical in both, originally it was better in a ssis package because data was coming from lots of different places, it no longer does so the ssis package isnt neccessary it was just still in there because it was working and there was more higher priority work

    As far as SQL is concerned, SSIS is just a client like any other. It's queries are processed just like any other.

    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

Viewing 10 posts - 1 through 9 (of 9 total)

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