How to optimise doing the same lookup multiple times. [More]

Not many people understand that the Integration Services pipeline can be intentionally throttled back under the covers to stop you from running out of memory.  In this video we show you how to detect this is happening and explain more about the limiter itself. More...

I am going to be delivering a one day tuning seminar for SSIS (SQL Server 2005).  I am doing it in conjunction with the UK User Group.  I will be covering as much as I am able on how to look at your package with performance in mind.  I will show you how to get more from the data flow engine and also how to avoid common performance slow downs.  The location we have chosen is beautiful and it should be really good day. if you are interested then shoot over to here Perf Course

Download the slides and sample code from the Advanced Topics Using Microsoft SQL Server 2005 Integration Services presentation, first presented at the SQLBits 2007 Community Conference. In this session we looked at some of the not so often thought about areas of the SSIS toolbox. Perhaps they aren't considered true ETL by some people or are simply not seen but we think they are really useful weapons in our ETL armoury. Highlights include: Loading partitions directly Consuming a package in SSRS Using a DM algorithm in the pipeline to see... [More]

Michael Entin has blogged about something that you see now, well you feel the effects anyway, in SQL Server 2005 but it isn't as visible as it should be but is really important. Backpressure is an interesting internal memory limiter inside SSIS and in SQL Server 2008 you get to see when the limiter kicks in. For a detailed explanation see Michael''s blog entry here but we wanted to show you how to reproduce it very simply. Here is a package we are going to use to show the limiter kick in and how SQL Server 2008 shows us it taking effect: Inside the Script Component a... [More]

So we got to playing with some of the ways that you can execute an SSIS package and got kind of curious about any overhead associated with the execution methods. From these thoughts came a few tests and here are our findings. We decided to look at performance over two different pipelines. We also had to decide on a "Control" so that we had something against which to measure. In the tests the "Control" was achieved by executing each pipeline in the Business Intelligence Development Studio with debugging (F5). Pipeline 1 This pipeline is a straight Source ... [More]

I was about to write about data flow buffer size configuration properties but need to get this one out first so that we can refer to it. The Data Flow task (internally and in the object model also called the 'Pipeline') logs some pretty interesting information that describe the internal scheduler. To see these messages, one way is: 1. In a package you want to get detailed pipeline logs on, select the 'Logging...' menu option on the control flow. 2. Check the Data Flow task of interest on the tree on the left. Switch over to Details and se... [More]

Adjusting the following properties can have an impact on parallelization during execution: 1. MaxConcurrentExecutables This is a property on the Package. It defines how many tasks can be run concurrently. A value of -1 means the number of processors and when hyperthreading is turned on, it counts the number of logical processors, not physically present ones. In the little testing that I (Ash) have done, I've not noticed significant difference (<5 percent) in performance between turning on hyperthreading or leaving it off. It'd be interested to know if you see better resu... [More]