April 23, 2009 at 1:08 pm
I have a large DTSX package which accomplishes numerouse tasks. Most of it runs great. Except on particular task. That task reads data from a view in one data base and copies it into a table in another database on the same server 4 times per day and to a different server 1 time per day. Currently, the process writes 2,208 rows.
I can do SELECT * INTO ... and copy these records in under 1 second.
I have isolated this process into a different .DTSX package for troubleshooting and here is what the results are.
I start the package and all appears to be running smoothly
Task ModelIntroductions
Start, 1:19:10 PM
Validation has started
[DTS.Pipeline] Information: Validation phase is beginning.
Progress: Validating - 0 percent complete.
Progress: Validating - 50 percent complete
Progress: Validating - 100 percent complete
Validation is completed
[DTS.Pipeline] Information: Prepare for Execute phase is beginning.
Progress: Prepare for Execute - 0 percent complete
Progress: Prepare for Execute - 50 percent complete
Progress: Prepare for Execute - 100 percent complete
[DTS.Pipeline] Information: Pre-Execute phase is beginning.
Progress: Pre-Execute - 0 percent complete
Progress: Pre-Execute - 50 percent complete
Progress: Pre-Execute - 100 percent complete
[DTS.Pipeline] Information: Execute phase is beginning.
[ModelsIntroduction [7149]] Information: The final commit for the data insertion has started.
[ModelsIntroduction [7149]] Information: The final commit for the data insertion has ended.
It takes maybe 15 seconds to get to this point. (maybe)
Now, this is where things fall apart. The package "stalls" at this point will wait anywhere from 19 minutes to 36 minutes. During this time the processes on the server are "Sleeping" with no blocking occuring between any processes on the server.
After the wait, things continue onward.
[DTS.Pipeline] Information: Post Execute phase is beginning.
Progress: Post Execute - 0 percent complete
Progress: Post Execute - 50 percent complete
Progress: Post Execute - 100 percent complete
[DTS.Pipeline] Information: Cleanup phase is beginning.
Progress: Cleanup - 0 percent complete
Progress: Cleanup - 50 percent complete
Progress: Cleanup - 100 percent complete
[DTS.Pipeline] Information: "component "ModelsIntroduction" (7149)" wrote 2208 rows.
Finished, 1:56:44 PM, Elapsed time: 00:37:27.657
This last section takes may be 2 seconds.
Does anyone have any ideas, encounters with this, etc.
Thanks in advance,
Scott B Dragoo
Database Administrator
Tripplite
Scott B Dragoo
Enterprise Architect
Vitality Group
http://www.thevitalitygroup.com
April 24, 2009 at 6:59 am
My first inclination is that maybe you have an I/O bottleneck. When you monitor the process, are there any wait states indicated?
May 4, 2009 at 12:08 pm
There are no wait states indicated. Also, what is strange is that the 2208 rows are present in the target table as soon as the "stall" period begins.
Scott B Dragoo
Enterprise Architect
Vitality Group
http://www.thevitalitygroup.com
May 5, 2009 at 9:35 am
OK. I got mine to perform more like expected.
On the DTSX step (Data Flow Task), I changed the DefaultBufferSize from 10485760 to 40485760.
Total execution time went from 20 minutes+ down to 6 seconds.
I hope this helps someone else.
Scott B Dragoo
Enterprise Architect
Vitality Group
http://www.thevitalitygroup.com
May 5, 2009 at 10:10 am
Wow! Can anyone explain that?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 1, 2011 at 12:30 pm
I think this makes a lot of sense. That default value equates to a 10 MB in memory buffer (10,240 * 1,024 = 10,485,760).
By changing the value to 40,485,760, you are essentially increasing the buffer size to 40 MB (although technically speaking the value should be 41,943,040).
If I switch from a 1 Gallon bucket to a 4 Gallon bucket, I can empty the well quite a bit faster provided my arms are strong enough to hoist a bucket that is 4 times as heavy.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply