I'm reading some data from a SQL Server 2012. The source table has some 600 million rows, and two of the headache columns are nvarchar(max). Problem: The performance drops from 100k rows per second to 800 per second when including these blob columns. Is there some way in make things go faster with blobs in SSIS?
Max datalength for the varchar(max) is 250 bytes and avg datalength is 50 bytes. The content is XML stored as text. I could possibly cast them to a nvarchar(2000), but I need later to work with a table with bigger xml-text objects.
The destination is simply a RowCount.
What I see is
- SSIS works on the data in chunks. While SSIS is working, the source server is idle: No CPU, IO or Pagelookups. I don't know what SSIS is doing internally.
- I have 40 GB of RAM on the server, SSD disks, should be enough resources.
- I've tried to set the Blob and Buffer Storage Path to a explicit folder, but I can't see anything going on there.
- The working set of the SSIS package fine, nothing going on there. It consumes only some 300-400 MB, that's it.
- No paging going on.
- I'm using the SQLNCLI11.1 provider, 32K packet size.
- Networking is fine, I've ruled out that part. The waitstats on the source server says vaguly it's waiting for network, but that's simple because the client app is not consuming anything. Again, if I leave out the blob column the network delivers 400 mbpbs constantly (1 gb network ).
It seems to me as SSIS simply doesn't handle blobs very well. It's very very conservative (because it cannot know how much space the data will occupy from the buffer). From the doc I can read the SSIS handles blobs differently from ordinairy columns, that they may be stored on disk, read back to memory again and then sent down the pipeline. Whatever happens, the performance is simply a disaster.
I've played around with the buffer size and maxbufferrows, it doesn't really make that much difference. The average performance is crap whatever I choose to do.
I've googled "everything", this is my last resort. What have I missed so far?
