May 8, 2018 at 2:39 am
Hi,
I have a stored procedure which inserts and updates millions of records in a table by referring some of the sales related tables in production. Problem here is it grows the tempdb log size drastically and application users face slowness during that time in that server. Instead of performing those manipulations through query and insert / update in table if i go with SSIS will it help? Does it still grows the tempdb similar to what the stored procedure did? Also when i do it through SSIS will it create slowness in the server for other activities? Thanks in Advance!
May 8, 2018 at 3:53 am
Will SSIS populate tempdb as it does transformations within the Dataflow? No, SSIS doesn't use the resources of the Data Engine, it uses it own. Will that effect the performance of the server? WEll, if it's on the same server, yes. The SSIS Service is still going to be using the same RAM and CPU resources. I suppose it might not have RAM contention, if you have plenty of RAM on your server (say you have 256GB of RAM, and you're running Standard Edition, which is limited to 128GB, you have 128GB of RAM for other things).
CPU Contention is going to be likely. Also I/O contention (SSIS is going to need to access your HDD/SSD/Storage Media to access the data, both for reading and writing to the database. While that's going on, user's of that database are, probably, going to notice a performance change.
The easiest way, however, to find out what works best for you is to test. This is what your Dev and UAT environments are for. 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 8, 2018 at 10:39 am
haichells - Tuesday, May 8, 2018 2:39 AMHi,I have a stored procedure which inserts and updates millions of records in a table by referring some of the sales related tables in production. Problem here is it grows the tempdb log size drastically and application users face slowness during that time in that server. Instead of performing those manipulations through query and insert / update in table if i go with SSIS will it help? Does it still grows the tempdb similar to what the stored procedure did? Also when i do it through SSIS will it create slowness in the server for other activities? Thanks in Advance!
If the source and target databases are on the same instance, a highly optimised T-SQL solution is likely to be faster than anything you can do in SSIS.
Getting to that highly optimised state requires knowledge in depth of how to handle huge transaction volumes. Jeff Moden is the name that comes to mind to help you with this; let's hope that he reads your post.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 8, 2018 at 12:53 pm
haichells - Tuesday, May 8, 2018 2:39 AMHi,I have a stored procedure which inserts and updates millions of records in a table by referring some of the sales related tables in production. Problem here is it grows the tempdb log size drastically and application users face slowness during that time in that server. Instead of performing those manipulations through query and insert / update in table if i go with SSIS will it help? Does it still grows the tempdb similar to what the stored procedure did? Also when i do it through SSIS will it create slowness in the server for other activities? Thanks in Advance!
SSIS won't automatically use less tempdb space or transaction log space. It all depends on how you have structured the process. It sounds like you are using a linked server to get the data - and probably trying to filter from the source system over the linked server. If so - then the usage of tempdb is related to the workfiles that SQL Server creates to pull the data across the linked server and join to the local tables.
If that is how you are doing things - then moving to SSIS and pulling the data directly with filters will reduce tempdb usage. Also - on the OLEDB Destination in SSIS you can set the batch/commit sizes to reduce the impact on the transaction log.
There are methods that can be used to reduce the tempdb utilization across a linked server - generally it requires not joining across the linked server and pulling the remote data local before joining.
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply