SSIS reading data from a view.

Question

SSIS reading data from a view.

avinash.818

Mr or Mrs. 500

Points: 520
More actions
December 4, 2017 at 6:24 am

#326728

I have recently encountered this problem wherein when I am trying to load data from a view(view has join on 3 different tables) to a table, I see that the reading from source pauses for a minute and continues the load and after loading x number of records it again pauses for a while and then continues.
this are my settings of
Default BufferSize:104857600(100MB)
DefaultBufferMaxRows: 2700(my row size 39417 bytes based on dataTypes)
right now I am not loading any data to destination to check why reading goes into a pause state, I am using a rowcount transformation as destination
in my case data reading happens continuously for 1024580 records pauses for approx 1min and then continues till 2067449 and then pauses for a 1min and then continues till 3110368 and this happens till the load is complete
number of rows in input is approx 300Million.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

aaron.reese SSChampion Points: 13498 More actions · Answer 1

There are many things that can cause SSIS to pause. Buffer size and row size are one as you have correctly identified. Some tasks can be considered blocking (require all data) or semi-blocking, especially if you have aggregates or sorts in the workflow. The other thing would be to look at the total number of buffers in use at once as this is limited by the amount of memory available to the process. Has it started to write output when the first pause occurs? If not then it could indicate overall memory pressure on the package. you may be able to fix this by releasing memory in the resource governor or you might have to change your buffer size. Remember that tuning is there to achieve overall performance, not just one element of the flow or package so consider changes in their holistic environment.

avinash.818 Mr or Mrs. 500 Points: 520 More actions · Answer 2

Thanks Aaron for your reply, there are no transformations involved, one oledb source which has Select <columnlist> from <viewname> and oledb destination , to test if it is a read or write problem I have removed the destination and used rowcount transformation as destination but still reading pauses at regular intervals. I have used performance counters to monitor things, I dont see any Bufferspooling happening but whenever data read pauses I observed that BuffersUsed(Counter) stays at a constant value and it increases/decreases when data read starts again, but I am unable to understand why that happens.
do you suggest any other values of Default BufferSize/DefaultBufferMaxRows based on my i/p row size.

aaron.reese SSChampion Points: 13498 More actions · Answer 3

what is the original data source. Some ODBC drivers (e.g. Progress) do not have a very tunable buffer size.

avinash.818 Mr or Mrs. 500 Points: 520 More actions · Answer 4

Source is SQL and destination is also SQL, I am using OleDB source, but connection is to a SQL Server Database

Joe Torre SSChampion Points: 10248 More actions · Answer 5

I don't think SSIS is pausing just the refresh to the GUI is asynchronous. SSIS also commits periodically maybe that has something to do with it.

avinash.818 Mr or Mrs. 500 Points: 520 More actions · Answer 6

even I thought so, but when I observe it with performance counter Bufferused remains at a constant value when UI stops reading that indicates nothing is happening
It might not also be with commit because, to test this I am right now using RowCount Transformation as destination still the reading pauses.