January 19, 2012 at 10:00 am
I have an SSIS package that joins 8 tables to create an aggregate table.
On the package, task 1 truncates the table, disables indexes. Task 2 is a dataflow task that consists of a straight forward query which joins the 8 tables, and insert the results into the aggregate table (around 12 millions records).
When I run the same query on SQL management studio, it takes me around 20 minutes, but on SSIS, it takes 80 minutes.
Is there a setting/configuration on SSIS that I could change to make it runs faster?
Thank you.
January 19, 2012 at 10:12 am
Is the package being run from the same server or from a separate server dedicated to SSIS?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 19, 2012 at 10:28 am
It's on the same server. The source tables are also on the same database as the destination table. Thanks.
January 19, 2012 at 10:50 am
Which version of the SS are you running?
How many processors does your machine have?
Is the query in SSIS in plain T-SQL or are you calling a sproc?
January 19, 2012 at 11:27 am
The query is a plain t-sql.
SQL Server 2005
Windows Server 2003 R2 64bit
Intel Xeon 4 processor - @2.33 ghz
31.9 GB RAM
January 19, 2012 at 11:55 am
Could you please run PerfMon trace during the SSMS execution and during the SSIS run, and log the following values:
- % Processor Time
- Avg. Disk Queue Length
- Pages/sec
You may write a log (Data Collector Set), or for the first approximation make a screenshot of the Performance Monitor pane.
Also, please post the query.
January 19, 2012 at 4:58 pm
sdarmohusodo (1/19/2012)
The query is a plain t-sql.SQL Server 2005
Windows Server 2003 R2 64bit
Intel Xeon 4 processor - @2.33 ghz
31.9 GB RAM
How much memory is allocated to sql server and what is leftover for the operating system?
SSIS will compete for memory with SQL server - and I think you may be experiencing that.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 19, 2012 at 5:34 pm
Let him get the numbers.
January 20, 2012 at 2:44 am
Basically, when an SSIS package executes, it has to create buffers to hold the data for futher processiong, which needs a lot of memory. SSIS run time has to hold the data to an extent of the buffer memory size set in the package. Once it is full, it has process and flow down and pull back from the source to refill.
Ensure that the whole package is using only the required columns. Especially query should have only the required columns and remove all the unwanted columns, so that the buffer will allow more data to hold.
January 20, 2012 at 9:11 am
Yes, I know these things, but without checking at least the three basic counters we can only guess whether his SSIS is memory-, CPU- or I/O-bound.
January 23, 2012 at 6:17 am
Try adding the package to an agent job, and running the agent job.
Executing it using dtloggedexec
http://dtloggedexec.codeplex.com/
might shed some light on things..
_____________________________________________________________________
Disclaimer - The opinions expressed by the mouth are not necessarily those of the brain
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply