May 15, 2009 at 3:55 pm
Can anyone tell me why an SSIS package would run slower on the server than it does in BIDS on my laptop?
I'm guessing it has something to do with validation of the package at run time.
any thoughts would be appreciated.
May 15, 2009 at 7:02 pm
tvanharp (5/15/2009)
Can anyone tell me why an SSIS package would run slower on the server than it does in BIDS on my laptop?I'm guessing it has something to do with validation of the package at run time.
any thoughts would be appreciated.
Are your CMs all connecting to the same places and servers? Or do you have a different configurations setup for Your server?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 16, 2009 at 5:34 am
Not exactly sure what your asking but here's what I have for my CM's.
Using SQL configs to set the connection string property. Config database is located on the server running SSIS. There are 3 CM's that point to the server running SSIS for logging, administration, and for calling shared procedures. 5 CM's that point to another server for doing all the heavy lifting, and 1 to a third server for doing lookups on a database located there. My CM used for accessing the sql configs is set by an xml config located on the network.
This is the same setup used when running in BIDS, just pointed to a different set of (test) servers.
Note the production servers are not being pounded by hundreds of users, traffic is limited and at night it is almost non-existent but run times stay pretty much the same day and night.
May 16, 2009 at 8:16 am
Right, the problem is that in reference to your original question "why an SSIS package would run slower on the server than it does in BIDS on my laptop?" there's just too many possibile answers to a generic question like that to even scratch the surface.
It could be something general and systemic like differences in the data throughput or available net bandwidth of one of your production connections compared to your test environment connections.
Or it could be something more specific like that certain connection types (say ADO.Net vs. OleDB) work better in some environments & configurations than others.
Or it could be something pathological, like say a bug in Logging that only manifests in certain rare circumstances. There are just too many possibilities to list.
Consequently, we need more information about the specifcs. Not knowng anything more, then generically I would suggest comparing your logging timestamps from your test environment to your production one and seeing where the big differences occur. This at least should help you to narrow it down to a particular connection, task or facility.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 17, 2009 at 11:09 am
Sorry about the generic question, I am always hoping for that big thing that I missed that's very obvious. I will take you suggestion with the logging and compare prod to test to see if I can isolate the problem. I'ts very discouraging to deploy and find your ETL runs like an old 3 legged dog.
May 17, 2009 at 12:10 pm
I hear that. 🙂
Let us know what you find out.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 18, 2009 at 4:39 pm
I know there are many possibilities but one thing I would consider is if the SSIS package runs while many client apps are connected to the server. Thus there would be competition for network bandwidth and server resources. If the overall server is slow for all connections a reboot (in off hours) may help. Or use Activity Monitor to look for those processes gone astray , usually indicated with a large wait time.
----------------------------------------------------
May 20, 2009 at 9:09 pm
tvanharp (5/17/2009)
Sorry about the generic question, I am always hoping for that big thing that I missed that's very obvious. I will take you suggestion with the logging and compare prod to test to see if I can isolate the problem. I'ts very discouraging to deploy and find your ETL runs like an old 3 legged dog.
In a remarkable coincidence, I had the same thing happen to me this week. In my case it turned out that the way to fix it was to change the Access Mode to "Fast Load". Of course, that's not to say that your situation is the same...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 21, 2009 at 4:58 am
I run fast load on all my inserts. Unfortunately we've been trying to get the latest release deployed and I have not had any time to investigate. We are in the midst of a huge project being executed using Agile methodology, a refactoring nightmare to be more exact. This has been quite an experience!
I will update when I have new information.
May 21, 2009 at 5:02 am
I just re-read your post and you say "Access mode to Fast Load" I assumed you were referring to an OLE Destination, is that correct or is Access mode something else?
May 21, 2009 at 5:04 am
tvanharp (5/21/2009)
I just re-read your post and you say "Access mode to Fast Load" I assumed you were referring to an OLE Destination, is that correct or is Access mode something else?
Sorry, yes, it is a setting on the OLE Destination component.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply