March 18, 2014 at 2:33 pm
I'm not sure if I'm using the correct terminology for what I'm trying to accomplish (could explain the lack of relevant search results). We have a SQL Server that we share with another department. Each month we have to run around 100 stored procedures that compare our repository tables to our staging tables. These stored procs flag any deletes/updates and load any inserts/updates. Our ideal situation would be to have all these stored procs run as quickly as possible but without limiting server availability completely. They range in the amount of time they take from seconds to a couple of days.
Is there some way to have ssis take care of the server utilization? I've seen a couple different web pages where they describe how to run multiple stored procedures in sequence but I've yet to find any discussion along the lines of what we are trying to accomplish.
_________________________________________________________________________________________________________________
'Everything will be okay in the end. If it's not okay, it's not the end.' - John Lennon
March 18, 2014 at 3:56 pm
No I don't think you can tell SSIS how to do this - but if I understand you correctly (you want to throttle the SSIS to do its thing, without bringing the server to its knees) If you're running Enterprise Edition you could take advantage of the Resource Governor and make an entry into your classifier function for the account that's executing the package. This probably won't be perfect, but should allow the process(es) to run optimally while the server isn't stressed, and throttle it down when resources become an issue.
I'm no expert in SSIS...but it might help minimize your monthly process.
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 19, 2014 at 3:38 am
I think this might help
March 19, 2014 at 3:10 pm
MyDoggieJessie (3/18/2014)
No I don't think you can tell SSIS how to do this - but if I understand you correctly (you want to throttle the SSIS to do its thing, without bringing the server to its knees) If you're running Enterprise Edition you could take advantage of the Resource Governor and make an entry into your classifier function for the account that's executing the package. This probably won't be perfect, but should allow the process(es) to run optimally while the server isn't stressed, and throttle it down when resources become an issue.I'm no expert in SSIS...but it might help minimize your monthly process.
This is the correct way.
Also you can limit SQL Server resource utilization by tweaking the Max Memory & CPU settings. But that's not SSIS package specific.
--
SQLBuddy
March 19, 2014 at 6:21 pm
Dumai (3/18/2014)
They range in the amount of time they take from seconds to a couple of days.
Gosh... a "couple of days"??? :blink: There's something [font="Arial Black"]really [/font]wrong there. If they were to fix that problem, there'd be no need for load balancing.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 19, 2014 at 8:43 pm
That was my thought Jeff 😉 But without knowing "why" or "how" it got that way...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 20, 2014 at 5:29 am
sqlbuddy123 (3/19/2014)
MyDoggieJessie (3/18/2014)
No I don't think you can tell SSIS how to do this - but if I understand you correctly (you want to throttle the SSIS to do its thing, without bringing the server to its knees) If you're running Enterprise Edition you could take advantage of the Resource Governor and make an entry into your classifier function for the account that's executing the package. This probably won't be perfect, but should allow the process(es) to run optimally while the server isn't stressed, and throttle it down when resources become an issue.I'm no expert in SSIS...but it might help minimize your monthly process.
This is the correct way.
Also you can limit SQL Server resource utilization by tweaking the Max Memory & CPU settings. But that's not SSIS package specific.
--
SQLBuddy
Heh... Just as a bit of a sidebar, I always think it odd that people try to limit resources on long running, resource hungry procs or scripts or processes instead of fixing the root problem which, of course, is the code itself... especially the ones that take "days" to run. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2014 at 7:19 am
Thanks for the replies guys. I will talk with our DBA (non-dev) about the Resource Governor, etc.
As far as the run times, I doubt there is anything wrong. the stored procs are running against vastly different sized tables. 1000s vs near billions of rows. But I am a junior developer just getting started in this work, taking my cues from our data architect so maybe I will find out different over time. Thanks again.
_________________________________________________________________________________________________________________
'Everything will be okay in the end. If it's not okay, it's not the end.' - John Lennon
March 20, 2014 at 7:59 am
Jeff Moden (3/20/2014)
sqlbuddy123 (3/19/2014)
MyDoggieJessie (3/18/2014)
No I don't think you can tell SSIS how to do this - but if I understand you correctly (you want to throttle the SSIS to do its thing, without bringing the server to its knees) If you're running Enterprise Edition you could take advantage of the Resource Governor and make an entry into your classifier function for the account that's executing the package. This probably won't be perfect, but should allow the process(es) to run optimally while the server isn't stressed, and throttle it down when resources become an issue.I'm no expert in SSIS...but it might help minimize your monthly process.
This is the correct way.
Also you can limit SQL Server resource utilization by tweaking the Max Memory & CPU settings. But that's not SSIS package specific.
--
SQLBuddy
Heh... Just as a bit of a sidebar, I always think it odd that people try to limit resources on long running, resource hungry procs or scripts or processes instead of fixing the root problem which, of course, is the code itself... especially the ones that take "days" to run. 😀
Very true, Jeff. Ideally, that should be the approach.
But sometimes people try to do a short -term fix as the situation demands and later do a RCA to prevent the issue from re-happening.
--
SQLBuddy
March 20, 2014 at 10:51 am
sqlbuddy123 (3/20/2014)
Jeff Moden (3/20/2014)
sqlbuddy123 (3/19/2014)
MyDoggieJessie (3/18/2014)
No I don't think you can tell SSIS how to do this - but if I understand you correctly (you want to throttle the SSIS to do its thing, without bringing the server to its knees) If you're running Enterprise Edition you could take advantage of the Resource Governor and make an entry into your classifier function for the account that's executing the package. This probably won't be perfect, but should allow the process(es) to run optimally while the server isn't stressed, and throttle it down when resources become an issue.I'm no expert in SSIS...but it might help minimize your monthly process.
This is the correct way.
Also you can limit SQL Server resource utilization by tweaking the Max Memory & CPU settings. But that's not SSIS package specific.
--
SQLBuddy
Heh... Just as a bit of a sidebar, I always think it odd that people try to limit resources on long running, resource hungry procs or scripts or processes instead of fixing the root problem which, of course, is the code itself... especially the ones that take "days" to run. 😀
Very true, Jeff. Ideally, that should be the approach.
But sometimes people try to do a short -term fix as the situation demands and later do a RCA to prevent the issue from re-happening.
--
SQLBuddy
Understood. That normally takes 4 times the work, though. Develop the load balancing, find out it doesn't work the way you expected, try to fix the queries, develop the queries for performance, install and test the new queries, undo the load balancing because that now takes more time than the queries do. 😉
Folks almost never get bank to doing an RCA because of the willingness to accept poor performance just because something has a billion row table associated with it. :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
March 20, 2014 at 11:03 am
Understood. That normally takes 4 times the work, though. Develop the load balancing, find out it doesn't work the way you expected, try to fix the queries, develop the queries for performance, install and test the new queries, undo the load balancing because that now takes more time than the queries do. 😉
Folks almost never get bank to doing an RCA because of the willingness to accept poor performance just because something has a billion row table associated with it. :hehe:
I'm not sure if I'm using the correct terminology for what I'm trying to accomplish
True. And, the post shouldn't have been named "SSIS Load Balancing ". He's actually trying to prevent SQL Server from eating up all the resources on the server. 🙂
--
SQLBuddy
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply