SSIS Performance on Sql Server 2008 R2

I have a very odd performance issue with an SSIS package in Sql Server 2008 R2. Here the facts: Recently we migrated from a Sql Server 2005 (on Windows Server 2003 R2 32bit) to Sql Server 2008 R2 (on Windows Server 2008 R2 64bit).

Everything seems to be fine except some performance issue on one SSIS package. If i run it from my PC it runs fine in some minutes (around 4/5) and the same seems to happen if i connect to Integrations Services through SqlServer Management Studio and start the package from there.

However when i run it from Sql Server Agent i have execution time that goes from 5 minutes to more than one hour... I had no such problems with the old server! I also tried to run the package in 32bit mode, on some run it seems to be faster, but it's pretty random... However it never reach the good performance it had on Sql Server 2005.

I have no clue... initially i think it was a memory problem because i did not give maximum memory limit to Sql Server and some other package had problems to run at the same time, so I expanded the RAM used by the server (it runs on VMWare) and now the machine has 8GB of RAM and maximum server memory for Sql Server is 4GB. The other package has no crash now, but this is still giving random execution time开发者_开发百科...

Any guess?

Following a table of execution time through days

Start Time          Execution Time
12/17/2010 06:15 00:49:43
12/16/2010 17:54 01:12:26
12/16/2010 17:18 00:06:29
12/16/2010 16:53 00:05:23
12/16/2010 16:10 00:24:23
12/16/2010 06:15 00:19:26
12/15/2010 06:15 00:07:19
12/14/2010 06:15 00:11:26
12/13/2010 06:15 00:17:30
12/12/2010 06:15 00:44:59
12/11/2010 06:15 00:11:59
12/10/2010 06:15 00:34:19

---

What else is running? Do you have users running queries (who may lock tables) or other packages touching related tables when this package is scheduled to run? When you run it, do you see blocking locks or anything else like that?

It's more likely to me that the production batch execution environment isn't as nice and quiet and controlled as your development environment.

A user or related package holding data locks might explain the random distribution of execution times.

---

I found a solution to this.

The problem was a memory issue with the buffer creation on the server.

I partially solved it increasing the default buffer size (both in MB and rows number) and completely solved it removing all the Sort and Merge component i used replacing them with Lookup on custom Cache built with Transform Cache component.

I still do not understand why the allocation in Sql Server 2005 with Windows Server 2003 and in development works fine, however now the package is fixed!