August 13, 2013 at 2:07 am
Hi,
we have run into situations when the same package is VERY slow when run with VStudio 2010 but the same package runs fast when run as a JOB in SqlAgent.
Sql2012 64 bit.
The 32/64 bit settings are the same both in SSIS project and in JOB Step.
Any iudeas?
ville
August 13, 2013 at 2:15 am
Depends on what you're doing in the package.
Are you running it on the same machine?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2013 at 2:19 am
Hi,
this is not one of my packages but the dude is very experienced with SSIS - a standard data load. And yes SSIS and Sql2012, in the same Win server 2008 62.
v
August 13, 2013 at 2:23 am
When it is run in VS, where does the package spends most of its time? Source, transformation (for example a sort) or the destination?
SSDT is 32-bit, so there might be a difference when run in 64-bit SQL Agent (less memory pressure).
Also, SSDT is always a bit slower, as it needs to update the layout (row counts, color boxes, etc) during debugging.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2013 at 2:28 am
I must ask the guy where the time is spent if possible to pinpoint that.
And the difference is two hours against one minute in this case...
v
August 13, 2013 at 2:32 am
Ville Lucander (8/13/2013)
I must ask the guy where the time is spent if possible to pinpoint that.And the difference is two hours against one minute in this case...
v
Such differences are usually caused by memory pressure. SSDT can't have as much RAM as in SQL Agent (it is 32-bit), so if it doesn't have enough memory it needs to swap to disk, which can result in disastreous performance.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2013 at 2:37 am
Ok, sounds reasonable. SSDT is 32 bit... but what does the project setting 'Run64BitRuntime' do then...? It affects somehow because Excel loads does not work if that is set to True when debugging.
August 13, 2013 at 2:45 am
Ville Lucander (8/13/2013)
Ok, sounds reasonable. SSDT is 32 bit... but what does the project setting 'Run64BitRuntime' do then...? It affects somehow because Excel loads does not work if that is set to True when debugging.
Apologies, when this setting is true, the debug environment of SSIS (not visual studio itself) runs in 64-bit.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
August 13, 2013 at 2:48 am
OK, now I got the difference myself as well. Maybe I'll just ask the guy to experiment with the 64 bit setting..
v
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply