January 8, 2013 at 12:44 pm
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!
January 8, 2013 at 5:25 pm
Relevant?
SSIS: A performance tuning success story
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 9, 2013 at 12:10 am
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 🙂
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.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
January 9, 2013 at 4:54 am
opc.three (1/8/2013)
Relevant?
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. 🙂
January 9, 2013 at 5:02 am
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. 🙂
January 9, 2013 at 8:14 am
Yvan Bouchard (1/9/2013)
opc.three (1/8/2013)
Relevant?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. 🙂
I should have just come out with it 😛 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply