Performance problem on server
Good morning, i have a strange situation with a SSIS package. Running in BIDS on my machine, the package is very fast and has no particular problem while running on server its running time ranges from some minute (~4min) to 1 hour! Randomly it also stops on DTS_E_PRIMEOUTPUTFAILED. The package does nothing particularly strange, and load around 800k records. It used to update a table (used only by this package), with an sql command update, but i changed it to an OleDB destination. The package is mainly build with Merge (with some sort when needed after Union All). I used Merge components instead of Lookup because all the searches are made on records loaded from the same table and filtered with Conditional Split to reduce the I/O needs. The funny thing is that the strange behaviour started when we migrate from Sql Server 2005 on a 32bit Windows Server 2003 to Sql Server 2008 R2 on 64 bit Windows Servero 2008 R2 with a double amount of memory. Do you have any idea of what i could try? Maybe some settings in the Sql Server Agent i do not know? Thanks Luigi
February 8th, 2011 6:09am

Hello Luigi, In general you would need to find the bottleneck in your SSIS, perhaps this is the merge, start with logging time to execute each step. Also you may want to read this article on how to speed up packages designed like yours http://msdn.microsoft.com/en-us/library/ms141031.aspx. Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
February 8th, 2011 11:42am

Definitely attempt to decompose your package and watch performance counters. I suspect a memory issue related to Sort and Merge components - but you need to confirm that. Your choice of Merge instead of Union based on your rationale doesn't seem to be a good use of components. You can craft a filtered Lookup to limit the lookup information, or use a partial cache to reduce memory requirements. Either of those strategies are probably better than using Sort and/or Merge components. You can even (in 2008) create your own cache in a prior Data Flow Task with the Cache Transformation, and then use that cache with a Lookup. Talk to me now on
February 8th, 2011 12:57pm

Add logging to see where it stops. Watch the task manager to see if the processor or memory usage is high (or even better watch the by Todd mentioned performance counters). And here are some easy to implement performance best practices: http://microsoft-ssis.blogspot.com/2010/12/performance-best-practices.html Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com
Free Windows Admin Tool Kit Click here and download it now
February 8th, 2011 1:07pm

I tried with lookup but i had some problems because too many were needed, but i think should try to rewrite it again using the common cache in 2008. However, the problem seems to be a memory issue while filling the buffer: i increased the default row number and size (to 30000 and 30MB) and performances seem to have improved, and i'll monitor it for a few days to see if it's ok However i still cannot understand why in development i have no issue...
February 9th, 2011 10:30am

I tried with lookup but i had some problems because too many were needed, but i think should try to rewrite it again using the common cache in 2008. However, the problem seems to be a memory issue while filling the buffer: i increased the default row number and size (to 30000 and 30MB) and performances seem to have improved, and i'll monitor it for a few days to see if it's ok However i still cannot understand why in development i have no issue... The question is if you have other processes like SQL Server running on the same machine where you execute packages. If that is the case, then you probably don't have enough memory to accommodate all applications.SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Free Windows Admin Tool Kit Click here and download it now
February 10th, 2011 7:52am

I build some Cache Transform and now the package works really fine. I still cannot understand why i have no problem in development studio and i had non too on the old SqlServer2005, however now is certanly a better package. Thanks a lot guys! Luigi
February 17th, 2011 11:03am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics