May 30, 2017 at 2:32 pm
I haven't really worked much on SSIS, i am running into some serious tempdb issues. I know quite a bit how sql server engine works internally don't know much internals about SSIS engine. The server i am working has lot of resources, there is no I/O issue, my biggest concern is tempdb is filling up very quickly, i have looked around online but couldn't find an exact answer. Basically i have two oledb tasks, one does select top 100000000 with two simple joins and the oledb does bulk insert. Data is loading fine but tempdb is filling up pretty quickly, i have played with different options but nothing seems to help so far. I would like to know for other experts here how they have addressed this. Below are my current options.
Row Per Batch: 50000000
Maximum insert commit size : 2147483647
Any help is appreciated.
May 31, 2017 at 6:35 am
What happens when you take the TOP 10000000 query from your OLE_DB source and just run it in SSMS? Is tempdb fine in that scenario? What if you insert same 50 million rows in SSMS? Is tempdb okay again? I'm trying to see if you can determine if it's the SELECT or the INSERTs that are causing tempdb growth. Have you played around with different batch sizes?
Just thinking out loud,
Rob
May 31, 2017 at 9:12 am
robert.gerald.taylor - Wednesday, May 31, 2017 6:35 AMWhat happens when you take the TOP 10000000 query from your OLE_DB source and just run it in SSMS? Is tempdb fine in that scenario? What if you insert same 50 million rows in SSMS? Is tempdb okay again? I'm trying to see if you can determine if it's the SELECT or the INSERTs that are causing tempdb growth. Have you played around with different batch sizes?Just thinking out loud,
Rob
You can shrink Temp DB for every Batch size run lets say 10,000,00
Can Try--
DBCC SHRINKFILE (TEMPDEV, 20480);
OR
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = target_size_in_MB) --Desired target size for the data file ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = target_size_in_MB) --Desired target size for the log file
May 31, 2017 at 9:24 am
subratnayak09 - Wednesday, May 31, 2017 9:12 AMYou can shrink Temp DB for every Batch size run lets say 10,000,00Can Try--
OR
ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = target_size_in_MB) --Desired target size for the data file ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = target_size_in_MB) --Desired target size for the log file
No, that's poor advice. There's no point shrinking tempdb just to have it grow again. We need to find out what's causing it to grow.
Can you capture the query that SSIS is running, then run it manually and post the actual execution plan?
John
May 31, 2017 at 9:40 am
There's little sense in shrinking tempdb over and over again. This may be a viable option of this was a one-time data load.
The question I have is what is the size of the disk where tempdb is located?
What are the file growths set to?
How big, in bytes, are the top 100000000 rows?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
May 31, 2017 at 1:42 pm
I think the issue is around Distinct that i have in my query. What i am not sure if i am looping through batches of 20M, i realize it uses about 250GB of tempdb but for next batch why is not able to reuse the tempdb space?
These are big tables, any other alternative to tempdb is also appreciated. Thanks
June 1, 2017 at 2:38 am
I think your query could do with some serious tuning if it's chewing up 250GB of tempdb for 20 million rows. Does the whole thing run in an explicit transaction, or does each batch commit as soon as it's finished? Please post your code, and we'll see whether we can help.
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply