May 6, 2010 at 11:01 am
Hello,
I didn't see any forum for SSIS 2008 so had to post in SSIS 2005.
I am trying to troubleshoot performance issues of SSIS 2008 packages and need inputs. This is an ETL process built by a developer who is no longer with the company and i am responsible for maintaining and tuning it now.
Here are the steps the Package currently does:
1) Truncates staging table.
2) Uses Data flow with a Stored procedure as the OLEDBSource "EXEC dbo.Extract_From_table_Incremental @ODS_Table = 'Test_Table' , @CreateDate = '2010-05-04 21:00', @EndDate = '2010-05-05 21:00' , @LoadType= 'INCR_ODS_LOAD' "
to extract one day's worth of data. This is a common Stored Proc used by multiple ETL packages where ODS_Table parameter corresponds to the table we are loading the data for and populates the staging table.
This Data flow has a pivot transformation to convert the rows to columns and a conditional split transformation to redirect any bad records to a log table. This procedure used to run great (probably less data) until lately has been crawling thereby delaying our data load significantly.
After doing some investigation i found that the SSIS package spends lot of time at the Data flow pre-execute validation, more specifically at the SP execution. Apparently it tries to execute the SP in order to get the metadata. The SP uses a table variable and joins bunch of tables which are quite large.
To overcome the Data flow performance issue, i am first executing the proc to populate another staging table by using a Execute SQL Task right before the Data flow and subesequently using that staging table as the OLEDB Source in the Data flow.
While this approach has definitely helped the SSIS packages run faster, i want to know why the SSIS package takes hell longer to execute using the SP as OLEDB Source?
Is there a way i can use SP as the OLEDB Source yet optimize the packages? What goes behind the scene?
Will really appreciate any ideas or pointers.
Thanks in advance.
Amol Naik
May 6, 2010 at 11:06 am
How well does the SP perform outside of the package? Have you checked indexes? What does the proc look like?
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
May 6, 2010 at 11:09 am
Yes, i tuned the proc execution down to 3 minutes from 20 minutes, yet the package takes longer to execute.
Amol Naik
May 6, 2010 at 11:11 am
Have you considered turning off pre-validation?
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
May 6, 2010 at 11:30 am
Is there an option to turn off pre-validation? How to do that?
Thanks,
Amol Naik
May 6, 2010 at 11:38 am
When you are running this, are you running it as a job or directly from BIDS?
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
May 6, 2010 at 11:43 am
Tried both, thru BIDS and Job, same results.
Amol Naik
May 6, 2010 at 11:50 am
I was thinking the delayvalidation flag that can be set to true. During runtime, the runtime engine validates each task as it reaches it. During runtime, the delayvalidation won't have an effect on it.
When you place a breakpoint on the task and then after the task, how long does it take for that one SP task to execute?
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
May 6, 2010 at 11:53 am
The time to run the SP definitely is longer than it should have taken, almost double the time (~6 minutes).
Thanks,
Amol Naik
May 6, 2010 at 11:55 am
Amol Naik-681410 (5/6/2010)
The time to run the SP definitely is longer than it should have taken, almost double the time (~6 minutes).Thanks,
That seems proportionate to a double run (validation and then actual execution at run time).
Is there any way you can optimize that query further?
Could you provide the execution plan for that proc?
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
May 6, 2010 at 12:44 pm
It's doing all Clustered index seek. Tried all possible optimization. Also tried replacing the @table variable to a #temp table..didn't help.
My question is there a way to tell SSIS to not execute the SP at the pre-execute phase? Delay validation is not going to help.
Thanks,
Amol Naik
May 6, 2010 at 12:49 pm
No. Pre-execute cannot be skipped either. It is a part of the run-time. If the query is doing nothing more than a Clustered Index seek, and it takes three minutes - do you have heavily fragmented indexes or out of date statistics?
Have you checked your indexes and statistics? 3 minutes seems quite slow. How many records?
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply