10 Sep 2007 14:00
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 as a source we simply generate some rows.
public override void CreateNewOutputRows()
for( int i = 0;i<=100000;i++)
Output0Buffer.Column = i;
If we run the package we see evidence of the limiter. Your results may vary as we have deliberately restricted memory to this server.
Information: 0x400492E0 at Data Flow Task, DTS.Pipeline: During last execution
the pipeline suspended output "Output 0" (76) of component "Script Component"
(73) for 110 milliseconds to limit the number of in-memory buffers.