I'm trying to figure out what is going on when I execute a package through the Integration Services Catalog. When I run it in debug from my local machine it completes in around 30 seconds, but when running through the catalog it's closer to 5 minutes. I turned on logging for all events and found that the major increase in execution time is happening on my lookups on the ProcessInput event. The time increase from 1200 msto 226342 ms, 675 ms to 173495 ms, etc. The increases listed are for my 2 largest lookups (2.1M rows and 875k rows). The other lookups are much smaller, but still show pretty large increases in processing time. I'm using full cache. The 2.1M row lookup only uses 99MB of memory so it's not too terrible. I'm running it on a VM. I have tried increasing the number of cores from 4 to 8, RAM from 4 to 16GB. It just doesn't seem to help. The storage is iSCSI. My local desktop is only quad core with 4GB RAM. Thoughts anyone?

Just to be clear - you are comparing a time to execute from your desktop in BIDS and the catalog on a different machine? If so, can you do an apples to apples comparision? ie: do you have a local copy of SQL server IS Catalog on your desktop that you could deploy the package to? It could be as simple as - you have a better path to the data than the server does. Chuck

You probably are processing a different amount of data on the server vs your desktop because the datasources are different.Arthur My Blog

It's the same amount of data (2089779 rows) the last time I ran it through the catalog is ran in 266 seconds and through BIDS it was 29 seconds.

I'll try running it through my local instance and get back to you. However, the source and destination databases are on the same server as the package I'm running in the Integration Services Catalogs. The major difference it would seem is that when I run the package locally it isn't competing with the resources of the database server.

It seems to me you need to switch to the Partial Cache mode, looks like the machine is doing alot in parallel with your package. Also to make sure this occurs, can you run your package at a time when the machine is more or less idling?Arthur My Blog

Ok, I just ran it on what is going to be my production box (I'm creating a new warehouse in 2012). This machine is a physical server with 16 cores and 24GB of RAM. I ran it through the catalog and it completed in 18 seconds. This was my production ODS to production STG that are on the same box as the catalog I ran it in. It seems to be something with my virtual server?

I tried partial cache and no cache and the performance was horrendous. I didn't even wait to let it finish it hung out there so long. The VM is completely dedicated to the dev warehouse. Also, the overall machine that the VM is hosted on only have 1 other VM on it that is used very infrequently. There's really nothing contending with the resources.

Could be any number of things. Is there memory currently free on the VM or has it all been taken and its swapping back and forth to disk when the package runs?Chuck

Since SSIS receives data in buffers / chunks (to optimize memory usage). And looks like on your VM the buffer size is small, SSIS does more ProcessInput calls there than on your other machines - one ProcessInput call per buffer in particular, and does not release it. So something has triggered this kind of SSIS processing engine algorithm to do that, and in short, I suspect the machine is taxed up to its eye balls and / or misconfigured.Arthur My Blog

My sys admin guy was working with me on it. We could see the disk access spiking. You have a good point. I noticed that my RAM was nearly 100% so I bet it was paging to disk during the load. We backed down the amount of RAM available to SQL to 50% of what is allocated to the VM.

Oh yeah, it's slammed. The more we throw at it, the more it will use. Here is a shot of the task manager when it is running... What configuration settings should be we looking at to help this?

If you look at the processes tab - what is using all of the memory and CPU? If it is SQL server then take a look at the sql activity monitor and see what is sucking up all the resources.Chuck

I've noticed in SQL Activity Monitor that there are a lot of CXPACKET and ASYNC_NETWORK_IO waits. Wait time on the CXPACKET waits is now at 248008 ms.

I think it is a moot point trying to run your package thru this "perfect storm". You need better conditions. I'd go to the Processes tab and check "Show processes from all users" and see what process(s) takes the most CPU and memory.Arthur My Blog

I've noticed in SQL Activity Monitor that there are a lot of CXPACKET and ASYNC_NETWORK_IO waits. Wait time on the CXPACKET waits is now at 248008 ms. This typically means SQL Server misconfiguration e.g not set to utilize parallelism - multiple CPUs. Review its settings. At times though, this could be a badly written query instructing SQL Server to disregard parallelism.Arthur My Blog

Max Degree of Parallelism is set to 0. I have 4 processors set to 'Automatically set processor affinity mask for all processors' and 'Automatically set I/O affinity mask for all processors'. Cost Threshold for Parallelism is set to 5. Do any of these seem to be incorrect? Also, I really appreciate your assistance. Thanks for taking the time to assist me.

with 8 CPUs or less 0 is fine. But I 'd try setting the Cost Threshold for Parallelism from 5 to 1Arthur My Blog

with 8 CPUs or less 0 is fine. But I 'd try setting the Cost Threshold for Parallelism from 5 to 1 Arthur My Blog That will make it worse not better - causing even smaller queries to go parralell. CX_PacketWaits are caused by too much parrallelism, not too little. Try setting it to 10 and go from there - but it will just be a bandaid. Queries that are going parrallel and causing cx_packet waits tend to point to indexing issues more often than not. What happens is that the parrallel threads spend more time talking to each other than doing any actual work. Chuck

Setting Cost Threshold for Parallelism to 10 appears to have taken care of it. The package executed in 32 seconds. Any thoughts as to why this would only be an issue when running on the server that hosts the source and destination databases? I would think that if it's indexing it would happen when I run it in debug as well? The source query is just a simple inner join between two tables on the ID column.

Setting Cost Threshold for Parallelism to 10 appears to have taken care of it. The package executed in 32 seconds. Any thoughts as to why this would only be an issue when running on the server that hosts the source and destination databases? I would think that if it's indexing it would happen when I run it in debug as well? The source query is just a simple inner join between two tables on the ID column.

Setting Cost Threshold for Parallelism to 10 appears to have taken care of it. The package executed in 32 seconds. Any thoughts as to why this would only be an issue when running on the server that hosts the source and destination databases? I would think that if it's indexing it would happen when I run it in debug as well? The source query is just a simple inner join between two tables on the ID column. Different query plan is being selected for some reason and in one case it is a bad one.Chuck

I guess the stats were not updated on your system, this happens oftentimes when data in a database changes dramatically and yet no db maintenance jobs exist to rebuild/refresh indexes.Arthur My Blog

Well, setting Cost Threshold for Parallelism to 10 worked on that package. I've had it come up with queries on the system. I'm now running sp_updatestats after the load and still have CTP set to 10. Now everything is fine. Thanks for the help!