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,
February 24, 2021 at 6:59 pm
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
February 24, 2021 at 9:08 pm
> 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.
February 25, 2021 at 8:15 pm
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
Change is inevitable... Change for the better is not.
February 27, 2021 at 1:44 pm
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