How to get started with performance tuning of SSIS packages

 

Well if you are a DBA and one fine day you get notified that SSIS packages have started performing slowly. Here is how to get started with finding the bottleneck and pointing the possible cause of sudden performance problem.

  SSIS native logging:

 First of all you need to learn how to enable SSIS native logging. This involves some changes to current package. So take the backup of your package if you are dealing with slowness in production environment. This way you could also revert the logging once you are done debugging the performance problem.

 Next step is enabling the logging for SSIS package. Here are couple of articles to help`` you:

 https://msdn.microsoft.com/en-us/library/ms140246.aspx

 https://msdn.microsoft.com/en-us/library/ms138020(v=sql.105).aspx

  While enabling the logging make sure you definitely select the events such as ``OnPreExecute,OnPreValidate,OnPostExecute``,PipelineComponentTime`` . These event will help later in identifying the slowest performing task within the package. If you dump the SSIS logs to table using SQL Server log provider such as: ``sysssislog``, then this would help in understanding the events logged in a chronological order in the table.

 SELECT id, event, starttime, source, message FROM sysssislog ORDER BY id desc

 For example for one of the sample execution of package here is how it would help if you SORT in above fashion.

 

 In above example we saw that for sample task in Control Flow: “Transform and Validate” is completing in approx. 2 minutes 51 seconds by just looking at the time difference between: ``OnPreExecute and OnPostExecute``. This is how we find the duration for each task within the package which may have many tasks executing. Our focus should be basically on tuning those tasks whose duration is the highest amongst all the tasks of the package concerned.

 Once you have identified the troublesome tasks, then you should deep dive into why exactly they are taking that much time and are performing slow. For e.g. one reason it could be slow is if the task in itself is calling the stored procedure which is performing slowly.

 I would not go into example for finding slowness in Data flow tasks but it would be on the same lines as in sample example above. Probably for Data Flow tasks you need to additionally ensure that PipelineComponentTime`` is as well enabled in native logging.

 Thx

Ashutosh Sharma

SQL Consultant

 

Disclaimer

The views expressed on this website/blog are mine alone and do not reflect the views of my company. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.