Getting better performance when lowering DefaultBufferMaxRows value

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » Getting better performance when lowering...

Getting better performance when lowering DefaultBufferMaxRows value - is it normal?

Rate Topic

Display Mode

Topic Options

Author

Message

Yvan Bouchard

Posted Tuesday, January 08, 2013 12:44 PM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:21 AM
Points: 872, Visits: 242

Hi,

We have server "A" running Windows Server 2003 on 64Bit OS with 128G of memory and we are setting a new server "B" running Windows Server 2008 on 64bits OS with 128G of memory.

Our SSIS package runs in about 1min30s on server "A" but unchanged, it runs in about 4min on server "B". We`ve already indentified that the performance impact is in the "reading" portion so we've set-up a simple test package which reads approx. 700,000 rows from a Sybase server.

Research on the net pointed me to playing around with DefaultBufferMaxRows in order to improve performance. Best practices dictates to try to get as many rows in a buffer, if you have enough memory (which we have). Both packages were ran originally with default values of DefaultBufferMaxRows=10000 and DefaultBufferMaxSize =10Mb.

After testing, the smaller the value in DefaultBufferMaxRows, the faster it runs. I can get it down to within 15secs.

Our client is cold to setting the value, as he is questionning the fact that we are going against best practices.

Any one would know why we have this behavior and would be able to explain it?

THanks!

Post #1404429

opc.three

Posted Tuesday, January 08, 2013 5:25 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737, Visits: 11,791

Relevant?

SSIS: A performance tuning success story

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1404504

Koen Verbeeck

Posted Wednesday, January 09, 2013 12:10 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:11 AM
Points: 9,378, Visits: 6,473

My guess is that the source is quite slow, hence it can take some time for a buffer to fill. When you make the buffer smaller, it is passed on quicker to the rest of the data flow. In other words, the rest of the data flow can start working on the data earlier then when you use a larger buffer.

I had a similar experience once, where reading a table with a large BLOB column took some considerable time, but lowering the buffer size speeded up my package with about 25%. I'll blog about it some time Smile

Rob Farley has a somewhat similar story: The SSIS tuning tip that everyone misses. He doesn't speak about making your buffer smaller, but filling up your buffers as soon as you can, which is the same in this case.

How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence

Post #1404567

Yvan Bouchard

Posted Wednesday, January 09, 2013 4:54 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:21 AM
Points: 872, Visits: 242

opc.three (1/8/2013)

Relevant?

SSIS: A performance tuning success story

Saw that article - talks about the same kind of problem and solution we used but still does not explain why making the buffer smaller makes it better. Smile

Post #1404685

Yvan Bouchard

Posted Wednesday, January 09, 2013 5:02 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:21 AM
Points: 872, Visits: 242

Thanks Koen, that makes a lot of sense -

After posting this yesterday, we were able to revert to a similar Sybase driver version 12.5 (someone was sure that we had to use the V15.5) and performance is now similar to the actual server. So there`s got to be a flaw with the driver with large chunks of data...

I`ll guess we`ll stick with this version for a while.

And thanks for the link to Rob Farley`s great article. Smile

Post #1404692

opc.three

Posted Wednesday, January 09, 2013 8:14 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737, Visits: 11,791

Yvan Bouchard (1/9/2013)

opc.three (1/8/2013)

Relevant?

SSIS: A performance tuning success story

Saw that article - talks about the same kind of problem and solution we used but still does not explain why making the buffer smaller makes it better. Smile

I should have just come out with it Tongue

I had typed up a big response espousing theories of Sybase driver performance degrading over time when streaming large batches and such, because I have dealt with it, but refrained because it had been a loooong time (Sybase 12.x with .NET 2.0), I was using the ADO.NET driver not the OLE DB driver at the time and it did not involve SSIS. The Sybase driver I was using was the first ever (I think) native ADO.NET driver they produced to connect by Sybase, and it was dodgy at best. That said, the article I linked to was leading in that direction so at worst I thought it might offer you a talking point when presenting the issue to others in your shop. I agree with Koen's line of thought and am also thankful for that article from Rob Farley (thanks Koen!).

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1404818

« Prev Topic | Next Topic »

Permissions