increase the tempdb size bigger before job run

  • I have a long running SSIS ETL job, it runs slow I am trying to optimize in many aspects.

    One thing is I see the tempdb grows while running the job. Is it a better approach before running the job I can make the initial size bigger?  so that while the job runs it won't take much  time to increase the size to slow the job.

    I see  most the growth takes 30-400 ms. so maybe it is not big deal.

    Thanks,

  • How to Resize tempdb — DatabaseJournal.com

  • If tempdb is growing every time you run this package - that indicates another process somewhere that is shrinking the files.  You should not be shrinking the files...reset the size to a size that meets the daily/weekly/monthly requirements and leave it alone.

    As for this process - you need to review what that package is doing and the queries used, and optimize those aspects.  If your queries are taxing tempdb then there is plenty of opportunity there to improve performance.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • > Is it a better approach before running the job I can make the initial size bigger? <<

    Yes, for sure.  For one thing, it means you can make sure that all tempdb data files remain the same size.  For the log file, it's critical for performance that you grow it yourself beforehand and not allow it to grow automatically.

    If you really are on SQL2016+, then you can shrink the tempdb files back afterward if you really insist on it.  As stated above, that's not a good idea, but it can be done.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Jeffrey Williams wrote:

    If tempdb is growing every time you run this package - that indicates another process somewhere that is shrinking the files.  You should not be shrinking the files...reset the size to a size that meets the daily/weekly/monthly requirements and leave it alone.

    As for this process - you need to review what that package is doing and the queries used, and optimize those aspects.  If your queries are taxing tempdb then there is plenty of opportunity there to improve performance.

    +1,000,000 to both of those suggestions!

    --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)

  • This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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